On Fri, 4 Apr 2025 at 00:34, David Rowley <dgrowle...@gmail.com> wrote: > I've attached 2 patches, which I think addresses most of this, aside > from the last point. > > These do need more work. I've just attached what I have so far before > I head off for the day. I am planning on running some performance > tests tomorrow and doing a round on the comments.
I've done some further work on this, mostly relating to the code
comments. I also removed the now-empty
dispose_eclass_member_iterator() function.
A couple of things which I'm still uncertain of:
1. How to handle the ec_childmembers array in _outEquivalenceClass().
There's no field to know the size of the array. Maybe I should add one
and then print out the non-empty lists.
2. When processing RELOPT_OTHER_JOINREL in add_child_eq_member(), I'm
adding the member to each List for all individual relid mentioned in
child_relids. This will result in the member going on multiple Lists
and cause the iterator to possibly return the member multiple times.
That might matter in a few places, e.g.
generate_join_implied_equalities_normal() keeps some scoring based on
the number of members.
For #2, Yuya's Bitmapset approach didn't suffer from this issue as the
Bitmapsets would be unioned to get the non-duplicative members. I
wondered about doing list_append_unique() instead of lappend() in
generate_join_implied_equalities_normal(). Unsure. The only other
thing I can think of is to do something else with members for
RELOPT_OTHER_JOINREL and store them elsewhere.
I also did some benchmarking using the attached script. I've attached
the results of running that on my AMD Zen2 machine. See the end of the
script for the CREATE TABLE statement for loading that into postgres.
The results look pretty good. v37 came out slightly faster than v36,
either noise or because of dispose_eclass_member_iterator() removal.
-- overall plan time.
select testname,sum(plan_time)::int as plan_ms from bench_results
group by 1 order by 2;
testname | plan_ms
------------------+---------
v37_patch | 6806
v36_patch | 6891
v35_patch | 6917
master_1aff1dc8d | 21113
-- plan time by number of joins for 1024 parts
select testname,joins,sum(plan_time)::int as "plan_ms" from
bench_results where parts=1024 group by 1,2 order by 2,1;
testname | joins | plan_ms
------------------+-------+---------
master_1aff1dc8d | 0 | 239
v35_patch | 0 | 120
v36_patch | 0 | 120
v37_patch | 0 | 119
master_1aff1dc8d | 1 | 485
v35_patch | 1 | 181
v36_patch | 1 | 184
v37_patch | 1 | 180
master_1aff1dc8d | 2 | 832
v35_patch | 2 | 252
v36_patch | 2 | 253
v37_patch | 2 | 249
master_1aff1dc8d | 3 | 1284
v35_patch | 3 | 342
v36_patch | 3 | 338
v37_patch | 3 | 337
master_1aff1dc8d | 4 | 1909
v35_patch | 4 | 427
v36_patch | 4 | 435
v37_patch | 4 | 435
master_1aff1dc8d | 5 | 2830
v35_patch | 5 | 530
v36_patch | 5 | 540
v37_patch | 5 | 535
master_1aff1dc8d | 6 | 4759
v35_patch | 6 | 685
v36_patch | 6 | 691
v37_patch | 6 | 681
-- The memory used is about the same as before:
select testname,joins,sum(mem_alloc)::int as mem_alloc from
bench_results group by 1,2 order by 2,1;
testname | joins | mem_alloc
------------------+-------+-----------
master_1aff1dc8d | 0 | 231110
v35_patch | 0 | 233662
v36_patch | 0 | 233662
v37_patch | 0 | 233662
master_1aff1dc8d | 1 | 432685
v35_patch | 1 | 435369
v36_patch | 1 | 435369
v37_patch | 1 | 435369
master_1aff1dc8d | 2 | 476916
v35_patch | 2 | 476300
v36_patch | 2 | 476300
v37_patch | 2 | 476300
master_1aff1dc8d | 3 | 801834
v35_patch | 3 | 801372
v36_patch | 3 | 801372
v37_patch | 3 | 801372
master_1aff1dc8d | 4 | 917312
v35_patch | 4 | 917015
v36_patch | 4 | 917015
v37_patch | 4 | 917015
master_1aff1dc8d | 5 | 1460833
v35_patch | 5 | 1460701
v36_patch | 5 | 1460701
v37_patch | 5 | 1460701
master_1aff1dc8d | 6 | 2550570
v35_patch | 6 | 2639395
v36_patch | 6 | 2639395
v37_patch | 6 | 2639395
David
#!/bin/bash
seconds=60
dbname=postgres
testname=$1
loops=10
psql -c "drop table if exists lp;" $dbname
psql -c "create table lp (a int not null) partition by list(a);" $dbname
psql -c "alter system set max_parallel_workers_per_gather = 0;" $dbname
psql -c "select pg_reload_conf();" $dbname
rm /tmp/partbench_results.csv
lastpart=0
for parts in 4 8 12 16 20 24 28 32 36 40 44 48 52 56 60 64 128 256 512 1024
do
while (( lastpart <= parts ));
do
psql -c "create table lp$lastpart partition of lp for values
in($lastpart);" $dbname
lastpart=$((lastpart+1))
done
for joins in {0..6}
do
sql=$(psql -t -c "select 'select * from lp ' ||
string_agg('inner join lp lp' || x || ' on lp.a = lp' || x || '.a', ' ') || ';'
from generate_series(0,$joins) x;" $dbname)
echo "Parts=$parts Joins=$joins"
for run in $(seq 0 $loops)
do
# Run the query once to populate relcache and then
again to get the explain output
psql -c "$sql explain (summary on, memory on) $sql"
$dbname | grep -E "(Planning Time|Memory: used)" | tr -d '\n' | awk
-vn=$testname -vp=$parts -vj=$joins -vr=$run -F'[=/ /kB]+' '{ print
n"|"p"|"j"|"r"|"$4"|"$6"|"$9; }' >> /tmp/partbench_results.csv
done
done
done
psql -c "create table if not exists bench_results (testname text not null,
parts int not null, joins int not null, run int not null, mem_used int not
null, mem_alloc int not null, plan_time float not null);" $dbname
psql -c "copy bench_results from '/tmp/partbench_results.csv' delimiter '|'"
$dbname
bench_results_2025-04-04.csv.bz2
Description: application/compressed
v37-0001-Add-the-PlannerInfo-context-to-the-parameter-of-.patch
Description: Binary data
v37-0002-Speed-up-searches-for-child-EquivalenceMembers.patch
Description: Binary data
