Show estimated number of groups for IncrementalSort in EXPLAIN

From: Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com>
To: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Show estimated number of groups for IncrementalSort in EXPLAIN
Date: 2026-06-10 15:50:33
Message-ID: 10682fef-3748-43f5-a932-7adcdd9bd2b8@tantorlabs.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

In [0], a question was raised whether we should expose IncrementalSort
group estimation in EXPLAIN, as we did for Memoize. Knowing the
estimated number of groups helps understand why the planner chose
IncrementalSort whether a bad estimate is to blame for a sub-optimal plan.

Example of EXPLAIN:

```
CREATE TABLE t (a int, b int, c int);
CREATE INDEX ON t (a);
INSERT INTO t SELECT i % 100, (random() * 1000)::int, (random() *
1000)::int FROM generate_series(1, 100000) i;
ANALYZE t;
SET enable_seqscan = off;

EXPLAIN ANALYZE SELECT * FROM t ORDER BY a, b;
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Incremental Sort  (cost=90.80..10302.90 rows=100000 width=12) (actual
time=6.715..29.592 rows=100000.00 loops=1)
   Sort Key: a, b
   Presorted Key: a
*Estimated Groups: 100*
   Full-sort Groups: 100  Sort Method: quicksort  Average Memory: 27kB 
Peak Memory: 27kB
   Pre-sorted Groups: 100  Sort Method: quicksort  Average Memory:
56kB  Peak Memory: 56kB
   Buffers: shared hit=54201 dirtied=1 written=1
   ->  Index Scan using t_a_idx on t  (cost=0.29..4068.01 rows=100000
width=12) (actual time=0.346..20.403 rows=100000.00 loops=1)
         Index Searches: 1
         Buffers: shared hit=54201 dirtied=1 written=1
 Planning:
   Buffers: shared hit=21
 Planning Time: 0.411 ms
 Execution Time: 30.530 ms
(14 rows)
```

Thoughts?

[0]:
https://www.postgresql.org/message-id/6642af90-561c-4f0c-9d5b-7e288e6e7f84%40gmail.com

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/

Attachment Content-Type Size
v1-0001-Show-estimated-number-of-groups-for-IncrementalSo.patch text/x-patch 6.1 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Nazir Bilal Yavuz 2026-06-10 15:54:00 Re: ci: Generate crashlogs on Windows
Previous Message Bertrand Drouvot 2026-06-10 15:16:56 Re: Avoid orphaned objects dependencies, take 3