planner support functions: handle GROUP BY estimates ?

From: Justin Pryzby <pryzby(at)telsasoft(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: planner support functions: handle GROUP BY estimates ?
Date: 2019-11-19 19:34:21
Message-ID: 20191119193421.GS30362@telsasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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)

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Munro 2019-11-19 19:34:39 Re: logical decoding : exceeded maxAllocatedDescs for .spill files
Previous Message Thomas Munro 2019-11-19 18:58:16 Re: logical decoding : exceeded maxAllocatedDescs for .spill files