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

On Fri, Nov 29, 2019 at 03:01:46PM +0900, Michael Paquier wrote:
>On Sun, Sep 29, 2019 at 01:00:49AM +0200, Tomas Vondra wrote:
>> OK. I'll try extending the set of synthetic queries in [1] to also do
>> soemthing like this and generate similar plans.
>
>Any progress on that?
>
>Please note that the latest patch does not apply anymore, so a rebase
>is needed.  I am switching the patch as waiting on author for now.
>--

Ah, thanks for reminding me. I've added a couple more queries with two
joins (there only were queries with two joins, I haven't expected
another joint to make such difference, but seems I was wrong).

So yes, there seem to be 6 different GUCs / places where considering
incremental sort makes a difference (the numbers say how many of the
4960 tested combinations were affected)

- create_ordered_paths_parallel (50)
- create_partial_grouping_paths_2 (228)
- standard_join_search (94)
- add_paths_to_grouping_rel (2148)
- set_rel_pathlist (156)
- apply_scanjoin_target_to_paths (286)

Clearly some of the places are more important than others, plus there
are some overlaps (two GUCs producing the same plan, etc.).

Plus there are four GUCs that did not affect any queries at all:

- create_partial_grouping_paths
- gather_grouping_paths
- create_ordered_paths
- add_paths_to_grouping_rel_parallel

Anyway, this might serve as a way to prioritize the effort. All the
test changes are in the original repo at

   https://github.com/tvondra/incremental-sort-tests-2
   
and I'm also attaching the rebased patches - the changes were pretty
minor, hopefully that helps others (all the patches with dev GUCs are in

https://github.com/tvondra/postgres/tree/incremental-sort-20191129


regards

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