Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Stephen Denne" <Stephen(dot)Denne(at)datamail(dot)co(dot)nz>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Possible future performance improvement: sort updates/deletes by ctid
Date: 2008-01-30 04:39:39
Message-ID: 2356.1201667979@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
"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

In response to

Responses

pgsql-hackers by date

Next:From: Stephen DenneDate: 2008-01-30 04:51:08
Subject: Re: Possible future performance improvement: sort updates/deletes by ctid
Previous:From: Stephen DenneDate: 2008-01-30 04:00:59
Subject: Re: Possible future performance improvement: sort updates/deletes by ctid

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group