sql - Postgres 按表达式评估分组

Question

什么时候评估“group by”表达式？当 Postgres 决定时，这两个表达式实际上相同？

例子:

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

由于可能的开销，“group by”中的别名是否比表达式表现得更好？例如，它会比使用表达式“group by”的版本更好吗:

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

(1 row)

Time: 1001.688 ms

即使 AST 不同，也不会重新计算表达式:

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

我不确定我是否理解问题，所以可能回答错了。然而——就在这里。它在得到它后对结果进行分组。我很惊讶 pg_sleep 没有用相同的类型转换重新执行。聪明的计划者是聪明的。聚合方法似乎并没有改变它。关于别名——如果规划器明白表达式是相同的并且不需要再次执行函数，那么它在没有别名帮助的情况下就做到了，所以别名可能不会改变任何东西。

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

最后我不确定这种行为是否符合直觉:

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

我希望 explain analyze select pg_sleep(random())::text group by pg_sleep(random())::text; 在 中重新执行 pg_sleep >分组依据