| 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 |
| 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 |