Message-ID: <9f61ddbf-2989-1536-b31e-6459370a6baa@postgrespro.ru>
Date: Sat, 5 Nov 2022 11:51:23 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.11.0
Subject: Re: Add proper planner support for ORDER BY / DISTINCT aggregates
Content-Language: en-US
To: David Rowley <dgrowleyml@gmail.com>, Justin Pryzby <pryzby@telsasoft.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Richard Guo <guofenglinux@gmail.com>,
 Ronan Dunklau <ronan.dunklau@aiven.io>, pgsql-hackers@lists.postgresql.org,
 Ranier Vilela <ranier.vf@gmail.com>
References: <3163474.aeNJFYEL58@aivenronan>
 <CAApHDvqS-p57j++gLMyQXmttk_MM3u243dWQ54FyW-7=4GP+1Q@mail.gmail.com>
 <CAMbWs49hcB-Mj3MJTOesL5=y4oU+md=zEvDYdCBrOSaDvXLRGQ@mail.gmail.com>
 <CAApHDvr3JQv4_H7vfu5Djtw0ubKwGDwvNtPuphtaQY4utgj7bQ@mail.gmail.com>
 <CAMbWs4_hAK6+0Gk=vZX+ikSFBx=6981iFLGkgLObDkvvGpjogg@mail.gmail.com>
 <CAApHDvrai=tCuUhH=YUvDnysivzbabz4pGPGz_3SytysWmyNdg@mail.gmail.com>
 <CAMbWs49Xw1Sb4KOZx6CMGBjiCNvGCcAyXkwWRGNZivyYv2fGMg@mail.gmail.com>
 <CAApHDvqtmK7AprGs=uh2+BNyew6L3OpC7hPSe43uD-7m1Dtx7w@mail.gmail.com>
 <1138716.1659282591@sss.pgh.pa.us>
 <CAApHDvpQ+LmokunMogxc1Ba+AOptP=sHqruCWeC8Xgv2XDbiDg@mail.gmail.com>
 <20220817015755.GB26426@telsasoft.com>
 <CAApHDvowz-gfbuq=8iwHebRcy4VpFuJv52B3jCcXVi+CPFrd1A@mail.gmail.com>
From: Pavel Luzanov <p.luzanov@postgrespro.ru>
In-Reply-To: 
 <CAApHDvowz-gfbuq=8iwHebRcy4VpFuJv52B3jCcXVi+CPFrd1A@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/9f61ddbf-2989-1536-b31e-6459370a6baa%40postgrespro.ru>
Precedence: bulk

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