X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7654052DFE for ; Mon, 15 Aug 2005 10:46:16 -0300 (ADT) 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 10533-01 for ; Mon, 15 Aug 2005 13:46:14 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 6600652DD0 for ; Mon, 15 Aug 2005 10:46:14 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j7FDk7NT025299; Mon, 15 Aug 2005 09:46:07 -0400 (EDT) To: =?ISO-8859-1?Q?Poul_M=F8ller_Hansen?= Cc: Richard Huxton , pgsql-general@postgresql.org Subject: Re: Optimizing query In-reply-to: <43006480.4000409@pbnet.dk> References: <43005482.7020105@pbnet.dk> <43005CBD.5070703@archonet.com> <43006480.4000409@pbnet.dk> Comments: In-reply-to =?ISO-8859-1?Q?Poul_M=F8ller_Hansen?= message dated "Mon, 15 Aug 2005 11:46:40 +0200" Date: Mon, 15 Aug 2005 09:46:07 -0400 Message-ID: <25298.1124113567@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200508/727 X-Sequence-Number: 82135 =?ISO-8859-1?Q?Poul_M=F8ller_Hansen?= writes: > explain analyze SELECT * FROM my.table WHERE node = '10' ORDER BY node, > id DESC LIMIT 1 > QUERY > PLAN > -------------------------------------------------------------------------------------------------------------------------------------------- > Limit (cost=22638.36..22638.36 rows=1 width=246) (actual > time=3.001..3.007 rows=1 loops=1) > -> Sort (cost=22638.36..22652.59 rows=5691 width=246) (actual > time=2.984..2.984 rows=1 loops=1) > Sort Key: node, id > -> Index Scan using node_date on table (cost=0.00..21898.65 > rows=5691 width=246) (actual time=0.077..1.852 rows=62 loops=1) > Index Cond: ((node)::text = '10'::text) > Total runtime: 3.127 ms You're not there yet: you want what Richard said, namely explain analyze SELECT * FROM my.table WHERE node = '10' ORDER BY node DESC, id DESC LIMIT 1 There shouldn't be any Sort in the plan, just the indexscan and Limit. The plan above is going to suck if there are a lot of rows with node = '10'. regards, tom lane