Re: [PATCH] Incremental sort (was: PoC: Partial sort)

Hi,

Attached is my take on simplification of the useful pathkeyes thing. It
keeps the function, but it truncates query_pathkeys to only members with
EC members in the relation. I think that's essentially the optimization
you've proposed.

I've also noticed an issue in explain output. EXPLAIN ANALYZE on a simple
query gives me this:

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Merge (cost=66.30..816060.48 rows=8333226 width=24) (actual time=6.464..19091.006 rows=10000000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Incremental Sort (cost=66.28..729188.13 rows=4166613 width=24) (actual time=1.836..13401.109 rows=3333333 loops=3)
Sort Key: a, b, c
Presorted Key: a, b
Full-sort Groups: 4156 (Methods: quicksort) Memory: 30kB (avg), 30kB (max)
Presorted Groups: 4137 (Methods: quicksort) Memory: 108kB (avg), 111kB (max)
Full-sort Groups: 6888 (Methods: ) Memory: 30kB (avg), 30kB (max)
Presorted Groups: 6849 (Methods: ) Memory: 121kB (avg), 131kB (max)
Full-sort Groups: 6869 (Methods: ) Memory: 30kB (avg), 30kB (max)
Presorted Groups: 6816 (Methods: ) Memory: 128kB (avg), 132kB (max)
-> Parallel Index Scan using t_a_b_idx on t (cost=0.43..382353.69 rows=4166613 width=24) (actual time=0.033..9346.679 rows=3333333 loops=3)
Planning Time: 0.133 ms
Execution Time: 23998.669 ms
(15 rows)

while with incremental sort disabled it looks like this:

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Gather Merge (cost=734387.50..831676.35 rows=8333226 width=24) (actual time=5597.978..14967.246 rows=10000000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=734387.47..744804.00 rows=4166613 width=24) (actual time=5584.616..7645.711 rows=3333333 loops=3)
Sort Key: a, b, c
Sort Method: external merge Disk: 111216kB
Worker 0: Sort Method: external merge Disk: 109552kB
Worker 1: Sort Method: external merge Disk: 112112kB
-> Parallel Seq Scan on t (cost=0.00..105361.13 rows=4166613 width=24) (actual time=0.011..1753.128 rows=3333333 loops=3)
Planning Time: 0.048 ms
Execution Time: 19682.582 ms
(11 rows)

So I think there's a couple of issues:

1) Missing worker identification (Worker #).

2) Missing method for workers (we have it for the leader, though).

3) I'm not sure why the lable is "Methods" instead of "Sort Method", and
why it's in parenthesis.

4) Not sure having two lines for each worker is a great idea.

5) I'd probably prefer having multiple labels for avg/max memory values,
instead of (avg) and (max) notes. Also, I think we use "peak" in this
context instead of "max".

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services