Re: PostgreSQL 8.3.3 chooses wrong query plan when LIMIT 1 added? - Mailing list pgsql-general

Mark Cave-Ayland <mark.cave-ayland@siriusit.co.uk> writes:
> svnlog=# SELECT revision_id FROM revision_files WHERE file_id=(SELECT
> file_id
> FROM files WHERE filepath='/trunk/app/widgets/gimptoolbox-dnd.c' LIMIT 1)
> ORDER BY revision_id DESC LIMIT 1;
>   revision_id
> -------------
>         15011
> (1 row)

> Time: 935.816 ms

Hmm, I'd expect it to be more or less instantaneous given the right
index.  What does EXPLAIN ANALYZE say about this?

> However, some more searching came up with this "ORDER BY x + 0"
> variation which seems to consistently perform the fastest for varying
> flavours of revision_id by forcing use of the file_id index:

And that?

            regards, tom lane