X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CE2FE56455 for ; Tue, 8 Mar 2005 04:46:41 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 36596-05 for ; Tue, 8 Mar 2005 04:46:40 +0000 (GMT) Received: from ct.radiology.uiowa.edu (ct.radiology.uiowa.edu [129.255.60.186]) by svr1.postgresql.org (Postfix) with ESMTP id 6975655012 for ; Tue, 8 Mar 2005 04:46:40 +0000 (GMT) Received: from [192.168.1.11] (12-217-241-0.client.mchsi.com [12.217.241.0]) by ct.radiology.uiowa.edu (8.11.6/8.11.6) with ESMTP id j284kd316450; Mon, 7 Mar 2005 22:46:39 -0600 Message-ID: <422D2E2D.5080305@arbash-meinel.com> Date: Mon, 07 Mar 2005 22:46:37 -0600 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Michael McFarland Cc: pgsql-performance@postgresql.org Subject: Re: adding 'limit' leads to very slow query References: In-Reply-To: X-Enigmail-Version: 0.89.5.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig5D767027BFABF85233AE18BF" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/119 X-Sequence-Number: 10824 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig5D767027BFABF85233AE18BF Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 =:-> --------------enig5D767027BFABF85233AE18BF Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.6 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCLS4tJdeBCYSNAAMRAonGAKDSTmRTj+C+p021JDcPdt1eEMdrqQCfR3GV MyZHj3Qy5dBfMTX5fxSrXXw= =sc4R -----END PGP SIGNATURE----- --------------enig5D767027BFABF85233AE18BF--