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

On Tue, 30 Jul 2019 at 02:17, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
wrote:

> On Sun, Jul 21, 2019 at 01:34:22PM +0200, Tomas Vondra wrote:
> >
> > ...
> >
> >I wonder if we're approaching this wrong. Maybe we should not reverse
> >engineer queries for the various places, but just start with a set of
> >queries that we want to optimize, and then identify which places in the
> >planner need to be modified.
> >
>
> I've decided to do a couple of experiments, trying to make my mind about
> which modified places matter to diffrent queries. But instead of trying
> to reverse engineer the queries, I've taken a different approach - I've
> compiled a list of queries that I think are sensible and relevant, and
> then planned them with incremental sort enabled in different places.
>
> I don't have any clear conclusions at this point - it does show some of
> the places don't change plan for any of the queries, although there may
> be some additional query where it'd make a difference.
>
> But I'm posting this mostly because it might be useful. I've initially
> planned to move changes that add incremental sort paths to separate
> patches, and then apply/skip different subsets of those patches. But
> then I realized there's a better way to do this - I've added a bunch of
> GUCs, one for each such place. This allows doing this testing without
> having to rebuild repeatedly.
>
> I'm not going to post the patch(es) with extra GUCs here, because it'd
> just confuse the patch tester, but it's available here:
>
> https://github.com/tvondra/postgres/tree/incremental-sort-20190730
>
> There are 10 GUCs, one for each place in planner where incremental sort
> paths are constructed. By default all those are set to 'false' so no
> incremental sort paths are built. If you do
>
> SET devel_create_ordered_paths = on;
>
> it'll start creating the paths in non-parallel in create_ordered_paths.
> Then you may enable devel_create_ordered_paths_parallel to also consider
> parallel paths, etc.
>
> The list of queries (synthetic, but hopefully sufficiently realistic)
> and a couple of scripts to collect the plans is in this repository:
>
> https://github.com/tvondra/incremental-sort-tests-2
>
> There's also a spreadsheet with a summary of results, with a visual
> representation of which GUCs affect which queries.
>
> Wow, that sounds like an elaborate experiment. But where is this
spreadsheet you mentioned ?

--
Regards,
Rafia Sabih