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 427FD52945 for ; Mon, 15 Aug 2005 06:46:49 -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 46516-07 for ; Mon, 15 Aug 2005 09:46:47 +0000 (GMT) Received: from benny.care4all.dk (cpe.atm2-0-52453.0x50a4a22a.esnxx3.customer.tele.dk [80.164.162.42]) by svr1.postgresql.org (Postfix) with ESMTP id 220F752E02 for ; Mon, 15 Aug 2005 06:46:45 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by benny.care4all.dk (Postfix) with ESMTP id 4B41D10BE2; Mon, 15 Aug 2005 11:46:47 +0200 (CEST) Received: from benny.care4all.dk ([127.0.0.1]) by localhost (benny [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 19205-04; Mon, 15 Aug 2005 11:46:46 +0200 (CEST) Received: from [10.10.10.20] (pmh [10.10.10.20]) by benny.care4all.dk (Postfix) with ESMTP id ABAE7F082; Mon, 15 Aug 2005 11:46:46 +0200 (CEST) Message-ID: <43006480.4000409@pbnet.dk> Date: Mon, 15 Aug 2005 11:46:40 +0200 From: =?ISO-8859-1?Q?Poul_M=F8ller_Hansen?= User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Richard Huxton Cc: pgsql-general@postgresql.org Subject: Re: Optimizing query References: <43005482.7020105@pbnet.dk> <43005CBD.5070703@archonet.com> In-Reply-To: <43005CBD.5070703@archonet.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at care4all.dk X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 required=5 tests=[AWL=-0.000, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200508/717 X-Sequence-Number: 82125 >> I have a problem creating a usable index for the following simple query: >> SELECT * FROM my.table WHERE node = '10' ORDER BY id DESC LIMIT 1 >> >> id is a serial, so the query is to find the latest entry to a given >> node and id is the primary key. > > > You're not necessarily getting the latest entry, just the one with the > highest "id". Sequences guarantee uniqueness but if you have > concurrent inserts not necessarily ordering. > Right you are, but I have no concurrent inserts from the same node. > > Difficult to say what's happening since you don't supply any EXPLAIN > ANALYSE output. > > However, if you have an index on (node,id) you might want to try: > SELECT ... ORDER BY node DESC, id DESC LIMIT 1; > That way the "ORDER BY" part clearly tells the planner that a > reverse-order on your index will be useful. > Thanks a lot, that did the trick ! explain analyze SELECT * FROM my.table WHERE node = '10' ORDER BY id DESC LIMIT 1 QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..764.00 rows=1 width=246) (actual time=1874.890..1874.896 rows=1 loops=1) -> Index Scan Backward using table_pkey on table (cost=0.00..4347913.94 rows=5691 width=246) (actual time=1874.867..1874.867 rows=1 loops=1) Filter: ((node)::text = '10'::text) Total runtime: 1875.111 ms 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 Poul