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

Mark Cave-Ayland <mark.cave-ayland@siriusit.co.uk> writes:
> I'm experiencing a strange issue with PostgreSQL 8.3.3 whereby adding
> "LIMIT 1" to the query increases the query time from several 10s of ms
> to over 5s, and was wondering if anyone with more planner-fu can shed
> some light on this.

It's hoping that the backwards scan will hit a row with the requested
file_id quickly; which might be true on average but isn't true for this
particular file_id (nor, presumably, any file_id that hasn't been
updated recently).

You might consider a two-column index on (file_id, revision_id) to
make this type of query fast.

            regards, tom lane