Efficient count(distinct x) query question

From: Sefer Tov <sefer(at)hotmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Efficient count(distinct x) query question
Date: 2011-01-16 17:14:55
Message-ID: BAY150-w37665BB920D0A4FACDE22FA8F50@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Hi,
I have several queries that perform something like:
select count(data) as count1, count(distinct data) as count2 from large_table group by user;
My problem is that this table contains about 500M records and the moment I perform a "count(distinct ...)" the planner always solved it using sorting (once the data is sorted this clearly becomes an easy problem to solve).
I was wondering whether PostgreSql will be introducing "hash of hashes" support for solving this (the first hash part of the "group by" HashAggregate and the inner hashes for tracking the distinct keys).
When I consider sorting large volumes of data at "n*log(n)" using external disk, then cannot hashing be faster (even if the inner-hashes also use some external storage). When "n" is fairly large, the logarithmic factor becomes a dominant factor.
I'd love to hear thoughts and ideas on that.
Thanks, Sefer.

Browse pgsql-general by date

  From Date Subject
Next Message Andy Colson 2011-01-16 17:20:17 Re: How to generate unique invoice numbers for each day
Previous Message Andrus Moor 2011-01-16 17:00:58 Re: How to generate unique invoice numbers for each day