Show estimated number of groups for IncrementalSort in EXPLAIN
Hi hackers,
In [0]/messages/by-id/6642af90-561c-4f0c-9d5b-7e288e6e7f84@gmail.com, 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]: /messages/by-id/6642af90-561c-4f0c-9d5b-7e288e6e7f84@gmail.com
/messages/by-id/6642af90-561c-4f0c-9d5b-7e288e6e7f84@gmail.com
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/
Attachments:
v1-0001-Show-estimated-number-of-groups-for-IncrementalSo.patchtext/x-patch; charset=UTF-8; name=v1-0001-Show-estimated-number-of-groups-for-IncrementalSo.patchDownload+26-8
Hi, thanks for working on this.
On 10/06/26 17:50, Ilia Evdokimov wrote:
*Estimated Groups: 100*
I think it's useful to know the estimated number of groups to understand
why the planner chooses IncrementalSort. The patch looks clean.
There are no tests for this change, although it should be safe, you might
want to consider adding one to `src/test/regress/sql/incremental_sort.sql`.
Best regards,
Enrique.
On 6/17/26 01:51, Enrique Sánchez wrote:
I think it's useful to know the estimated number of groups to
understand why the planner chooses IncrementalSort. The patch looks clean.There are no tests for this change, although it should be safe, you
might want to consider adding one to
`src/test/regress/sql/incremental_sort.sql`.
Memoize has similar planner estimation output in EXPLAIN, but has no
regression tests for it either. Besides, in `inremental_sort` test all
EXPLAIN calls use COSTS OFF, so the new line would not appear there at
all. To be honest, I don't see any reason to display it in regression tests.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/