From: John A Meinel <john@arbash-meinel.com>
To: Michael McFarland <sidlonDoesntLikeSpam@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: adding 'limit' leads to very slow query
Date: Mon, 07 Mar 2005 22:46:37 -0600
Message-ID: <422D2E2D.5080305@arbash-meinel.com> (raw)
In-Reply-To: <opsnaj0hctsvs6tg@localhost.localdomain>
References: <opsnaj0hctsvs6tg@localhost.localdomain>
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
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: john@arbash-meinel.com, sidlonDoesntLikeSpam@yahoo.com
Subject: Re: adding 'limit' leads to very slow query
In-Reply-To: <422D2E2D.5080305@arbash-meinel.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox