On Wed, Jun 8, 2011 at 7:08 AM, <anthony.shipman@xxxxxxxxxxxxx> 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). -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
