Re: strange query plan with LIMIT

On Wed, Jun 8, 2011 at 7:08 AM,  <anthony(dot)shipman(at)symstream(dot)com> wrote:
> What seems odd to me is that the only difference between the two is the limit
> clause

Why would that seem odd?

Of course optimally executing a plan with limit is a lot different
than one without.

Just... why are you sorting by diag_id?

I believe you would be better off sorting by timestamp than diag_id,
but I don't know what the query is supposed to do.

In any case, that's a weakness I've seen in many database systems, and
postgres is no exception: order + limit strongly suggests index usage,
and when the ordered column has "anti" correlation with the where
clause (that is, too many of the first rows in the ordered output are
filtered out by the whereclause), the plan with an index is
insufferably slow compared to a sequential scan + sort.

Postgres has no way to know that, it depends on correlation between
the where clause and the ordering expressions.

If you cannot change the query, I think your only option is to either
add a specific index for that query (ie, if the where clause is always
the same, you could add a partial index), or just disable nested loops
with "set enable_nestloop = false;" just prior to running that query
(and remember to re-enable afterwards).