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: Tom Lane <tgl@sss.pgh.pa.us>, 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>, PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>, Andreas Karlsson <andreas@proxel.se>
Date: 2020-04-08T00:39:50Z
Lists: pgsql-hackers 
Hi,

I've pushed the second part of this patch series, adding incremental
sort to additional places in the planner. As explained in the commit
message (and somewhere in this thread) we've decided to only update some
of the places that require sorted input (and do create_sort). This might
be overly cautious, I expect we'll add it to more places in the future.


As for the remaining part tweaking add_partial_path to also consider
startup cost (a bit confusingly 0001 - 0003 in the v54 patchset), I've
decided not to push it now and leave it for v14. The add_partial_path
change is simple, but I came to the conclusion that the "precheck"
function should be modified to follow the same logic - it would be a bit
strange if add_partial_path_precheck considered only total cost and
add_partial_path considered both startup and total cost. It would not
matter for most places because the add_partial_path_precheck is ony used
in join planning, but it's still strange.

I could have modified the add_partial_path_precheck too, but looking at
add_path_precheck we'd probably need to compute required_outer so that
we only compare startup_cost when really useful. Or we might simply
consider startup_cost every time and leave that up to add_partial_path,
but then that would be another difference in behavior.

That seems like way too much stuff to rework on the last day of the last
commitfest. It does mean we'll fail to generate the cheapest plan in some
cases (e.g. with LIMIT, there's an example in [1]) but that's a
pre-existing condition, not something introduced by incremental sort.

regards


[1] https://www.postgresql.org/message-id/20190720132244.3vgg2uynfpxh3me5%40development

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