From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, "pgsql-hackers(at)postgreSQL(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Endgame for all those SELECT FOR UPDATE changes: fix plan node order |
Date: | 2009-10-26 14:30:04 |
Message-ID: | 28333.1256567404@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Tom Lane escribi:
>> Yeah, it could definitely run slower than the existing code --- in
>> particular the combination of all three (FOR UPDATE ORDER BY LIMIT)
>> would tend to become a seqscan-and-sort rather than possibly just
>> reading one end of an index. However, I quote the old aphorism that
>> it can be made indefinitely fast if it doesn't have to give the right
>> answer. The reason the current behavior is fast is it's giving the
>> wrong answer :-(
> So this probably merits a warning in the release notes for people to
> check that their queries continue to run with the performance they
> expect.
One problem with this is that there isn't any good way for someone to
get back the old behavior if they want to. Which might be a perfectly
reasonable thing, eg if they know that no concurrent update is supposed
to change the sort-key column. The obvious thing would be to allow
select * from (select * from foo order by col limit 10) ss for update;
to apply the FOR UPDATE last. Unfortunately, that's not how it works
now because the FOR UPDATE will get pushed down into the subquery.
I was shot down when I proposed a related change, a couple weeks ago
http://archives.postgresql.org/message-id/7741.1255278907@sss.pgh.pa.us
but it seems like we might want to reconsider.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2009-10-26 14:34:51 | Re: Proposal: String key space for advisory locks |
Previous Message | Alvaro Herrera | 2009-10-26 14:25:34 | Re: Parsing config files in a directory |