Message-ID: <017612dc-3bce-0d08-096e-db8605df9dbe@postgrespro.ru>
Date: Mon, 7 Nov 2022 23:37:55 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.2.2
Subject: Re: Add proper planner support for ORDER BY / DISTINCT aggregates
Content-Language: en-US
To: Ronan Dunklau <ronan.dunklau@aiven.io>,
 David Rowley <dgrowleyml@gmail.com>, Justin Pryzby <pryzby@telsasoft.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Richard Guo <guofenglinux@gmail.com>,
 pgsql-hackers@lists.postgresql.org, Ranier Vilela <ranier.vf@gmail.com>
References: <3163474.aeNJFYEL58@aivenronan> <2202180.iZASKD2KPV@aivenlaptop>
 <43b1c6fc-e2f4-c252-5ce5-3db98c6b71d4@postgrespro.ru>
 <3699971.kQq0lBPeGt@aivenlaptop>
From: Pavel Luzanov <p.luzanov@postgrespro.ru>
In-Reply-To: <3699971.kQq0lBPeGt@aivenlaptop>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/017612dc-3bce-0d08-096e-db8605df9dbe%40postgrespro.ru>
Precedence: bulk

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