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

BUG #2342: Extremely bad performance on a specific query, compared to 7.4

From: "Mattias Kregert" <mattias(at)kregert(dot)se>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2342: Extremely bad performance on a specific query, compared to 7.4
Date: 2006-03-20 15:07:39
Message-ID: 200603201507.k2KF7dbo061926@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      2342
Logged by:          Mattias Kregert
Email address:      mattias(at)kregert(dot)se
PostgreSQL version: 8.1
Operating system:   Linux
Description:        Extremely bad performance on a specific query, compared
to 7.4
Details: 

Query:
------
SELECT id FROM pics WHERE
(file='3a76h3gd873kmn7ckd76' AND archived IS NULL) OR
(file='jj38kmc983lmn2547cx0' AND archived IS NULL) OR
...
[something like 500 lines]

Table info:
-----------
The pics table has 200K rows.
Index#1 on id.
Index#2 on file.
Analyze done.

Problem:
--------
With pg7.4, on an antique pII-233mhz, this query takes approximately 0.5
seconds.

With pg8.1, on a Cel-2.67ghz, the same query takes around 70 seconds @ 99%
cpu usage.

I would expect it to take less than .05 seconds, since the cpu is 10 times
faster, the disks are faster, more memory and so on... So what makes pg8.1
1400 times slower than pg7.4?

According to 'explain' it is doing a Bitmap Index Scan for every line in the
query. Is there a way to turn off this new dynamic bitmap feature, or tune
it in some way?

/M

Responses

pgsql-bugs by date

Next:From: Tom HollinsDate: 2006-03-20 15:35:39
Subject: Re: BUG #2327: Startup script for FC4 with selinux
Previous:From: Slavcho IvanovDate: 2006-03-20 13:05:04
Subject: BUG #2341: Wrong results when using index

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