public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ankit Kumar Pandey <[email protected]>
To: David Rowley <[email protected]>
Cc: pghackers <[email protected]>
Cc: Vik Fearing <[email protected]>
Subject: Re: Todo: Teach planner to evaluate multiple windows in the optimal order
Date: Tue, 24 Jan 2023 23:02:18 +0530
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CAApHDvpEYcQ7d-c6Lje26gLi4dXOM3qQ9nrTe1Z241tT8nBiCg@mail.gmail.com>
<[email protected]>
<CAApHDvp8BpUEo_kQdGHWNPCjcmRWCdiy5p26SoQA4R6rinkaLA@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAApHDvp=YnmEQ8Ceu5g4wPL-b5YKmhQ7A3Lya6x=6ga=LLHMFA@mail.gmail.com>
<[email protected]>
<CAApHDvpigtPVJOJ4EmWOAODtPTccz=L_gdPXDVgG2Z=iSPfNmQ@mail.gmail.com>
<[email protected]>
<CAApHDvqVjeHg6UgeCvFOa-uCaEFix9C_3YaT2qxsMtjN4oKbNQ@mail.gmail.com>
<[email protected]>
<CAApHDvra7JeUm+wRpS+XoYXun_9b601s6ujWVG+gOd_AP5cADw@mail.gmail.com>
<[email protected]>
<CAApHDvpAO5H_L84kn9gCJ_hihOavtmDjimKYyftjWtF69BJ=8Q@mail.gmail.com>
<[email protected]>
> 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
view thread (60+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Todo: Teach planner to evaluate multiple windows in the optimal order
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox