Re: Why is my (empty) partial index query slow?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Richard Frith-Macdonald <richard(at)tiptree(dot)demon(dot)co(dot)uk>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Why is my (empty) partial index query slow?
Date: 2006-06-22 16:04:57
Message-ID: 511.1150992297@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Richard Frith-Macdonald <richard(at)tiptree(dot)demon(dot)co(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2006-06-22 16:19:52 Re: Tuning New Server (slow function)
Previous Message Tom Lane 2006-06-22 14:47:27 Re: Regarding ERROR: fmgr_info: function 2720768: cache lookup failed