Re: adding 'limit' leads to very slow query

Michael McFarland wrote:

> I'm trying to understand why a particular query is slow, and it
> seems like the optimizer is choosing a strange plan. See this summary:
>
...

>> explain select * from foo where barId = 412 order by id desc limit 25;
>
> Limit ()
> -> Index scan backward using primarykey_index
> Filter: barID = 412
>
>
> Could anyone shed some light on what might be happening here?
>
> - Michael

It is using the wrong index. The problem is that order by + limit
generally means that you can use the index on the "+" to get the items
in the correct order. In this case, however, you need it to find all of
the barId=412 first, since apparently that is more selective than the limit.

It really sounds like the postgres statistics are out of date. And
either you haven't run vacuum analyze recently, or you need to keep
higher statistics on either one or both of barId and id.

John
=:->