pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy is my (empty) partial index query slow?
3+ messages / 2 participants
[nested] [flat]
* Why is my (empty) partial index query slow?
@ 2006-06-16 07:17 Richard Frith-Macdonald <richard@tiptree.demon.co.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Frith-Macdonald @ 2006-06-16 07:17 UTC (permalink / raw)
To: pgsql-performance
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.
The records are added with a char(1) field specifying their state and
a timestamp, and a varchar(40) saying which class of consumer may
handle them.
There is a partial index on consumer and timestamp where the state
field says the record is new ... so there are no records in this
index except when a producer has just added them and no consumer has
yet handled them.
Each consumer polls the database with a query to select a batch of
unhandled records (ordered by timestamp) ... the idea being that,
even though the table has a huge number of historical records used
for reporting, the partial index based query should be tiny/quick as
there are usually few/no unhandled records.
Problem 1 ... why does this polling query take 200-300 milliseconds
when the partial index is empty, and what can be done about it?
This is on a fast modern machine and various other queries take under
a millisecond.
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 ...
If that's the case, dropping it and creating a new one might
temporarily fix the issue... but for how long?
As the actual table is huge (44 million records) and reading all the
records to create a new index would take a long time (simply doing a
'select count(*)' on the table takes some minutes) and lock the table
while it's happening, I can't really experiment, though I could
schedule/agree downtime for the system in the middle of the night at
some point, and try rebuilding the index then.
Problem 2 ... tentative (not readily reproducible and haven't managed
to rule out the possibility of a bug in my code yet) ... a long
running consumer process (which establishes a connection to the
database using libpq, and keeps the connection open indefinitely) was
reporting that the query in question was taking 4.5 seconds, but
starting up the psql command-line tool and running the same query
reported a 200-300 millisecond duration. Could this be a problem in
the database server process handling the connection? or in the libpq
code handling it? The disparity between the times taken for queries
as logged in psql and within the consumer application only seemed to
occur for this polling query (which gets executed very often), not
for other queries the consumer program did on other tables.
Restarting the consumer process 'cured' this ... now I'm waiting to
see if this behavior returns.
Anyone seen anything like this or know what might cause it?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Why is my (empty) partial index query slow?
@ 2006-06-22 16:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Frith-Macdonald <richard@tiptree.demon.co.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2006-06-22 16:04 UTC (permalink / raw)
To: Richard Frith-Macdonald <richard@tiptree.demon.co.uk>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Why is my (empty) partial index query slow?
@ 2006-06-23 00:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2006-06-23 00:43 UTC (permalink / raw)
To: Richard Frith-Macdonald <richard@tiptree.demon.co.uk>; +Cc: pgsql-performance
Richard Frith-Macdonald <richard@tiptree.demon.co.uk> writes:
> What has confused me is why a query using an empty index should be
> slow, irrespective of the state of the table that the index applies to.
Is it actually empty, or have you just deleted-and-not-yet-vacuumed
all the rows in the index?
I had hoped to see comparative EXPLAIN ANALYZE output for the fast and
slow cases. Maybe when it gets slow again you could redo the explain.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-06-23 00:43 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-16 07:17 Why is my (empty) partial index query slow? Richard Frith-Macdonald <richard@tiptree.demon.co.uk>
2006-06-22 16:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-23 00:43 ` Tom Lane <tgl@sss.pgh.pa.us>
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