Thread: Getting ERROR "subplan "SubPlan 1" was not initialized" in EXISTS subplan when using for list partition.
Getting ERROR "subplan "SubPlan 1" was not initialized" in EXISTS subplan when using for list partition.
From
Rajkumar Raghuwanshi
Date:
Hi,
I am getting "ERROR: subplan "SubPlan 1" was not initialized" error with below test case.
CREATE TABLE tbl ( c1 int, c2 int, c3 int ) PARTITION BY LIST (c1);
create table tbl_null PARTITION OF tbl FOR VALUES IN (null);
create table tbl_def PARTITION OF tbl DEFAULT;
insert into tbl values (8800,0,0);
insert into tbl values (1891,1,1);
insert into tbl values (3420,2,0);
insert into tbl values (9850,3,0);
insert into tbl values (7164,4,4);
analyze tbl;
explain (costs off) select count(*) from tbl t1 where (exists(select 1 from tbl t2 where t2.c1 = t1.c2) or c3 < 0);
postgres=# explain (costs off) select count(*) from tbl t1 where (exists(select 1 from tbl t2 where t2.c1 = t1.c2) or c3 < 0);
ERROR: subplan "SubPlan 1" was not initialized
Thanks & Regards,
I am getting "ERROR: subplan "SubPlan 1" was not initialized" error with below test case.
CREATE TABLE tbl ( c1 int, c2 int, c3 int ) PARTITION BY LIST (c1);
create table tbl_null PARTITION OF tbl FOR VALUES IN (null);
create table tbl_def PARTITION OF tbl DEFAULT;
insert into tbl values (8800,0,0);
insert into tbl values (1891,1,1);
insert into tbl values (3420,2,0);
insert into tbl values (9850,3,0);
insert into tbl values (7164,4,4);
analyze tbl;
explain (costs off) select count(*) from tbl t1 where (exists(select 1 from tbl t2 where t2.c1 = t1.c2) or c3 < 0);
postgres=# explain (costs off) select count(*) from tbl t1 where (exists(select 1 from tbl t2 where t2.c1 = t1.c2) or c3 < 0);
ERROR: subplan "SubPlan 1" was not initialized
Thanks & Regards,
Rajkumar Raghuwanshi
Re: Getting ERROR "subplan "SubPlan 1" was not initialized" in EXISTS subplan when using for list partition.
From
Ranier Vilela
Date:
Em ter., 14 de set. de 2021 às 08:49, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com> escreveu:
Hi,
I am getting "ERROR: subplan "SubPlan 1" was not initialized" error with below test case.
CREATE TABLE tbl ( c1 int, c2 int, c3 int ) PARTITION BY LIST (c1);
create table tbl_null PARTITION OF tbl FOR VALUES IN (null);
create table tbl_def PARTITION OF tbl DEFAULT;
insert into tbl values (8800,0,0);
insert into tbl values (1891,1,1);
insert into tbl values (3420,2,0);
insert into tbl values (9850,3,0);
insert into tbl values (7164,4,4);
analyze tbl;
explain (costs off) select count(*) from tbl t1 where (exists(select 1 from tbl t2 where t2.c1 = t1.c2) or c3 < 0);
postgres=# explain (costs off) select count(*) from tbl t1 where (exists(select 1 from tbl t2 where t2.c1 = t1.c2) or c3 < 0);
ERROR: subplan "SubPlan 1" was not initialized
Not sure if that helps, but below backtrace at Windows 64.
01 postgres!ExecInitExprRec(struct Expr * node = 0x00000000`021b4ed8, struct ExprState * state = 0x00000000`021a0ba0, unsigned int64 * resv = 0x00000000`021a0ba8, bool * resnull = 0x00000000`021a0ba5)+0x1447 [C:\dll\postgres\postgres_head\src\backend\executor\execExpr.c @ 1424]
02 postgres!ExecInitExprRec(struct Expr * node = 0x00000000`021b4ea8, struct ExprState * state = 0x00000000`021a0ba0, unsigned int64 * resv = 0x00000000`021a0ba8, bool * resnull = 0x00000000`021a0ba5)+0x1176 [C:\dll\postgres\postgres_head\src\backend\executor\execExpr.c @ 1364]
03 postgres!ExecInitQual(struct List * qual = 0x00000000`021b5198, struct PlanState * parent = 0x00000000`0219ff90)+0x197 [C:\dll\postgres\postgres_head\src\backend\executor\execExpr.c @ 256]
04 postgres!ExecInitSeqScan(struct SeqScan * node = 0x00000000`021b3dd8, struct EState * estate = 0x00000000`0219f2c8, int eflags = 0n17)+0x105 [C:\dll\postgres\postgres_head\src\backend\executor\nodeSeqscan.c @ 171]
05 postgres!ExecInitNode(struct Plan * node = 0x00000000`021b3dd8, struct EState * estate = 0x00000000`0219f2c8, int eflags = 0n17)+0x1bb [C:\dll\postgres\postgres_head\src\backend\executor\execProcnode.c @ 209]
06 postgres!ExecInitAppend(struct Append * node = 0x00000000`021b3c78, struct EState * estate = 0x00000000`0219f2c8, int eflags = 0n17)+0x301 [C:\dll\postgres\postgres_head\src\backend\executor\nodeAppend.c @ 232]
07 postgres!ExecInitNode(struct Plan * node = 0x00000000`021b3c78, struct EState * estate = 0x00000000`0219f2c8, int eflags = 0n17)+0xf8 [C:\dll\postgres\postgres_head\src\backend\executor\execProcnode.c @ 181]
08 postgres!ExecInitAgg(struct Agg * node = 0x00000000`021b4688, struct EState * estate = 0x00000000`0219f2c8, int eflags = 0n17)+0x559 [C:\dll\postgres\postgres_head\src\backend\executor\nodeAgg.c @ 3383]
09 postgres!ExecInitNode(struct Plan * node = 0x00000000`021b4688, struct EState * estate = 0x00000000`0219f2c8, int eflags = 0n17)+0x58a [C:\dll\postgres\postgres_head\src\backend\executor\execProcnode.c @ 340]
0a postgres!InitPlan(struct QueryDesc * queryDesc = 0x00000000`021b5e48, int eflags = 0n17)+0x490 [C:\dll\postgres\postgres_head\src\backend\executor\execMain.c @ 936]
0b postgres!standard_ExecutorStart(struct QueryDesc * queryDesc = 0x00000000`021b5e48, int eflags = 0n17)+0x242 [C:\dll\postgres\postgres_head\src\backend\executor\execMain.c @ 265]
0c postgres!ExecutorStart(struct QueryDesc * queryDesc = 0x00000000`021b5e48, int eflags = 0n1)+0x4a [C:\dll\postgres\postgres_head\src\backend\executor\execMain.c @ 144]
0d postgres!ExplainOnePlan(struct PlannedStmt * plannedstmt = 0x00000000`021b5db8, struct IntoClause * into = 0x00000000`00000000, struct ExplainState * es = 0x00000000`021831f8, char * queryString = 0x00000000`00999348 "explain (costs off) select count(*) from tbl t1 where (exists(select 1 from tbl t2 where t2.c1 = t1.c2) or c3 < 0);", struct ParamListInfoData * params = 0x00000000`00000000, struct QueryEnvironment * queryEnv = 0x00000000`00000000, union _LARGE_INTEGER * planduration = 0x00000000`007ff160 {5127}, struct BufferUsage * bufusage = 0x00000000`00000000)+0x197 [C:\dll\postgres\postgres_head\src\backend\commands\explain.c @ 582]
0e postgres!ExplainOneQuery(struct Query * query = 0x00000000`0099a5d0, int cursorOptions = 0n2048, struct IntoClause * into = 0x00000000`00000000, struct ExplainState * es = 0x00000000`021831f8, char * queryString = 0x00000000`00999348 "explain (costs off) select count(*) from tbl t1 where (exists(select 1 from tbl t2 where t2.c1 = t1.c2) or c3 < 0);", struct ParamListInfoData * params = 0x00000000`00000000, struct QueryEnvironment * queryEnv = 0x00000000`00000000)+0x210 [C:\dll\postgres\postgres_head\src\backend\commands\explain.c @ 413]
0f postgres!ExplainQuery(struct ParseState * pstate = 0x00000000`0099de20, struct ExplainStmt * stmt = 0x00000000`0099a410, struct ParamListInfoData * params = 0x00000000`00000000, struct _DestReceiver * dest = 0x00000000`0099dd90)+0x72f [C:\dll\postgres\postgres_head\src\backend\commands\explain.c @ 286]
10 postgres!standard_ProcessUtility(struct PlannedStmt * pstmt = 0x00000000`02197808, char * queryString = 0x00000000`00999348 "explain (costs off) select count(*) from tbl t1 where (exists(select 1 from tbl t2 where t2.c1 = t1.c2) or c3 < 0);", bool readOnlyTree = false, ProcessUtilityContext context = PROCESS_UTILITY_TOPLEVEL (0n0), struct ParamListInfoData * params = 0x00000000`00000000, struct QueryEnvironment * queryEnv = 0x00000000`00000000, struct _DestReceiver * dest = 0x00000000`0099dd90, struct QueryCompletion * qc = 0x00000000`007ff630)+0x8f1 [C:\dll\postgres\postgres_head\src\backend\tcop\utility.c @ 846]
11 postgres!ProcessUtility(struct PlannedStmt * pstmt = 0x00000000`02197808, char * queryString = 0x00000000`00999348 "explain (costs off) select count(*) from tbl t1 where (exists(select 1 from tbl t2 where t2.c1 = t1.c2) or c3 < 0);", bool readOnlyTree = false, ProcessUtilityContext context = PROCESS_UTILITY_TOPLEVEL (0n0), struct ParamListInfoData * params = 0x00000000`00000000, struct QueryEnvironment * queryEnv = 0x00000000`00000000, struct _DestReceiver * dest = 0x00000000`0099dd90, struct QueryCompletion * qc = 0x00000000`007ff630)+0xb5 [C:\dll\postgres\postgres_head\src\backend\tcop\utility.c @ 530]
12 postgres!PortalRunUtility(struct PortalData * portal = 0x00000000`0095cd38, struct PlannedStmt * pstmt = 0x00000000`02197808, bool isTopLevel = true, bool setHoldSnapshot = true, struct _DestReceiver * dest = 0x00000000`0099dd90, struct QueryCompletion * qc = 0x00000000`007ff630)+0x135 [C:\dll\postgres\postgres_head\src\backend\tcop\pquery.c @ 1157]
13 postgres!FillPortalStore(struct PortalData * portal = 0x00000000`0095cd38, bool isTopLevel = true)+0x105 [C:\dll\postgres\postgres_head\src\backend\tcop\pquery.c @ 1028]
regards,
Ranier Vilela
Re: Getting ERROR "subplan "SubPlan 1" was not initialized" in EXISTS subplan when using for list partition.
From
Tom Lane
Date:
Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com> writes: > I am getting "ERROR: subplan "SubPlan 1" was not initialized" error with > below test case. > CREATE TABLE tbl ( c1 int, c2 int, c3 int ) PARTITION BY LIST (c1); > create table tbl_null PARTITION OF tbl FOR VALUES IN (null); > create table tbl_def PARTITION OF tbl DEFAULT; > insert into tbl values (8800,0,0); > insert into tbl values (1891,1,1); > insert into tbl values (3420,2,0); > insert into tbl values (9850,3,0); > insert into tbl values (7164,4,4); > analyze tbl; > explain (costs off) select count(*) from tbl t1 where (exists(select 1 from > tbl t2 where t2.c1 = t1.c2) or c3 < 0); > postgres=# explain (costs off) select count(*) from tbl t1 where > (exists(select 1 from tbl t2 where t2.c1 = t1.c2) or c3 < 0); > ERROR: subplan "SubPlan 1" was not initialized Nice example. This is failing since 41efb8340. It happens because we copy the AlternativeSubPlan for the EXISTS into the scan clauses for each of t1's partitions. At setrefs.c time, when fix_alternative_subplan() looks at the first of these AlternativeSubPlans, it decides it likes the first subplan better, so it deletes SubPlan 2 from the root->glob->subplans list. But when it comes to the next copy (which is attached to a partition with a different number of rows), it likes the second subplan better, so it deletes SubPlan 1 from the root->glob->subplans list. Now we have SubPlan nodes in the tree with no referents in the global list of subplans, so kaboom. The easiest fix would just be to not try to delete unreferenced subplans. The error goes away if I remove the "lfirst(lc2) = NULL" statements from fix_alternative_subplan(). However, this is a bit annoying since then we will still pay the cost of initializing subplans that (in most cases) will never be used. I'm going to look into how painful it is to have setrefs.c remove unused subplans only at the end, after it's seen all the AlternativeSubPlans. regards, tom lane
Re: Getting ERROR "subplan "SubPlan 1" was not initialized" in EXISTS subplan when using for list partition.
From
Zhihong Yu
Date:
On Tue, Sep 14, 2021 at 10:44 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com> writes:
> I am getting "ERROR: subplan "SubPlan 1" was not initialized" error with
> below test case.
> CREATE TABLE tbl ( c1 int, c2 int, c3 int ) PARTITION BY LIST (c1);
> create table tbl_null PARTITION OF tbl FOR VALUES IN (null);
> create table tbl_def PARTITION OF tbl DEFAULT;
> insert into tbl values (8800,0,0);
> insert into tbl values (1891,1,1);
> insert into tbl values (3420,2,0);
> insert into tbl values (9850,3,0);
> insert into tbl values (7164,4,4);
> analyze tbl;
> explain (costs off) select count(*) from tbl t1 where (exists(select 1 from
> tbl t2 where t2.c1 = t1.c2) or c3 < 0);
> postgres=# explain (costs off) select count(*) from tbl t1 where
> (exists(select 1 from tbl t2 where t2.c1 = t1.c2) or c3 < 0);
> ERROR: subplan "SubPlan 1" was not initialized
Nice example. This is failing since 41efb8340. It happens because
we copy the AlternativeSubPlan for the EXISTS into the scan clauses
for each of t1's partitions. At setrefs.c time, when
fix_alternative_subplan() looks at the first of these
AlternativeSubPlans, it decides it likes the first subplan better,
so it deletes SubPlan 2 from the root->glob->subplans list. But when
it comes to the next copy (which is attached to a partition with a
different number of rows), it likes the second subplan better, so it
deletes SubPlan 1 from the root->glob->subplans list. Now we have
SubPlan nodes in the tree with no referents in the global list of
subplans, so kaboom.
The easiest fix would just be to not try to delete unreferenced
subplans. The error goes away if I remove the "lfirst(lc2) = NULL"
statements from fix_alternative_subplan(). However, this is a bit
annoying since then we will still pay the cost of initializing
subplans that (in most cases) will never be used. I'm going to
look into how painful it is to have setrefs.c remove unused subplans
only at the end, after it's seen all the AlternativeSubPlans.
regards, tom lane
Hi,
In the fix, isUsedSubplan is used to tell whether any given subplan is used.
Since only one subplan is used, I wonder if the array can be replaced by specifying the subplan is used.
Cheers
Re: Getting ERROR "subplan "SubPlan 1" was not initialized" in EXISTS subplan when using for list partition.
From
Tom Lane
Date:
Zhihong Yu <zyu@yugabyte.com> writes: > In the fix, isUsedSubplan is used to tell whether any given subplan is used. > Since only one subplan is used, I wonder if the array can be replaced by > specifying the subplan is used. That doesn't seem particularly more convenient. The point of the bool array is to merge the results from examination of (possibly) many AlternativeSubPlans. regards, tom lane
Re: Getting ERROR "subplan "SubPlan 1" was not initialized" in EXISTS subplan when using for list partition.
From
Ranier Vilela
Date:
Em ter., 14 de set. de 2021 às 17:11, Tom Lane <tgl@sss.pgh.pa.us> escreveu:
Zhihong Yu <zyu@yugabyte.com> writes:
> In the fix, isUsedSubplan is used to tell whether any given subplan is used.
> Since only one subplan is used, I wonder if the array can be replaced by
> specifying the subplan is used.
That doesn't seem particularly more convenient. The point of the bool
array is to merge the results from examination of (possibly) many
AlternativeSubPlans.
Impressive quick fix, but IMHO I also think it's a bit excessive.
I would like to ask if this alternative fix (attached) would also solve the problem or not.
Apparently, it passes the proposed test and in regress.
Apparently, it passes the proposed test and in regress.
postgres=# create temp table exists_tbl (c1 int, c2 int, c3 int) partition by list (c1);
CREATE TABLE
postgres=# create temp table exists_tbl_null partition of exists_tbl for values in (null);
CREATE TABLE
postgres=# create temp table exists_tbl_def partition of exists_tbl default;
CREATE TABLE
postgres=# insert into exists_tbl select x, x/2, x+1 from generate_series(0,10) x;
INSERT 0 11
postgres=# analyze exists_tbl;
ANALYZE
postgres=# explain (costs off)
postgres-# explain (costs off);
ERROR: syntax error at or near "explain"
LINE 2: explain (costs off);
^
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);
QUERY PLAN
------------------------------------------------------
Append
-> Seq Scan on exists_tbl_null t1_1
Filter: ((SubPlan 1) OR (c3 < 0))
SubPlan 1
-> Append
-> Seq Scan on exists_tbl_null t2_1
Filter: (t1_1.c1 = c2)
-> Seq Scan on exists_tbl_def t2_2
Filter: (t1_1.c1 = c2)
-> Seq Scan on exists_tbl_def t1_2
Filter: ((hashed SubPlan 2) OR (c3 < 0))
SubPlan 2
-> Append
-> Seq Scan on exists_tbl_null t2_4
-> Seq Scan on exists_tbl_def t2_5
(15 rows)
postgres=# select * from exists_tbl t1
postgres-# where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0);
c1 | c2 | c3
----+----+----
0 | 0 | 1
1 | 0 | 2
2 | 1 | 3
3 | 1 | 4
4 | 2 | 5
5 | 2 | 6
(6 rows)
CREATE TABLE
postgres=# create temp table exists_tbl_null partition of exists_tbl for values in (null);
CREATE TABLE
postgres=# create temp table exists_tbl_def partition of exists_tbl default;
CREATE TABLE
postgres=# insert into exists_tbl select x, x/2, x+1 from generate_series(0,10) x;
INSERT 0 11
postgres=# analyze exists_tbl;
ANALYZE
postgres=# explain (costs off)
postgres-# explain (costs off);
ERROR: syntax error at or near "explain"
LINE 2: explain (costs off);
^
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);
QUERY PLAN
------------------------------------------------------
Append
-> Seq Scan on exists_tbl_null t1_1
Filter: ((SubPlan 1) OR (c3 < 0))
SubPlan 1
-> Append
-> Seq Scan on exists_tbl_null t2_1
Filter: (t1_1.c1 = c2)
-> Seq Scan on exists_tbl_def t2_2
Filter: (t1_1.c1 = c2)
-> Seq Scan on exists_tbl_def t1_2
Filter: ((hashed SubPlan 2) OR (c3 < 0))
SubPlan 2
-> Append
-> Seq Scan on exists_tbl_null t2_4
-> Seq Scan on exists_tbl_def t2_5
(15 rows)
postgres=# select * from exists_tbl t1
postgres-# where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0);
c1 | c2 | c3
----+----+----
0 | 0 | 1
1 | 0 | 2
2 | 1 | 3
3 | 1 | 4
4 | 2 | 5
5 | 2 | 6
(6 rows)
regards,
Ranier Vilela
Attachment
Re: Getting ERROR "subplan "SubPlan 1" was not initialized" in EXISTS subplan when using for list partition.
From
Tom Lane
Date:
Ranier Vilela <ranier.vf@gmail.com> writes: > I would like to ask if this alternative fix (attached) would also solve the > problem or not. If I'm reading the patch correctly, that fixes it by failing to drop unused subplans at all --- the second loop you have has no external effect. 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. The committed patch seems small enough and cheap enough to be worthwhile. regards, tom lane
Re: Getting ERROR "subplan "SubPlan 1" was not initialized" in EXISTS subplan when using for list partition.
From
Ranier Vilela
Date:
Em qua., 15 de set. de 2021 às 12:00, Tom Lane <tgl@sss.pgh.pa.us> escreveu:
Ranier Vilela <ranier.vf@gmail.com> writes:
> I would like to ask if this alternative fix (attached) would also solve the
> problem or not.
If I'm reading the patch correctly, that fixes it by failing to drop
unused subplans at all --- the second loop you have has no external
effect.
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. The committed
patch seems small enough and cheap enough to be worthwhile.
Understood, thanks for replying.
regards,
Ranier Vilela
Re: Getting ERROR "subplan "SubPlan 1" was not initialized" in EXISTS subplan when using for list partition.
From
Ranier Vilela
Date:
Em qua., 15 de set. de 2021 às 12:00, Tom Lane <tgl@sss.pgh.pa.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.
I modified the way the subplane deletion is done and it seems to me that this really happens.
I ran a quick dirty test to count the remaining subplanes.
i = 0;
foreach(lc, asplan->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, asplan->subplans)
{
SubPlan *curplan = (SubPlan *) lfirst(lc);
if (curplan != bestplan)
lfirst(lc) = NULL;
}
j = 0;
foreach(lc, asplan->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)));
}
}
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
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
I think that works:
lfirst(lc) = NULL;
regards,
Ranier Vilela
Re: Getting ERROR "subplan "SubPlan 1" was not initialized" in EXISTS subplan when using for list partition.
From
Tom Lane
Date:
Ranier Vilela <ranier.vf@gmail.com> writes: > Em qua., 15 de set. de 2021 às 12:00, Tom Lane <tgl@sss.pgh.pa.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. regards, tom lane
Re: Getting ERROR "subplan "SubPlan 1" was not initialized" in EXISTS subplan when using for list partition.
From
Ranier Vilela
Date:
Em qua., 15 de set. de 2021 às 15:35, Tom Lane <tgl@sss.pgh.pa.us> escreveu:
Ranier Vilela <ranier.vf@gmail.com> writes:
> Em qua., 15 de set. de 2021 às 12:00, Tom Lane <tgl@sss.pgh.pa.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.
"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)));
}
}
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
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
Re: Getting ERROR "subplan "SubPlan 1" was not initialized" in EXISTS subplan when using for list partition.
From
Ranier Vilela
Date:
Em qua., 15 de set. de 2021 às 16:16, Ranier Vilela <ranier.vf@gmail.com> escreveu:
Em qua., 15 de set. de 2021 às 15:35, Tom Lane <tgl@sss.pgh.pa.us> escreveu:Ranier Vilela <ranier.vf@gmail.com> writes:
> Em qua., 15 de set. de 2021 às 12:00, Tom Lane <tgl@sss.pgh.pa.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 *asplandoes 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.
Hmm, too fast and wrong, do not work.
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: unrecognized node type: 13
postgres=# select * from exists_tbl t1
postgres-# where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0);
ERROR: unrecognized node type: 13
postgres-# select * from exists_tbl t1
postgres-# where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0);
ERROR: unrecognized node type: 13
postgres=# select * from exists_tbl t1
postgres-# where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0);
ERROR: unrecognized node type: 13
regards,
Ranier Vilela