Re: 8.1 count(*) distinct: IndexScan/SeqScan

Hi,

After few test, the difference is explained by the
effective_cache_size parameter.

with effective_cache_size=1000 (default)
the planner chooses the following plan
postgres=# explain select count(*) from (select distinct on (val) *
from test) as foo;
QUERY PLAN
------------------------------------------------------------------------
--------
Aggregate (cost=421893.64..421893.65 rows=1 width=0)
-> Unique (cost=385193.48..395679.24 rows=2097152 width=8)
-> Sort (cost=385193.48..390436.36 rows=2097152 width=8)
Sort Key: test.val
-> Seq Scan on test (cost=0.00..31252.52
rows=2097152 width=8)
(5 rows)

with effective_cache_size=15000
the planner chooses the following plan
postgres=# explain select count(*) from (select distinct on (val) *
from test) as foo;
QUERY PLAN
------------------------------------------------------------------------
------------------
Aggregate (cost=101720.39..101720.40 rows=1 width=0)
-> Unique (cost=0.00..75505.99 rows=2097152 width=8)
-> Index Scan using testval on test (cost=0.00..70263.11
rows=2097152 width=8)
(3 rows)

I test some other values for effective_cache_size.
The switch from seq to index scan happens between 9900 and 10000 for
effective_cache_size.

I have my sql server on a OpenBSD 3.8 box with 1 Gb of RAM with
nothing else running on it.
I setup the cachepercent to 25. I expect to have 25% of 1 Gb of RAM
(256 Mb) as file cache.
effective_cache_size=15000 means 15000 x 8K of OS cache = 120,000 Kb
which is lower than my 256 MB of disk cache.

I recall the result of my precedent test.
#rows 2097152
IndexScan 1363396,581s
SeqScan 98758,445s
Ratio 13,805
So the planner when effective_cache_size=15000 chooses a plan that is
13 times slower than the seqscan one.

I did not understand where the problem comes from.
Any help welcome.

Cordialement,
Jean-Gérard Pailloncy