Обсуждение: BUG #2342: Extremely bad performance on a specific query, compared to 7.4

The following bug has been logged online:

Bug reference:      2342
Logged by:          Mattias Kregert
Email address:      mattias@kregert.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

"Mattias Kregert" <mattias@kregert.se> writes:
> SELECT id FROM pics WHERE
> (file='3a76h3gd873kmn7ckd76' AND archived IS NULL) OR
> (file='jj38kmc983lmn2547cx0' AND archived IS NULL) OR
> ...

Could we see EXPLAIN ANALYZE results for this query from both the 7.4
and 8.1 systems?  Since you haven't even told us what indexes are
available on the table, there's not a lot we can do with this report.

            regards, tom lane