Index used under 7.2 slows down query? - Mailing list pgsql-general

From Jeff Boes
Subject Index used under 7.2 slows down query?
Date
Msg-id agcim8$12ho$1@news.hub.org
Whole thread Raw
Responses Re: Index used under 7.2 slows down query?
List pgsql-general
We're trying to track down a rather large performance difference between
7.2 and 7.1.  Statistics gathered show that one query takes a lot longer
under 7.2 than under 7.1.

7.2:

SELECT oid,* FROM job_queue
WHERE daemon_id IS NULL AND method_id = ?
AND status_id = ? AND startable_time <= now()
ORDER BY startable_time
FOR UPDATE
LIMIT ? OFFSET ? => {
    'count' => '20142',
    'avg' => ' 0.862',
    'time' => '17366.107' }

--that is, we ran this query 20,142 times, it averaged about 0.862
wallclock-seconds per invocation, for a total of 17,366
wallclock-seconds.  The timing is taken at the level of the Perl DBD::Pg
interface, starting the clock just before the API is called, and stopping
it immediately after it returns.  The intervening executable code is not
identical on the two systems, but this appears to be by far the query
most affected, so I'm inclined to think it is slowdown in the database.

7.1:

SELECT oid,* FROM job_queue
WHERE daemon_id IS NULL AND method_id = ?
AND status_id = ? AND startable_time <= now()
ORDER BY startable_time
FOR UPDATE
LIMIT ? OFFSET ? => {
    'count' => '14278',
    'avg' => ' 0.014',
    'time' => '200.584' }

--a big change in the average and total time.  Here's the query plans:

7.2:

Limit  (cost=1845.99..3691.98 rows=1 width=97)
  ->  Index Scan using ix_job_queue_1 on job_queue  (cost=0.00..76419.98 rows=41 width=97)


7.1:

Limit  (cost=1.44..1.44 rows=1 width=72)
  ->  Sort  (cost=1.44..1.44 rows=1 width=72)
        ->  Seq Scan on job_queue  (cost=0.00..1.43 rows=1 width=72)

On both schemas, the table has an index (btree, non-unique) on the
TIMESTAMP column "startable_time".  (That's the "ix_job_queue_1" in the
plan above.)  There is also an index (btree, unique) on the primary key,
a sequence number.  On the 7.2 schema, I added two more indexes, but
they are not on columns used in the WHERE clause.  The table is vacuumed
once an hour on 7.2, and once a day on 7.1.  (The slowdown predates the
change to the vacuum schedule.)

The table undergoes around 15,000-20,000 inserts per day, and the column
"startable_time" is usually within a few hours (future) of the current
time at the insert.


--
Jeff Boes                                      vox 616.226.9550 ext 24
Database Engineer                                     fax 616.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise

pgsql-general by date:

Previous
From: Robert L Mathews
Date:
Subject: Re: I am being interviewed by OReilly
Next
From: Andrew Sullivan
Date:
Subject: Re: Bug in 7.2.1? -- ERROR: Adding columns with defaults is not implemented.