Horton

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

Tomas Vondra <tomas.vondra@2ndquadrant.com>

View thread View on postgr.es
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: James Coleman <jtc331@gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>, Michael Paquier <michael@paquier.xyz>, Rafia Sabih <rafia.pghackers@gmail.com>, Peter Geoghegan <pg@bowt.ie>, Simon Riggs <simon@2ndquadrant.com>, Shaun Thomas <shaun.thomas@2ndquadrant.com>, Dmitry Dolgov <9erthalion6@gmail.com>, Alexander Korotkov <a.korotkov@postgrespro.ru>, Tom Lane <tgl@sss.pgh.pa.us>, PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>, Andreas Karlsson <andreas@proxel.se>
Date: 2020-03-28T22:59:22Z
Lists: pgsql-hackers

Attachments

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

Commits

  1. Further adjustments to Hashagg EXPLAIN ANALYZE output

  2. Rework EXPLAIN format for incremental sort

  3. Fix typos and improve incremental sort comments

  4. Stabilize incremental_sort tests

  5. Minor improvements in Incremental Sort explain

  6. Consider Incremental Sort paths at additional places

  7. Fix representation of SORT_TYPE_STILL_IN_PROGRESS.

  8. Fix failures in incremental_sort due to number of workers

  9. Fix show_incremental_sort_info with force_parallel_mode

  10. Implement Incremental Sort

  11. Fix handling of "Subplans Removed" field in EXPLAIN output.

  12. Fix EXPLAIN (SETTINGS) to follow policy about when to print empty fields.

  13. Ensure plpgsql result tuples have the right composite type marking.

  14. Propagate sort instrumentation from workers back to leader.

  15. Make new regression test case parallel-safe, and improve its output.

  16. Push limit through subqueries to underlying sort, where possible.

  17. Fix inappropriate printing of never-measured times in EXPLAIN.

  18. Fix some infelicities in EXPLAIN output for parallel query plans.