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

"Stephen Denne" <Stephen(dot)Denne(at)datamail(dot)co(dot)nz> writes:
> 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.

At the planner level that would be entirely the wrong way to go about
it, because that's forcing the equivalent of a nestloop join, which is
very unlikely to be faster for the numbers of rows that we're talking
about here. The reason it looks faster to you is that the benefits of
updating the document_file rows in ctid order outweigh the costs of the
dumb join strategy ... but what we want to achieve here is to have both
benefits, or at least to give the planner the opportunity to make a
cost-driven decision about what to do.

regards, tom lane