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