From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alexander Lakhin <exclusion@gmail.com>
Cc: Richard Guo <guofenglinux@gmail.com>
Cc: Jian Guo <gjian@vmware.com>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: Hans Buschmann <buschmann@nidsa.net>
Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Cc: Zhenghua Lyu <zlyu@vmware.com>
Subject: Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
Date: Sat, 06 Jan 2024 17:41:14 -0500
Message-ID: <1450797.1704580874@sss.pgh.pa.us> (raw)
In-Reply-To: <608a4886-6c60-0f9e-97d5-591256bd4150@gmail.com>
References: <4504e67078d648cdac3651b2960da6e7@nidsa.net>
<SN6PR05MB5199BB4BB8C982BB519B6660C417A@SN6PR05MB5199.namprd05.prod.outlook.com>
<46a7d950-7ca7-8ab9-76dd-f0bc7611c609@enterprisedb.com>
<SN6PR05MB5199C0095508555B7D8D0D23C41EA@SN6PR05MB5199.namprd05.prod.outlook.com>
<891440.1699483495@sss.pgh.pa.us>
<CAMbWs4-W_81Q3ff1Bk3NU_ChRzOJK0njAoJanW-kXnPF0wjd1Q@mail.gmail.com>
<414317.1700158568@sss.pgh.pa.us>
<CAMbWs48Z_PvveOeankGA69=V=fqogusRKGVPBUGfYC0zDo1WPA@mail.gmail.com>
<482957.1700192299@sss.pgh.pa.us>
<CAMbWs49eVQMgfR-KNALJB15RR5r-zjNtNWnB0j5S=j40gtu1Jg@mail.gmail.com>
<754093.1700250120@sss.pgh.pa.us>
<608a4886-6c60-0f9e-97d5-591256bd4150@gmail.com>
Alexander Lakhin <exclusion@gmail.com> writes:
> Please look at the following query:
> CREATE TABLE t(i int);
> INSERT INTO t VALUES (1);
> VACUUM ANALYZE t;
> WITH ir AS (INSERT INTO t VALUES (2) RETURNING i)
> SELECT * FROM ir WHERE i = 2;
> which produces ERROR: no relation entry for relid 1
> starting from f7816aec2.
Thanks for the report! I guess we need something like the attached.
I'm surprised that this hasn't been noticed before; was the case
really unreachable before?
regards, tom lane
Attachments:fix-examine-variable-for-INSERT-RETURNING.patch (text/x-diff, 4.7K) downloaddiff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.cindex 5a9ce44532..22d01cef5b 100644--- a/src/backend/optimizer/util/relnode.c+++ b/src/backend/optimizer/util/relnode.c@@ -420,6 +420,19 @@ find_base_rel(PlannerInfo *root, int relid)
return NULL; /* keep compiler quiet */
}
+/*+ * find_base_rel_noerr+ * Find a base or otherrel relation entry, returning NULL if there's none+ */+RelOptInfo *+find_base_rel_noerr(PlannerInfo *root, int relid)+{+ /* use an unsigned comparison to prevent negative array element access */+ if ((uint32) relid < (uint32) root->simple_rel_array_size)+ return root->simple_rel_array[relid];+ return NULL;+}+
/*
* find_base_rel_ignore_join
* Find a base or otherrel relation entry, which must already exist.
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.cindex dbcd98d985..cea777e9d4 100644--- a/src/backend/utils/adt/selfuncs.c+++ b/src/backend/utils/adt/selfuncs.c@@ -119,6 +119,7 @@
#include "optimizer/paths.h"
#include "optimizer/plancat.h"
#include "parser/parse_clause.h"
+#include "parser/parse_relation.h"
#include "parser/parsetree.h"
#include "statistics/statistics.h"
#include "storage/bufmgr.h"
@@ -5434,17 +5435,30 @@ examine_simple_variable(PlannerInfo *root, Var *var,
if (HeapTupleIsValid(vardata->statsTuple))
{
- RelOptInfo *onerel = find_base_rel(root, var->varno);+ RelOptInfo *onerel = find_base_rel_noerr(root, var->varno);
Oid userid;
/*
* Check if user has permission to read this column. We require
* all rows to be accessible, so there must be no securityQuals
- * from security barrier views or RLS policies. Use- * onerel->userid if it's set, in case we're accessing the table- * via a view.+ * from security barrier views or RLS policies.+ *+ * Normally the Var will have an associated RelOptInfo from which+ * we can find out which userid to do the check as; but it might+ * not if it's a RETURNING Var for an INSERT target relation. In+ * that case use the RTEPermissionInfo associated with the RTE.
*/
- userid = OidIsValid(onerel->userid) ? onerel->userid : GetUserId();+ if (onerel)+ userid = onerel->userid;+ else+ {+ RTEPermissionInfo *perminfo;++ perminfo = getRTEPermissionInfo(root->parse->rteperminfos, rte);+ userid = perminfo->checkAsUser;+ }+ if (!OidIsValid(userid))+ userid = GetUserId();
vardata->acl_ok =
rte->securityQuals == NIL &&
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.hindex 03a0e46e70..c43d97b48a 100644--- a/src/include/optimizer/pathnode.h+++ b/src/include/optimizer/pathnode.h@@ -307,6 +307,7 @@ extern void expand_planner_arrays(PlannerInfo *root, int add_size);
extern RelOptInfo *build_simple_rel(PlannerInfo *root, int relid,
RelOptInfo *parent);
extern RelOptInfo *find_base_rel(PlannerInfo *root, int relid);
+extern RelOptInfo *find_base_rel_noerr(PlannerInfo *root, int relid);
extern RelOptInfo *find_base_rel_ignore_join(PlannerInfo *root, int relid);
extern RelOptInfo *find_join_rel(PlannerInfo *root, Relids relids);
extern RelOptInfo *build_join_rel(PlannerInfo *root,
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.outindex 69c56ce207..3cf969d938 100644--- a/src/test/regress/expected/with.out+++ b/src/test/regress/expected/with.out@@ -654,6 +654,24 @@ select count(*) from tenk1 a
-> CTE Scan on x
(8 rows)
+explain (costs off)+with x as materialized (insert into tenk1 default values returning unique1)+select count(*) from tenk1 a+ where unique1 in (select * from x);+ QUERY PLAN +------------------------------------------------------------+ Aggregate+ CTE x+ -> Insert on tenk1+ -> Result+ -> Nested Loop+ -> HashAggregate+ Group Key: x.unique1+ -> CTE Scan on x+ -> Index Only Scan using tenk1_unique1 on tenk1 a+ Index Cond: (unique1 = x.unique1)+(10 rows)+
-- SEARCH clause
create temp table graph0( f int, t int, label text );
insert into graph0 values
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sqlindex 3ef9898866..ff68e21e2e 100644--- a/src/test/regress/sql/with.sql+++ b/src/test/regress/sql/with.sql@@ -354,6 +354,11 @@ with x as materialized (select unique1 from tenk1 b)
select count(*) from tenk1 a
where unique1 in (select * from x);
+explain (costs off)+with x as materialized (insert into tenk1 default values returning unique1)+select count(*) from tenk1 a+ where unique1 in (select * from x);+
-- SEARCH clause
create temp table graph0( f int, t int, label text );
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: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, exclusion@gmail.com, guofenglinux@gmail.com, gjian@vmware.com, tomas.vondra@enterprisedb.com, buschmann@nidsa.net, pgsql-hackers@lists.postgresql.org, zlyu@vmware.com
Subject: Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
In-Reply-To: <1450797.1704580874@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox