On Fri, 26 Dec 2008 19:13:48 -0500
Tom Lane <tgl@sss.pgh.pa.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