planner support functions: handle GROUP BY estimates ?

Поиск
Список
Период
Сортировка
От Justin Pryzby
Тема planner support functions: handle GROUP BY estimates ?
Дата
Msg-id 20191119193421.GS30362@telsasoft.com
обсуждение исходный текст
Ответы Re: planner support functions: handle GROUP BY estimates ?  (Justin Pryzby <pryzby@telsasoft.com>)
Список pgsql-hackers
Tom implemented "Planner support functions":
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a391ff3c3d418e404a2c6e4ff0865a107752827b
https://www.postgresql.org/docs/12/xfunc-optimization.html

I wondered whether there was any consideration to extend that to allow
providing improved estimates of "group by".  That currently requires manually
by creating an expression index, if the function is IMMUTABLE (which is not
true for eg.  date_trunc of timestamptz).

ts=# explain analyze SELECT date_trunc('day', start_time) FROM child.alu_amms_201911 GROUP BY 1;
 HashAggregate  (cost=87.34..98.45 rows=889 width=8) (actual time=1.476..1.482 rows=19 loops=1)

ts=# explain analyze SELECT date_trunc('year', start_time) FROM child.alu_amms_201911 GROUP BY 1;
 HashAggregate  (cost=87.34..98.45 rows=889 width=8) (actual time=1.499..1.500 rows=1 loops=1)

ts=# CREATE INDEX ON child.alu_amms_201911 (date_trunc('year',start_time));
ts=# ANALYZE child.alu_amms_201911;
ts=# explain analyze SELECT date_trunc('year', start_time) FROM child.alu_amms_201911 GROUP BY 1;
 HashAggregate  (cost=87.34..87.35 rows=1 width=8) (actual time=1.414..1.414 rows=1 loops=1)



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

Предыдущее
От: Thomas Munro
Дата:
Сообщение: Re: logical decoding : exceeded maxAllocatedDescs for .spill files
Следующее
От: Thomas Munro
Дата:
Сообщение: Re: logical decoding : exceeded maxAllocatedDescs for .spill files