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: Simon Riggs <simon@2ndquadrant.com>
Cc: James Coleman <jtc331@gmail.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-06-24T20:20:10Z
Lists: pgsql-hackers 
On Mon, Jun 24, 2019 at 07:05:24PM +0100, Simon Riggs wrote:
>On Mon, 24 Jun 2019 at 18:01, James Coleman <jtc331@gmail.com> wrote:
>
>> On Mon, Jun 24, 2019 at 12:56 PM Simon Riggs <simon@2ndquadrant.com>
>> wrote:
>>
>> > What is the specific use case for this? This sounds quite general case.
>>
>> They are both general cases in some sense, but the concerns lie mostly
>> with what happens when they're unexpectedly encountered. For example,
>> if the expected row count or group size is off by a good bit and we
>> effectively have to perform a sort of all (or most) possible rows.
>>
>> If we can get the performance to a point where that misestimated row
>> count or group size doesn't much matter, then ISTM including the patch
>> becomes a much more obvious total win.
>>
>
>I was trying to think of ways of using external information/circumstance to
>knowingly avoid negative use cases. i.e. don't treat sort as a black box,
>use its context.
>

Like what, for example? I'm not saying there's no such additional
information, but I can't think of anything at the moment.

One of the issues is that while the decision whether to use incremental
sort is done during planning, most of the additional (and reliable)
infomation about the data is only available at execution time. And it's
likely not available explicitly - we need to deduce it. Which I think is
exactly what the min group size heuristics is about, no?


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.