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 13C2356404 for ; Mon, 7 Mar 2005 23:39:56 +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 43874-07 for ; Mon, 7 Mar 2005 23:39:46 +0000 (GMT) Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.250.190.2]) by svr1.postgresql.org (Postfix) with ESMTP id D155156424 for ; Mon, 7 Mar 2005 23:39:42 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id A4E8231FC9; Tue, 8 Mar 2005 00:39:45 +0100 (MET) From: "Michael McFarland" X-Newsgroups: pgsql.performance Subject: adding 'limit' leads to very slow query Date: Mon, 07 Mar 2005 18:39:43 -0500 Organization: Hub.Org Networking Services Lines: 38 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; format=flowed; delsp=yes; charset=us-ascii Content-Transfer-Encoding: Quoted-Printable X-Complaints-To: usenet@news.hub.org User-Agent: Opera M2(BETA1)/8.00 (Linux, build 913) To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.174 tagged_above=0 required=5 tests=FORGED_YAHOO_RCVD X-Spam-Level: ** X-Archive-Number: 200503/114 X-Sequence-Number: 10819 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: * I have a large table, with an index on the primary key 'id' and on a = field 'foo'. > select count(*) from foo; 1,000,000 > select count(*) from foo where bar =3D 41; 7 * This query happens very quickly. > explain select * from foo where barId =3D 412 order by id desc; Sort () Sort key=3D id -> Index scan using bar_index on foo () Index cond: barId =3D 412 But this query takes forever > explain select * from foo where barId =3D 412 order by id desc limit 2= 5; Limit () -> Index scan backward using primarykey_index Filter: barID =3D 412 Could anyone shed some light on what might be happening here? - Michael -- = Using Opera's revolutionary e-mail client: http://www.opera.com/mail/