From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Frith-Macdonald <richard@tiptree.demon.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why is my (empty) partial index query slow?
Date: Thu, 22 Jun 2006 12:04:57 -0400
Message-ID: <511.1150992297@sss.pgh.pa.us> (raw)
In-Reply-To: <5510ED72-8266-438B-A4DF-9591ED26BA38@tiptree.demon.co.uk>
References: <5510ED72-8266-438B-A4DF-9591ED26BA38@tiptree.demon.co.uk>
Richard Frith-Macdonald <richard@tiptree.demon.co.uk> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, richard@tiptree.demon.co.uk
Subject: Re: Why is my (empty) partial index query slow?
In-Reply-To: <511.1150992297@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox