Re: UPDATE using 3 medium-sized tables causes runaway hash table and fills disk

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(at)optionshouse(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email