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 5768D53111 for ; Mon, 15 Aug 2005 05:38:40 -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 31771-03 for ; Mon, 15 Aug 2005 08:38:35 +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 2429053115 for ; Mon, 15 Aug 2005 05:38:33 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by benny.care4all.dk (Postfix) with ESMTP id 060F81077C for ; Mon, 15 Aug 2005 10:38:34 +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 18588-03 for ; Mon, 15 Aug 2005 10:38:33 +0200 (CEST) Received: from [10.10.10.20] (pmh [10.10.10.20]) by benny.care4all.dk (Postfix) with ESMTP id 63B2C107D0 for ; Mon, 15 Aug 2005 10:38:33 +0200 (CEST) Message-ID: <43005482.7020105@pbnet.dk> Date: Mon, 15 Aug 2005 10:38:26 +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: pgsql-general@postgresql.org Subject: Optimizing query 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=[FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200508/714 X-Sequence-Number: 82122 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. The table contains around 1 million records and the query takes around 2 seconds. I have tried to make an index on node and also on both id & node, but is doesn't lower the query time. What am I doing wrong ? Thanks, Poul