Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group