Postgres group by expressions evaluation

Question

When "group by" expression evaluated? When Postgres decides, that two expressions 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 possible overhead, is it aliases in "group by" performed better than expressions? For example, will it be better than version with "group by" using expressions:

db=> select pg_sleep(1)::varchar as e group by e;
 e 
---

(1 row)

Time: 1001.688 ms

Even if ASTs are different, 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

Answer 1

I'm not sure I understand the question, so maybe answering the wrong one. Yet - here it is. It groups result after getting it. I was surprised pg_sleep was not re executed with same cast. Smart planner is smart. Aggregation method does not seem to change it. And regarding aliasing - if planner understood that expression is same and no need to execute function again it did it without help of alias, so probably alias wont 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 such behaviour 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 explain analyze select pg_sleep(random())::text group by pg_sleep(random())::text; to reexecute pg_sleep in group by