Re: PostgreSQL 8.3.3 chooses wrong query plan when LIMIT 1 added?

Mark Cave-Ayland <mark(dot)cave-ayland(at)siriusit(dot)co(dot)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