public inbox for [email protected]
help / color / mirror / Atom feedFrom: Pavel Luzanov <[email protected]>
To: Ronan Dunklau <[email protected]>
To: David Rowley <[email protected]>
To: Justin Pryzby <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Richard Guo <[email protected]>
Cc: [email protected]
Cc: Ranier Vilela <[email protected]>
Subject: Re: Add proper planner support for ORDER BY / DISTINCT aggregates
Date: Mon, 7 Nov 2022 23:37:55 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <3699971.kQq0lBPeGt@aivenlaptop>
References: <3163474.aeNJFYEL58@aivenronan>
<2202180.iZASKD2KPV@aivenlaptop>
<[email protected]>
<3699971.kQq0lBPeGt@aivenlaptop>
On 07.11.2022 20:30, Ronan Dunklau wrote:
> What I meant here is that disabling seqscans, the planner still chooses a full
> sort over a partial sort. The underlying index is the same, it is just a
> matter of choosing a Sort node over an IncrementalSort node. This, I think, is
> wrong: I can't see how it could be worse to use an incrementalsort in that
> case.
I finally get your point. And I agree with you.
> Maybe the original costing code for incremental sort was a bit too
> pessimistic.
In this query, incremental sorting lost just a little bit in cost:
164468.95 vs 162504.23.
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=155002.98..162504.23 rows=100 width=34) (actual
time=296.591..568.270 rows=100 loops=1)
Group Key: a
-> Sort (cost=155002.98..157502.98 rows=1000000 width=4) (actual
time=293.810..454.170 rows=1000000 loops=1)
Sort Key: a, c
Sort Method: external merge Disk: 15560kB
-> Index Scan using t_a_b_idx on t (cost=0.42..41670.64
rows=1000000 width=4) (actual time=0.021..156.441 rows=1000000 loops=1)
Settings: enable_seqscan = 'off'
Planning Time: 0.074 ms
Execution Time: 569.957 ms
(9 rows)
set enable_sort=off;
SET
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1457.58..164468.95 rows=100 width=34) (actual
time=6.623..408.833 rows=100 loops=1)
Group Key: a
-> Incremental Sort (cost=1457.58..159467.70 rows=1000000 width=4)
(actual time=2.652..298.530 rows=1000000 loops=1)
Sort Key: a, c
Presorted Key: a
Full-sort Groups: 100 Sort Method: quicksort Average Memory:
27kB Peak Memory: 27kB
Pre-sorted Groups: 100 Sort Method: quicksort Average
Memory: 697kB Peak Memory: 697kB
-> Index Scan using t_a_b_idx on t (cost=0.42..41670.64
rows=1000000 width=4) (actual time=0.011..155.260 rows=1000000 loops=1)
Settings: enable_seqscan = 'off', enable_sort = 'off'
Planning Time: 0.044 ms
Execution Time: 408.867 ms
--
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