This looks like another form of the cross-column dependency problem. Postgres
is assuming that the revisions for all files will be evenly spread throughout
the date range and apparently there's a larger variety of dates than files so
it expects to find the last revision for that file fairly quickly scanning
backwards through the dates.
In fact of course files tend to be hot for a period of time and then mostly
idle, so depending on which file you pick that may work well if it's currently
hot or be absolutely terrible if it's a file that hasn't been touched
recently.
With the LIMIT Postgres favours the plan it thinks will return one row quickly
without sorting. Without it it's favouring the plan that will return all the
rows for that file_id most quickly.
I'm not sure what to suggest for this case if you can't change the data model
except perhaps increasing the statistics target.
One thing that comes to mind though, I would have defined one of those two
indexes to include both columns. Probably the file_id index, so you would have
an index on <revision_id> and an index on <file_id,revision_id>. That would
be a huge win for this query.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!
