subselect and count (DISTINCT expression [ , ... ] ) performances

On Fri, 26 Dec 2008 19:13:48 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> The subselect syntax certainly seems like the one most likely to
> work across different SQL implementations. WITH is a pretty

subselects actually works on mysql too but on a 1M table with about
300K unique columns it performs more than 4 times slower than
select (distinct a,b) from table

18sec vs. 4sec

Times were similar for innodb and myisam.

Postgresql needs 17sec with subselect.

I didn't try to see how both db could perform with indexes.

mysql performance is impressive. I thought that most of the time
would be spent on "distinct" where postgresql shouldn't suffer from
its "count" implementation. But well still 300K rows to count on 1M
aren't few.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it