Re: Getting ERROR "subplan "SubPlan 1" was not initialized" in EXISTS subplan when using for list partition.

Em qua., 15 de set. de 2021 às 15:35, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> escreveu:

> Ranier Vilela <ranier(dot)vf(at)gmail(dot)com> writes:
> > Em qua., 15 de set. de 2021 às 12:00, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> escreveu:
> >> We could, in fact, not bother with removing the no-longer-referenced
> >> subplans, and it probably wouldn't be all that awful. But the intent
> >> of the original patch was to save the executor startup time for such
> >> subplans, so I wanted to preserve that goal if I could.
>
> > I'm sorry if I'm being persistent with this issue, but I'd like to give
> it
> > one last try before I let it go
> > I modified the way the subplane deletion is done and it seems to me that
> > this really happens.
>
> It looks like what this fragment is doing is clobbering the List
> substructure of the AlternativeSubPlan node itself. That's not
> going to make any difference, since the whole point of the exercise
> is that the AlternativeSubPlan gets cut out of the finished tree.
> But the list that we want to modify, in order to save the
> executor time, is the root->glob->subplans list (which ends
> up being PlannedStmt.subplans). And that's global to the
> query, so we can't fix it correctly on the basis of a single
> AlternativeSubPlan.
>
Ok, I can see now.
But this leads me to the conclusion that AlternativeSubPlan *asplan
does not seem to me to be a good approach for this function, better to deal
with it directly:
"root->glob->subplans" which, it seems, works too.

i = 0;
foreach(lc, root->glob->subplans)
{
SubPlan *curplan = (SubPlan *) lfirst(lc);
Cost curcost;

curcost = curplan->startup_cost + num_exec * curplan->per_call_cost;
if (bestplan == NULL || curcost <= bestcost)
{
bestplan = curplan;
bestcost = curcost;
}
i++;
}

if (bestplan != NULL)
{
foreach(lc, root->glob->subplans)
{
SubPlan *curplan = (SubPlan *) lfirst(lc);
if (curplan != bestplan)
lfirst(lc) = NULL;
}
j = 0;
foreach(lc, root->glob->subplans)
{
SubPlan *curplan = (SubPlan *) lfirst(lc);
if (curplan != NULL)
j++;
}
if (j != i)
{
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("too many subplans: total_plans=%d,
remain_plans=%d", i, j)));
}
}

postgres=# explain (costs off)
postgres-# select * from exists_tbl t1
postgres-# where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2)
or c3 < 0);
ERROR: too many subplans: total_plans=2, remain_plans=1
postgres=# select * from exists_tbl t1
postgres-# where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2)
or c3 < 0);
ERROR: too many subplans: total_plans=2, remain_plans=1

Anyway, thank you for the explanations.

regards,
Ranier Vilela