Re: Possible future performance improvement: sort updates/deletes by ctid

["Stephen Denne" <Stephen(dot)Denne(at)datamail(dot)co(dot)nz>]

> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us] 
> doubtless-oversimplified
It looks equivalent.

> With that patch, I got the results
...
>    ->  Hash Join  (cost=123330.50..1207292.72 rows=4000008 
> width=618) (actual time=20186.510..721120.455 rows=4000000 loops=1)

The plan from here is equivalent to the query plan that I had.
In an update query, does the actual time = 721120 mean that after 12 minutes it had completed figuring out what to update, and what to?

> This is with default shared_buffers (32MB) and work_mem (1MB);

I had tried a few larger settings, and though I had fewer temp files created, they still took longer than I was willing to wait to process.
I did figure out that contention with the background writer or checkpoint processing probably wasn't a large contributor.

How hard is it to match, recognise potential benefit, and rewrite the query from

UPDATE ONLY document_file AS df SET document_type_id = 
        d.document_type_id FROM document AS d WHERE d.id = document_id;

to

UPDATE ONLY document_file AS df SET document_type_id = 
(SELECT d.document_type_id FROM document AS d WHERE d.id = document_id);

Which is several orders of magnitude faster for me.

Stephen Denne.

Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege.  If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way.

__________________________________________________________________
  This email has been scanned by the DMZGlobal Business Quality 
              Electronic Messaging Suite.
Please see http://www.dmzglobal.com/services/bqem.htm for details.
__________________________________________________________________