Re: [PERFORM] Any idea on how to improve the statistics estimates for this plan?
On Sat, Dec 8, 2012 at 5:19 AM, Guillaume Smet wrote: > Hi Jeff, > > On Sat, Dec 8, 2012 at 3:32 AM, Jeff Janes wrote: >> If those estimates are better, it probably means that your filter >> condition is picking a part of the "el JOIN l" that has much different >> selectivity to r than the full set does, and PostgreSQL has no way of >> knowing that. > > It's certainly that. The fact is that this query is OK on most of the > French territory but it doesn't go well when you're looking at Paris > area in particular. As the query is supposed to return the shows you > can book, the selectivity is quite different as Paris has a lot of > places AND places organize a lot more shows in Paris than in the rest > of France. I was hoping that the high number of places would be enough > to circumvent the second fact which is much harder for PostgreSQL to > get but it looks like it's not. > > Is there any way I could mitigate this issue by playing with planner > knobs? I don't know the answer to that. But does it matter? If it knew you were going to get 300,000 rows rather than 2, would it pick a better plan? Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query: bitmap scan troubles
On Tue, 2012-12-04 at 15:42 -0800, Jeff Janes wrote: > On Tue, Dec 4, 2012 at 10:03 AM, wrote: > >[...] > > > > Is there some nice bit of literature somewhere that explains what sort of > > costs are associated with the different types of lookup? > > I've heard good things about Greg Smith's book, but I don't know if it > covers this particular thing. > > Otherwise, I don't know of a good single place which is a tutorial > rather than a reference (or the code itself) > Greg's book is awesome. It really gives a lot of informations/tips/whatever on performances. I mostly remember all the informations about hardware, OS, PostgreSQL configuration, and such. Not much on the EXPLAIN part. On the EXPLAIN part, you may have better luck with some slides available here and there. Robert Haas gave a talk on the query planner at pgCon 2010. The audio feed of Robert Haas talk is available with this file: http://www.pgcon.org/2010/audio/15%20The%20PostgreSQL%20Query% 20Planner.mp3 You can also find the slides on https://sites.google.com/site/robertmhaas/presentations You can also read the "Explaining the Postgres Query Optimizer" talk written by Bruce Momjian. It's available there : http://momjian.us/main/presentations/internals.html And finally, you can grab my slides over here: http://www.dalibo.org/_media/understanding_explain.pdf. You have more than slides. I tried to put a lot of informations in there. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any idea on how to improve the statistics estimates for this plan?
(cough cough, missed the Reply to all button) Hi Jeff, On Sat, Dec 8, 2012 at 3:32 AM, Jeff Janes wrote: > If those estimates are better, it probably means that your filter > condition is picking a part of the "el JOIN l" that has much different > selectivity to r than the full set does, and PostgreSQL has no way of > knowing that. It's certainly that. The fact is that this query is OK on most of the French territory but it doesn't go well when you're looking at Paris area in particular. As the query is supposed to return the shows you can book, the selectivity is quite different as Paris has a lot of places AND places organize a lot more shows in Paris than in the rest of France. I was hoping that the high number of places would be enough to circumvent the second fact which is much harder for PostgreSQL to get but it looks like it's not. Is there any way I could mitigate this issue by playing with planner knobs? I don't remember having seen something I could use for selectivity (such as the n_distinct stuff). It's not that big a deal if it's a little worth elsewhere as there are far less places so the effects of a bad plan are more contained. -- Guillaume -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
