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: Sun, 29 Jan 2023 17:35:15 +0530
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAApHDvqh+qOHk4sbvvy=Qr2NjPqAAVYf82oXY0g=Z2hRpC2Vmg@mail.gmail.com>
References: <[email protected]>
<[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]>
<[email protected]>
<CAApHDvqh+qOHk4sbvvy=Qr2NjPqAAVYf82oXY0g=Z2hRpC2Vmg@mail.gmail.com>
> On 26/01/23 07:40, David Rowley wrote:
> I just put together a benchmark script to try to help paint a picture
> of under what circumstances reducing the number of sorts slows down
> performance. work_mem was 4GB for each, so the sorts never went to disk.
I tried same test suit albeit work_mem = 1 MB to check how sort behaves with frequent
disk IO.
For 1 million row test, patch version shows some promise but performance degrades in 10 million row test.
There is also an anomalies in middle range for 100/1000 random value.
I have also added results of benchmark with sorting fix enabled and table with 3 columns
and sorting done on > 2 columns. I don't see much improvement vs master here.
Again, these results are for work_mem = 1 MB.
> Sorting in smaller batches that better fit into CPU cache.
More reading yielded that we are looking for cache-oblivious
sorting algorithm.
One the papers[1] mentions that in quick sort, whenever we reach size which can fit in cache,
instead of partitioning it further, we can do insertion sort there itself.
> Memory-tuned quicksort uses insertion sort to sort small subsets while they are in
> the cache, instead of partitioning further. This increases the instruction count,
> but reduces the cache misses
If this looks step in right direction, I can give it a try and do more reading and experimentation.
[1] http://www.ittc.ku.edu/~jsv/Papers/WAC01.sorting_using_registers.pdf
Thanks,
Ankit
#!/bin/bash
alias /usr/local/pgsql/bin/psql="/usr/local/pgsql/bin//usr/local/pgsql/bin/psql"
alias /usr/local/pgsql/bin/pgbench="/usr/local/pgsql/bin//usr/local/pgsql/bin/pgbench"
/usr/local/pgsql/bin/psql -c "create table if not exists abc (a int, b int, c int);" postgres
for rows in 1000000 10000000
do
for mod in 1 10 100 1000 10000 100000 1000000
do
for selectrows in "select x%$mod,x%$mod,x from generate_Series(1,$rows) x;" "select x / ($rows / $mod),x / ($rows / $mod) ,x from generate_Series(1,$rows) x;" "select x % $mod * random(),x % $mod * random(),x from generate_Series(1,$rows) x;"
do
/usr/local/pgsql/bin/psql -c "truncate table abc;" postgres > /dev/null
/usr/local/pgsql/bin/psql -c "insert into abc $selectrows" postgres > /dev/null
/usr/local/pgsql/bin/psql -c "vacuum freeze abc;" postgres > /dev/null
/usr/local/pgsql/bin/psql -c "select pg_prewarm('abc');" postgres > /dev/null
echo "select a,b,row_number() over (order by a,b) from abc order by a,b,c" > bench.sql
/usr/local/pgsql/bin/psql -c "alter system set enable_sort_pushdown = off;" postgres > /dev/null
/usr/local/pgsql/bin/psql -c "select pg_reload_conf();" postgres > /dev/null
echo -n "$rows $mod Master: "
/usr/local/pgsql/bin/pgbench -n -f bench.sql -T 10 -M prepared postgres | grep latency
/usr/local/pgsql/bin/psql -c "alter system set enable_sort_pushdown = on;" postgres > /dev/null
/usr/local/pgsql/bin/psql -c "select pg_reload_conf();" postgres > /dev/null
echo -n "$rows $mod Patched: "
/usr/local/pgsql/bin/pgbench -n -f bench.sql -T 10 -M prepared postgres | grep latency
done
done
done
Attachments:
[image/png] 10 Million row test.png (44.9K, ../[email protected]/2-10%20Million%20row%20test.png)
download | view image
[image/png] 1 million row test.png (43.4K, ../[email protected]/3-1%20million%20row%20test.png)
download | view image
[image/png] 1 Million row test (with sort optimizations).png (38.9K, ../[email protected]/4-1%20Million%20row%20test%20%28with%20sort%20optimizations%29.png)
download | view image
[text/plain] bench_windowsort_sort_opt.sh.txt (1.6K, ../[email protected]/5-bench_windowsort_sort_opt.sh.txt)
download | inline:
#!/bin/bash
alias /usr/local/pgsql/bin/psql="/usr/local/pgsql/bin//usr/local/pgsql/bin/psql"
alias /usr/local/pgsql/bin/pgbench="/usr/local/pgsql/bin//usr/local/pgsql/bin/pgbench"
/usr/local/pgsql/bin/psql -c "create table if not exists abc (a int, b int, c int);" postgres
for rows in 1000000 10000000
do
for mod in 1 10 100 1000 10000 100000 1000000
do
for selectrows in "select x%$mod,x%$mod,x from generate_Series(1,$rows) x;" "select x / ($rows / $mod),x / ($rows / $mod) ,x from generate_Series(1,$rows) x;" "select x % $mod * random(),x % $mod * random(),x from generate_Series(1,$rows) x;"
do
/usr/local/pgsql/bin/psql -c "truncate table abc;" postgres > /dev/null
/usr/local/pgsql/bin/psql -c "insert into abc $selectrows" postgres > /dev/null
/usr/local/pgsql/bin/psql -c "vacuum freeze abc;" postgres > /dev/null
/usr/local/pgsql/bin/psql -c "select pg_prewarm('abc');" postgres > /dev/null
echo "select a,b,row_number() over (order by a,b) from abc order by a,b,c" > bench.sql
/usr/local/pgsql/bin/psql -c "alter system set enable_sort_pushdown = off;" postgres > /dev/null
/usr/local/pgsql/bin/psql -c "select pg_reload_conf();" postgres > /dev/null
echo -n "$rows $mod Master: "
/usr/local/pgsql/bin/pgbench -n -f bench.sql -T 10 -M prepared postgres | grep latency
/usr/local/pgsql/bin/psql -c "alter system set enable_sort_pushdown = on;" postgres > /dev/null
/usr/local/pgsql/bin/psql -c "select pg_reload_conf();" postgres > /dev/null
echo -n "$rows $mod Patched: "
/usr/local/pgsql/bin/pgbench -n -f bench.sql -T 10 -M prepared postgres | grep latency
done
done
done
[image/png] 10 Million row test (with sort optimizations).png (40.0K, ../[email protected]/6-10%20Million%20row%20test%20%28with%20sort%20optimizations%29.png)
download | view image
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