X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 24A249FA92E for ; Thu, 22 Jun 2006 13:05:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 03334-03 for ; Thu, 22 Jun 2006 13:05:00 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 9BD029FA7BA for ; Thu, 22 Jun 2006 13:04:59 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k5MG4vHD000512; Thu, 22 Jun 2006 12:04:58 -0400 (EDT) To: Richard Frith-Macdonald cc: pgsql-performance@postgresql.org Subject: Re: Why is my (empty) partial index query slow? In-reply-to: <5510ED72-8266-438B-A4DF-9591ED26BA38@tiptree.demon.co.uk> References: <5510ED72-8266-438B-A4DF-9591ED26BA38@tiptree.demon.co.uk> Comments: In-reply-to Richard Frith-Macdonald message dated "Fri, 16 Jun 2006 08:17:14 +0100" Date: Thu, 22 Jun 2006 12:04:57 -0400 Message-ID: <511.1150992297@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/448 X-Sequence-Number: 19805 Richard Frith-Macdonald writes: > I have a producer/consumer setup where various producer processes > insert new records into a table and consumer processes mark those > records as having been handled when they have dealt with them, but > leave the records in the table so that we can generate reports later. Have you tried EXPLAIN ANALYZE on the problem queries? If you want help here, you really need to show us the table and index definitions, the exact queries, and the EXPLAIN ANALYZE results. Oh, and mention the exact Postgres version you're using, too. Otherwise we're just guessing at what's going on. > I guess that the fact that records are constantly (and rapidly) added > to and removed from the index may have caused the index to become > inefficient somehow ... How often are you vacuuming the table? A heavily-updated table needs a lot of vacuuming to avoid becoming bloated. regards, tom lane