Re: Add proper planner support for ORDER BY / DISTINCT aggregates

Hello,

While playing with the patch I found a situation where the performance
may be degraded compared to previous versions.

The test case below.
If you create a proper index for the query (a,c), version 16 wins. On my
notebook, the query runs ~50% faster.
But if there is no index (a,c), but only (a,b), in previous versions the
planner uses it, but with this patch a full table scan is selected.

create table t (a text, b text, c text);
insert into t (a,b,c) select x,y,x from generate_series(1,100) as x,
generate_series(1,10000) y;
create index on t (a,b);
vacuum analyze t;

explain (analyze, buffers)
select a, array_agg(c order by c) from t group by a;

v 14.5
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.42..46587.76 rows=100 width=34) (actual
time=3.077..351.526 rows=100 loops=1)
   Group Key: a
   Buffers: shared hit=193387 read=2745
   ->  Index Scan using t_a_b_idx on t  (cost=0.42..41586.51
rows=1000000 width=4) (actual time=0.014..155.095 rows=1000000 loops=1)
         Buffers: shared hit=193387 read=2745
 Planning:
   Buffers: shared hit=9
 Planning Time: 0.059 ms
 Execution Time: 351.581 ms
(9 rows)

v 16
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=128728.34..136229.59 rows=100 width=34) (actual
time=262.930..572.915 rows=100 loops=1)
   Group Key: a
   Buffers: shared hit=5396, temp read=1950 written=1964
   ->  Sort  (cost=128728.34..131228.34 rows=1000000 width=4) (actual
time=259.423..434.105 rows=1000000 loops=1)
         Sort Key: a, c
         Sort Method: external merge  Disk: 15600kB
         Buffers: shared hit=5396, temp read=1950 written=1964
         ->  Seq Scan on t  (cost=0.00..15396.00 rows=1000000 width=4)
(actual time=0.005..84.104 rows=1000000 loops=1)
               Buffers: shared hit=5396
 Planning:
   Buffers: shared hit=9
 Planning Time: 0.055 ms
 Execution Time: 575.146 ms
(13 rows)

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company