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
