Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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]


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux