BUG #4462: Adding COUNT to query causes massive slowdown

The following bug has been logged online:

Bug reference:      4462
Logged by:          Jussi Pakkanen
Email address:      jpakkane@gmail.com
PostgreSQL version: 8.3.3
Operating system:   Ubuntu x86 8/04
Description:        Adding COUNT to query causes massive slowdown
Details:

I have a table in the following format

code CHARACTER(9) NOT NULL
text VARCHAR(200)

I have built an INDEX on "code", VACUUMed and ANALYZEd the table.

I have about 32 million rows and roughly 200 000 unique "code" elements.

I determine the unique codes using the following SQL query:



EXPLAIN SELECT DISTINCT code FROM log;
                             QUERY PLAN
----------------------------------------------------------------------------
-----------
 Unique  (cost=0.00..1384173.89 rows=6393 width=10)
   ->  Index Scan using codeindex on log  (cost=0.00..1303930.83
rows=32097224 width=10)
(2 rows)

This takes about 4 minutes (it's a slow machine) but pretty much works as
expected.

However when I try to count the amount of distinct codes, I get this:


EXPLAIN SELECT COUNT(DISTINCT code) FROM log;
                                   QUERY PLAN
----------------------------------------------------------------------------
-----
 Aggregate  (cost=100801488.30..100801488.31 rows=1 width=10)
   ->  Seq Scan on log  (cost=100000000.00..100721245.24 rows=32097224
width=10)
(2 rows)


For some reason PostgreSQL wants to do a full table scan in this case. This
takes over 11 minutes.

Transferring the result set from the first query to a Python client program
and calculating the lines there takes about 4 seconds. This makes pg over
100 times slower than the naive implementation.

If I do the same COUNT using a view, it uses the index and is fast:

CREATE VIEW distcode AS SELECT DISTINCT code FROM log;

EXPLAIN SELECT COUNT(*) FROM distcode;
                                         QUERY PLAN

----------------------------------------------------------------------------
-----------------
 Aggregate  (cost=1384253.81..1384253.82 rows=1 width=0)
   ->  Unique  (cost=0.00..1384173.89 rows=6393 width=10)
         ->  Index Scan using codeindex on log  (cost=0.00..1303930.83
rows=320972

I tried setting seq_scan to off. It did not help.

Due to reasons beyond my control, I can't test version 8.3.4 until the next
Ubuntu is released (at the end of this month).