Optimizer on sort aggregate

Поиск
Список
Период
Сортировка
От Feng Tian
Тема Optimizer on sort aggregate
Дата
Msg-id CAFjtmHU3Obf5aSpWY7i18diapvjg-418hYySdqUuYhXZtjChhg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Optimizer on sort aggregate
Re: Optimizer on sort aggregate
Список pgsql-hackers
Hi,

Consider the following queries.

create table t(i int, j int, k int, t text);
insert into t select i, i % 100, i % 1000, 'AABBCCDD' || i from generate_series(1, 1000000) i;

ftian=# explain select count(distinct j) from t group by t, i;
                               QUERY PLAN                              
------------------------------------------------------------------------
 GroupAggregate  (cost=158029.84..178029.84 rows=1000000 width=22)
   ->  Sort  (cost=158029.84..160529.84 rows=1000000 width=22)
         Sort Key: t, i
         ->  Seq Scan on t  (cost=0.00..17352.00 rows=1000000 width=22)
(4 rows)


The query,
select count(distinct j) from t group by t, i;

runs for 35 seconds.  However, if I change the query to,
select count(distinct j) from t group by i, t;  -- note switching the ordering
select count(distinct j) from t group by decode(t, 'escape'), i; -- convert t to bytea

Run times are just about 5 and 6.5 seconds.  The reason is clear, compare a string with collation is slow, which is well understood by pg hackers.   However, here, the sorting order is forced by the planner, not user.   Planner can do the following optimizations,

1. for the sort we generated for sort agg, planner can switch column ordering, put int before string,
2. for the sort we generated for sort agg, use bytea compare instead of string compare.

They will bring big improvement to this common query.   Is this something reasonable? 

Thanks,




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

Предыдущее
От: Caleb Welton
Дата:
Сообщение: Issue with mkdtemp() in port.h
Следующее
От: Peter Geoghegan
Дата:
Сообщение: Re: json function volatility