Postgres Expression Evaluation Group
When is the "group by" expression evaluated? When does Postgres decide that two expressions are actually the same?
Example:
db=> \timing
Timing is on.
db=> select pg_sleep(1) group by pg_sleep(1)::varchar;
pg_sleep
----------
(1 row)
Time: 2002.416 ms
db=> select pg_sleep(1)::varchar group by pg_sleep(1)::varchar;
pg_sleep
----------
(1 row)
Time: 1001.367 ms
Because of the potential overhead, are the aliases in "group by" performed better than expressions? For example, is it better than the "group by" version using the expressions:
db=> select pg_sleep(1)::varchar as e group by e;
e
---
(1 row)
Time: 1001.688 ms
Even if the ASTs differ from each other, the expression will not be re-evaluated:
db=> select pg_sleep(1 + 0.5)::varchar group by pg_sleep(0.5 + 1)::varchar;
pg_sleep
----------
(1 row)
Time: 1500.971 ms
source to share
I'm not sure I understand this question, so maybe answering the wrong question. Nevertheless, this is it. It groups the result after receiving it. I was surprised that pg_sleep
it was not repeated with the same act. Smart planner is smart. The aggregation method doesn't seem to change it. And in regards to anti-aliasing - if the planner realized that the expression was the same and there was no need to execute the function again, it did it without the help of an alias, so the alias probably won't change anything.
so=# explain analyze select pg_sleep(1) group by pg_sleep(1)::varchar;
QUERY PLAN
-------------------------------------------------------------------------------------------------
HashAggregate (cost=0.02..0.04 rows=1 width=0) (actual time=2002.201..2002.203 rows=1 loops=1)
-> Result (cost=0.00..0.02 rows=1 width=0) (actual time=1001.093..1001.093 rows=1 loops=1)
Total runtime: 2002.236 ms
(3 rows)
Time: 2002.779 ms
so=# set enable_hashagg to off;
SET
so=# explain analyze select pg_sleep(1)::text group by pg_sleep(1)::varchar;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Group (cost=0.03..0.05 rows=1 width=0) (actual time=2002.219..2002.220 rows=1 loops=1)
-> Sort (cost=0.03..0.03 rows=1 width=0) (actual time=1001.111..1001.112 rows=1 loops=1)
Sort Key: ((pg_sleep(1::double precision))::character varying)
Sort Method: quicksort Memory: 25kB
-> Result (cost=0.00..0.02 rows=1 width=0) (actual time=1001.100..1001.100 rows=1 loops=1)
Total runtime: 2002.245 ms
(6 rows)
Time: 2002.564 ms
so=# explain analyze select pg_sleep(1)::varchar as e group by e;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Group (cost=0.03..0.04 rows=1 width=0) (actual time=1001.109..1001.110 rows=1 loops=1)
-> Sort (cost=0.03..0.03 rows=1 width=0) (actual time=1001.107..1001.108 rows=1 loops=1)
Sort Key: ((pg_sleep(1::double precision))::character varying)
Sort Method: quicksort Memory: 25kB
-> Result (cost=0.00..0.02 rows=1 width=0) (actual time=1001.098..1001.099 rows=1 loops=1)
Total runtime: 1001.132 ms
(6 rows)
Time: 1001.470 ms
Finally, I'm not sure if this behavior is intuitive:
so=# explain analyze select pg_sleep(random())::text group by pg_sleep(random())::varchar;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Group (cost=0.03..0.06 rows=1 width=0) (actual time=742.928..742.930 rows=1 loops=1)
-> Sort (cost=0.03..0.04 rows=1 width=0) (actual time=53.152..53.153 rows=1 loops=1)
Sort Key: ((pg_sleep(random()))::character varying)
Sort Method: quicksort Memory: 25kB
-> Result (cost=0.00..0.02 rows=1 width=0) (actual time=53.143..53.143 rows=1 loops=1)
Total runtime: 742.958 ms
(6 rows)
Time: 743.271 ms
so=# explain analyze select pg_sleep(random())::text group by pg_sleep(random())::text;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Group (cost=0.03..0.05 rows=1 width=0) (actual time=963.075..963.076 rows=1 loops=1)
-> Sort (cost=0.03..0.04 rows=1 width=0) (actual time=963.073..963.073 rows=1 loops=1)
Sort Key: ((pg_sleep(random()))::text)
Sort Method: quicksort Memory: 25kB
-> Result (cost=0.00..0.02 rows=1 width=0) (actual time=963.063..963.065 rows=1 loops=1)
Total runtime: 963.099 ms
(6 rows)
Time: 963.419 ms
I would expect to explain analyze select pg_sleep(random())::text group by pg_sleep(random())::text;
be re-executed pg_sleep
ingroup by
source to share