On 04/29/2013 12:13 PM, Rowan Collins wrote:
This is the same basic plan as the test case, but with the tables in a slightly different order (this has the offers table joined last, where the test data joins the mapping table last).
Tom already explained this much better than I could. But the key words here are "same basic plan" and "slightly different order."
Slight differences in query plans can have much larger impact than you might imagine, as your test case has clearly demonstrated. :)
What you basically created was a worst case scenario for the planner. It takes certain shortcuts based on your WHERE clauses, and the data doesn't match them with nearly the estimated correlation. This often happens with nested loops. The thing that bit you was the fact that this is worst case is so large, it can't really be EXPLAIN ANALYZED to see *where* it went wrong except by someone relatively intimately acquainted with the planner.
What would happen if the planner could calculate worst case estimations for likely candidates, and if one could potentially be horrible (equivalent of a Cartesian Product), use the next best? I'm not sure that's actually feasible, but you never know.
From my experience, it's not uncommon for WHERE clauses to actually do very little in regards to restricting the result set. In a couple of cases, I've had to use CTE's or our friend OFFSET 0 to insert an optimization fence because some WHERE clause is only removing 5 rows, but the planner is producing a drastically different plan that ends up being much slower. And that's on a fully analyzed table with stats cranked way up.
So yeah... analyze your temp tables. If you can find a case where that doesn't help, say something. The more examples we get of edge cases confusing the planner, the quicker they'll get addressed.
This is an awesome little test case, though. How dramatically it explodes really is something to behold.
-- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 sthomas@xxxxxxxxxxxxxxxx ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
