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: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: James Coleman <jtc331@gmail.com>, Peter Geoghegan <pg@bowt.ie>, Simon Riggs <simon@2ndquadrant.com>, Rafia Sabih <rafia.pghackers@gmail.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>
Date: 2019-09-13T14:51:53Z
Lists: pgsql-hackers 
On Thu, Sep 12, 2019 at 12:49:29PM -0300, Alvaro Herrera wrote:
>On 2019-Jul-30, Tomas Vondra 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.
>
>[...]
>
>> 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.
>
>OK, so we have that now.  I suppose this spreadsheet now tells us which
>places are useful and which aren't, at least for the queries that you've
>tested.  Dowe that mean that we want to get the patch to consider adding
>paths only the places that your spreadsheet says are useful?  I'm not
>sure what the next steps are for this patch.
>

Yes. I think the spreadsheet call help us with answering two things:

1) places actually affecting the plan (all but three do)

2) redundant places (there are some cases where two GUCs produce the
same plan in the end)

Of course, this does assume the query set makes sense and is somewhat
realistic, but I've tried to construct queries where that is true. We
may extend it over time, of course.

I think we've agreed to add incremental sort paths different places in
separate patches, to make review easier. So this may be a useful way to
decide which places to address first. I'd probably do it in this order:

- create_ordered_paths
- create_ordered_paths (parallel part)
- add_paths_to_grouping_rel
- ... not sure ...

but that's just a proposal. It'd give us most of the benefits, I think,
and we could also focus on the rest of the patch.

Also, regarding the three GUCs that don't affect any of the queries, we
can't really add them as we wouldn't be able to test them. If we manage
to construct a query that'd benefit from them, we can revisit this.


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.