Message-ID: <5314d2fc-76b6-c4ea-c083-1ef11411e5be@gmail.com>
Date: Tue, 24 Jan 2023 23:02:18 +0530
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: Todo: Teach planner to evaluate multiple windows in the optimal
 order
From: Ankit Kumar Pandey <itsankitkp@gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: pghackers <pgsql-hackers@lists.postgresql.org>,
 Vik Fearing <vik@postgresfriends.org>
References: <83d80853-a45c-d85c-68eb-59acfe7fb5fb@gmail.com>
 <01248493-182b-0f92-f2f2-ff28dc4b2e83@gmail.com>
 <CAApHDvpEYcQ7d-c6Lje26gLi4dXOM3qQ9nrTe1Z241tT8nBiCg@mail.gmail.com>
 <c1d1aace-ce9f-9ec3-1984-5319920b1594@gmail.com>
 <CAApHDvp8BpUEo_kQdGHWNPCjcmRWCdiy5p26SoQA4R6rinkaLA@mail.gmail.com>
 <2040c902-32b1-46c8-0b26-d3f43a16b9bb@gmail.com>
 <12f6ef19-e06e-8ea9-ad98-3fd92afb7f0b@gmail.com>
 <CAApHDvp=YnmEQ8Ceu5g4wPL-b5YKmhQ7A3Lya6x=6ga=LLHMFA@mail.gmail.com>
 <8d0b24ce-f8ba-cd63-82ae-be853b4067e8@gmail.com>
 <CAApHDvpigtPVJOJ4EmWOAODtPTccz=L_gdPXDVgG2Z=iSPfNmQ@mail.gmail.com>
 <b1df021b-e6c0-f26a-1d5a-bd07f765db62@gmail.com>
 <CAApHDvqVjeHg6UgeCvFOa-uCaEFix9C_3YaT2qxsMtjN4oKbNQ@mail.gmail.com>
 <4ef7f947-526b-c4ec-f7a0-8b4586e693e4@gmail.com>
 <CAApHDvra7JeUm+wRpS+XoYXun_9b601s6ujWVG+gOd_AP5cADw@mail.gmail.com>
 <8599211e-1ea9-8827-4b9b-adeb865656bd@gmail.com>
 <CAApHDvpAO5H_L84kn9gCJ_hihOavtmDjimKYyftjWtF69BJ=8Q@mail.gmail.com>
 <a1c1f93a-16f7-4f8a-4562-4b89dd2e371c@gmail.com>
Content-Language: en-US
In-Reply-To: <a1c1f93a-16f7-4f8a-4562-4b89dd2e371c@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/5314d2fc-76b6-c4ea-c083-1ef11411e5be%40gmail.com>
Precedence: bulk


> I think more benchmarking is required
> so we can figure out if this is a corner case or a common case

I did some more benchmarks:

#1. AIM: Pushdown column whose size is very high

create table test(a int, b int, c text);
insert into test select a,b,c from generate_series(1,1000)a, generate_series(1,1000)b, repeat(md5(random()::text), 999)c;

explain (analyze, costs off) select count(*) over (order by a), row_number() over (order by a, b) from test order by a,b,c;
                                                 QUERY PLAN
----------------------------------------------------------------------------------------------------------
  Incremental Sort (actual time=1161.605..6577.141 rows=1000000 loops=1)
    Sort Key: a, b, c
    Presorted Key: a, b
    Full-sort Groups: 31250  Sort Method: quicksort  Average Memory: 39kB  Peak Memory: 39kB
    ->  WindowAgg (actual time=1158.896..5819.460 rows=1000000 loops=1)
          ->  WindowAgg (actual time=1154.614..3391.537 rows=1000000 loops=1)
                ->  Gather Merge (actual time=1154.602..2404.125 rows=1000000 loops=1)
                      Workers Planned: 2
                      Workers Launched: 2
                      ->  Sort (actual time=1118.326..1295.743 rows=333333 loops=3)
                            Sort Key: a, b
                            Sort Method: external merge  Disk: 145648kB
                            Worker 0:  Sort Method: external merge  Disk: 140608kB
                            Worker 1:  Sort Method: external merge  Disk: 132792kB
                            ->  Parallel Seq Scan on test (actual time=0.018..169.319 rows=333333 loops=3)
  Planning Time: 0.091 ms
  Execution Time: 6816.616 ms
(17 rows)

Planner choose faster path correctly (which was not path which had pushed down column).

#2. AIM: Check strict vs incremental sorts wrt to large size data
Patch version is faster as for external merge sort, disk IO is main bottleneck and if we sort an extra column,
it doesn't have major impact. This is when work mem is very small.

For larger work_mem, difference between patched version and master is minimal and
they both provide somewhat comparable performance.

Tried permutation of few cases which we have already covered but I did not see anything alarming in those.


> I'm just unsure if we should write this off as the expected behaviour

> of Sort and continue with the patch or delay the whole thing until we

> make some improvements to sort.  

I am not seeing other cases where patch version is consistently slower.


Thanks,
Ankit