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

On 04/26/2013 09:39 AM, Rowan Collins wrote:

> If I run "ANALYZE temp_fares_mappings;" - the table which is being
> Updated, and is the outermost in the query plan - the problem goes
> away *even though the Query Plan hasn't changed*.

Oh, but it has.

The query plan pre-analyze:

Update on test_mappings lfm (cost=1277.27..2482.22 rows=1 width=50)
-> Hash Join (cost=1277.27..2482.22 rows=1 width=50)
Hash Cond: ((lf.low_fares_row_id = lfm.low_fares_row_id) AND
(co.central_offer_id = lfm.central_offer_id) AND (co.credential_set_id =
lfm.credential_set_id))
-> Hash Join (cost=335.68..1505.46 rows=293 width=44)
Hash Cond: (lf.number_in_party = co.num_adults_searched)
-> Seq Scan on test_low_fares lf (cost=0.00..946.80
rows=58680 width=22)
-> Hash (cost=335.67..335.67 rows=1 width=30)
-> Seq Scan on test_cob_offers co
(cost=0.00..335.67 rows=1 width=30)
Filter: ((num_children_searched = 0) AND
(num_infants_searched = 0) AND (supplier_code = 'AMA'::text))
-> Hash (cost=936.39..936.39 rows=297 width=18)
-> Seq Scan on test_mappings lfm (cost=0.00..936.39
rows=297 width=18)
Filter: (cob_offer_id IS NULL)

And post-analyze:

Update on test_mappings lfm (cost=2652.90..9219.07 rows=63362 width=50)
-> Hash Join (cost=2652.90..9219.07 rows=63362 width=50)
Hash Cond: ((lf.number_in_party = co.num_adults_searched) AND
(lfm.central_offer_id = co.central_offer_id) AND (lfm.credential_set_id
= co.credential_set_id))
-> Hash Join (cost=1959.95..5358.02 rows=63362 width=36)
Hash Cond: (lfm.low_fares_row_id = lf.low_fares_row_id)
-> Seq Scan on test_mappings lfm (cost=0.00..976.62
rows=63362 width=18)
Filter: (cob_offer_id IS NULL)
-> Hash (cost=923.98..923.98 rows=56398 width=22)
-> Seq Scan on test_low_fares lf
(cost=0.00..923.98 rows=56398 width=22)
-> Hash (cost=413.48..413.48 rows=15970 width=30)
-> Seq Scan on test_cob_offers co (cost=0.00..413.48
rows=15970 width=30)
Filter: ((num_children_searched = 0) AND
(num_infants_searched = 0) AND (supplier_code = 'AMA'::text))

These are pretty drastically different. But... the first crippled my
test system and started consuming vast resources, while the second
executed in about 300ms. That's hard to believe with the row counts seen
here unless it's turning it into some invisible Cartesian Product.

--
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