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

Поиск
Список
Период
Сортировка
От Ivan Sergio Borgonovo
Тема subselect and count (DISTINCT expression [ , ... ] ) performances
Дата
Msg-id 20081227121433.1b40edd2@dawn.webthatworks.it
обсуждение исходный текст
Ответ на Re: WITH AS vs subselect was: count (DISTINCT expression [ , ... ] ) and documentation  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Craig Ringer
Дата:
Сообщение: Re: "disappearing" rows in temp table, in recursing trigger
Следующее
От: Alvaro Herrera
Дата:
Сообщение: Re: Automatic CRL reload