count distinct slow?

Hello.

As a note, I ran into the following today (doing a select distinct is fast,
doing a count distinct is significantly slower?)

assume a table "issue" with a COLUMN nodename character varying(64);, 7.5M
rows...

select distinct substring(nodename from 1 for 9) from issue;

-- 5.8s

select count(distinct substring(nodename from 1 for 9)) from issue;

-- 190s

SELECT COUNT(*) FROM (SELECT DISTINCT substring(nodename from 1 for 9) FROM
issue) as temp;

-- 5.5s

I have an index on nodename's substring 1 for 9.

It struck me as odd that a count distinct would be far slower than
selecting distinct rows themselves. Apparently there are other workarounds
people have come up with as well [1]. Just mentioning in case it's helpful.
Cheers!
-roger-

[1]
http://stackoverflow.com/questions/11250253/postgresql-countdistinct-very-slow/14732410#14732410

explains:

explain analyze select count(distinct substring(nodename from 1 for 9))
from issue;

Aggregate (cost=222791.77..222791.78 rows=1 width=16) (actual
time=190641.069..190641.071 rows=1 loops=1)
-> Seq Scan on issue (cost=0.00..185321.51 rows=7494051 width=16)
(actual time=0.016..3487.694 rows=7495551 loops=1)
Total runtime: 190641.182 ms

explain analyze select distinct substring(nodename from 1 for 9) from
issue;

HashAggregate (cost=222791.77..222846.45 rows=4375 width=16) (actual
time=6276.578..6278.004 rows=6192 loops=1)
-> Seq Scan on issue (cost=0.00..204056.64 rows=7494051 width=16)
(actual time=0.058..4293.976 rows=7495551 loops=1)
Total runtime: 6278.564 ms

explain analyze SELECT COUNT(*) FROM (SELECT DISTINCT substring(nodename
from 1 for 9) FROM issue) as temp;

Aggregate (cost=222901.14..222901.15 rows=1 width=0) (actual
time=5195.025..5195.025 rows=1 loops=1)
-> HashAggregate (cost=222791.77..222846.45 rows=4375 width=16) (actual
time=5193.121..5194.454 rows=6192 loops=1)
-> Seq Scan on issue (cost=0.00..204056.64 rows=7494051 width=16)
(actual time=0.035..3402.834 rows=7495551 loops=1)
Total runtime: 5195.160 ms