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?
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.
Description: OpenPGP digital signature