public inbox for [email protected]
help / color / mirror / Atom feedFrom: Pavel Luzanov <[email protected]>
To: David Rowley <[email protected]>
To: Justin Pryzby <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Richard Guo <[email protected]>
Cc: Ronan Dunklau <[email protected]>
Cc: [email protected]
Cc: Ranier Vilela <[email protected]>
Subject: Re: Add proper planner support for ORDER BY / DISTINCT aggregates
Date: Sat, 5 Nov 2022 11:51:23 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAApHDvowz-gfbuq=8iwHebRcy4VpFuJv52B3jCcXVi+CPFrd1A@mail.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>
<[email protected]>
<CAApHDvpQ+LmokunMogxc1Ba+AOptP=sHqruCWeC8Xgv2XDbiDg@mail.gmail.com>
<[email protected]>
<CAApHDvowz-gfbuq=8iwHebRcy4VpFuJv52B3jCcXVi+CPFrd1A@mail.gmail.com>
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
view thread (64+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Add proper planner support for ORDER BY / DISTINCT aggregates
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox