X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 70ACED1BB8C for ; Mon, 12 Apr 2004 09:28:44 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 69334-03 for ; Mon, 12 Apr 2004 09:28:46 -0300 (ADT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 7AB38D1BAAD for ; Mon, 12 Apr 2004 09:28:42 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i3CCSJIh008580; Mon, 12 Apr 2004 08:28:19 -0400 (EDT) To: =?ISO-8859-1?Q?Pailloncy_Jean-G=E9rard?= Cc: pgsql-performance@postgresql.org Subject: Re: Index Backward Scan fast / Index Scan slow ! In-reply-to: <8A2105D4-8C63-11D8-80FD-000A95DE2550@ifrance.com> References: <8A2105D4-8C63-11D8-80FD-000A95DE2550@ifrance.com> Comments: In-reply-to =?ISO-8859-1?Q?Pailloncy_Jean-G=E9rard?= message dated "Mon, 12 Apr 2004 11:26:55 +0200" Date: Mon, 12 Apr 2004 08:28:19 -0400 Message-ID: <8579.1081772899@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/129 X-Sequence-Number: 6429 =?ISO-8859-1?Q?Pailloncy_Jean-G=E9rard?= writes: > How to optimize the last query ? (~ 2000 times slower than the first > one) > I suppose there is some odd distribution of data in the index ? Looks to me like a whole lot of dead rows at the left end of the index. Have you VACUUMed this table lately? It would be interesting to see what VACUUM VERBOSE has to say about it. > Is the solution to reindex data ? In 7.4 a VACUUM should be sufficient ... or at least, if it isn't I'd like to know why not before you destroy the evidence by reindexing. regards, tom lane