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

On 29/04/2013 17:04, Shaun Thomas wrote:
> 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.

Hi Shaun,

Unfortunately the cut-down version of the data I put together to avoid
showing confidential data doesn't show the same effect. (Basically, I
removed most of the columns from the original tables that weren't
referenced anywhere in the query.) The actual import process I was
working on when I first uncovered the issue resulted in this query plan:

Update on temp_low_fares_mappings lfm (cost=3780.90..10347.07
rows=63362 width=50)
-> Hash Join (cost=3780.90..10347.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=2809.95..6208.02 rows=63362 width=36)
Hash Cond: (lfm.low_fares_row_id = lf.low_fares_row_id)
-> Seq Scan on temp_low_fares_mappings lfm
(cost=0.00..976.62 rows=63362 width=18)
Filter: (cob_offer_id IS NULL)
-> Hash (cost=1773.98..1773.98 rows=56398 width=22)
-> Seq Scan on csv_low_fares lf
(cost=0.00..1773.98 rows=56398 width=22)
-> Hash (cost=691.48..691.48 rows=15970 width=30)
-> Seq Scan on offers co (cost=0.00..691.48 rows=15970
width=30)
Filter: ((num_children_searched = 0) AND
(num_infants_searched = 0) AND (supplier_code = 'AMA'::text))

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). Running ANALYZE
temp_low_fares_mappings in this case led to no change at all in the
query plan, but resolved the odd behaviour of the execution of that plan.

Frustratingly, it's quite hard to reproduce the exact circumstances of
this due to the complex processing this is just one part of. It is
possible that I messed something up during testing, but I am pretty sure
the ANALYZE was fixing the execution itself, not just changing the query
plan.

Regards,
Rowan Collins
[IMSoP]