Re: Reducing memory consumed by RestrictInfo list translations in partitionwise join planning

> On Thu, Oct 10, 2024 at 05:36:10PM GMT, Ashutosh Bapat wrote:
>
> 3. With just patch 0001 applied, planning time usually shows
> degradation (column Q and X in planning time sheets) with or without
> PWJ enabled. I first thought that it might be because of the increased
> size of PlannerInfo. We had seen a similar phenomenon when adding a
> new member to WindowAggState [2]. Hence I introduced patch 0002 which
> moves two fields around to not increase the size of structure. But
> that doesn't fix the regression in the planning time (columns R and
> Y). Apart from increasing the PlannerInfo size and may be object file
> size, 0002 does not have any other impact. But the regression seen
> with just that patch is more than what we saw in [2]. More investigate
> is required to decide whether this regression is real or not and if
> real, the root cause. Looking at the numbers, it seems that this
> regression is causing the planning time regression in rest of the
> patches. If we fix regression by 0001, we should not see much
> regression in rest of the patches. I am looking for some guidance in
> investigating this regression.

Hi,

I've tried to reproduce some subset of those results, in case if I would
be able to notice anything useful. Strangely enough, I wasn't able to
get much boost in planning time e.g. with 4 first patches, 100
partitions and 5 joins -- the results you've posted are showing about
16% in that case, where I'm getting only a couple of percents. Probably
I'm doing something differently, but it's turned out to be hard to
reconstruct (based only on this thread) how did you exactly benchmark
the patch -- could you maybe summarize the benchmark in a reproducible
way?

From what I understand you were testing againt an empty partitioned table. Here
is what I was doing:

create table t1p (c1 int) partition by list(c1);
select format('create table %I partition of t1p for values in (%s)',
't1p' || i, i) from generate_series(1, 100) i; \gexec

do $x$
declare
i record;
plan float[];
plan_line text;
begin
for i in select * from generate_series(1, 1000) i loop
for plan_line in execute format($y$
explain analyze
select * from t1p t1, t1p t2, t1p t3, t1p t4, t1p t5
where t2.c1 =
t1.c1 and t3.c1 = t2.c1 and t4.c1 = t3.c1 and t5.c1 = t4.c1
$y$) loop
if plan_line like '%Planning Time%' then
plan := array_append(plan, substring(plan_line from '\d+.\d+')::float);
end if;
end loop;
end loop;

-- skip the first record as prewarming
raise warning 'avg: %',
(select avg(v) from unnest(plan[2:]) v);
raise warning 'median: %',
(select percentile_cont(0.5) within group(order by v)
from unnest(plan[2:]) v);
end;
$x$;

As a side note, may I ask to attach benchmark results as files, rather than a
link to a google spreadsheet? It feels nice to have something static to work
with.