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

Pailloncy Jean-Gerard <jg(at)rilk(dot)com> writes:
> I redo the test, with a freshly installed data directory. Same result.

What "same result"? You only ran it up to 2K rows, not 2M. In any
case, EXPLAIN without ANALYZE is pretty poor ammunition for complaining
that the planner made the wrong choice. I ran the same test case,
and AFAICS the indexscan is the right choice at 2K rows:

regression=# explain analyze select count(*) from (select distinct on (val) * from test) as foo;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=105.24..105.25 rows=1 width=0) (actual time=41.561..41.565 rows=1 loops=1)
-> Unique (cost=0.00..79.63 rows=2048 width=8) (actual time=0.059..32.459 rows=2048 loops=1)
-> Index Scan using testval on test (cost=0.00..74.51 rows=2048 width=8) (actual time=0.049..13.197 rows=2048 loops=1)
Total runtime: 41.683 ms
(4 rows)

regression=# set enable_indexscan TO 0;
SET
regression=# explain analyze select count(*) from (select distinct on (val) * from test) as foo;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=179.96..179.97 rows=1 width=0) (actual time=59.567..59.571 rows=1 loops=1)
-> Unique (cost=144.12..154.36 rows=2048 width=8) (actual time=21.438..50.434 rows=2048 loops=1)
-> Sort (cost=144.12..149.24 rows=2048 width=8) (actual time=21.425..30.589 rows=2048 loops=1)
Sort Key: test.val
-> Seq Scan on test (cost=0.00..31.48 rows=2048 width=8) (actual time=0.014..9.902 rows=2048 loops=1)
Total runtime: 60.265 ms
(6 rows)

regards, tom lane