pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feed From: jacktby@gmail.com <jacktby@gmail.com>
To: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: How to repair my plan modify error?
Date: Sun, 4 Dec 2022 13:32:23 +0800
Message-ID: <202212041332222668251@gmail.com> (raw )
jacktby(at)gmail(dot)com
--------------
Hello, I'm trying to modify pg codes for my personal project. And after I finish modify planner, I get this.
postgres=# create table tt(a int);
CREATE TABLE
postgres=# \d tt
ERROR: btree index keys must be ordered by attribute
here are the patches of my modifies.
Attachments: download all (2 files, .tar.gz)
0001-finish-planner-modify.patch (application/octet-stream, 17.1K) download
From 26e9675671cdf6142928560dee300f24bbe68ba7 Mon Sep 17 00:00:00 2001
From: JackTan25 <jacktby@gmail.com>
Date: Sat, 26 Nov 2022 05:45:00 +0200
Subject: [PATCH] finish planner modify
---
src/backend/executor/nodeIndexscan.c | 292 ++++++++++++++++++++++++--------
src/backend/optimizer/path/costsize.c | 9 +
src/backend/optimizer/path/indxpath.c | 23 ++-
src/backend/optimizer/plan/createplan.c | 6 +-
src/backend/optimizer/plan/planner.c | 15 ++
src/backend/utils/cache/lsyscache.c | 20 +++
src/include/access/skey.h | 2 +
src/include/nodes/pathnodes.h | 3 +
src/include/nodes/primnodes.h | 2 +
src/include/utils/lsyscache.h | 2 +-
10 files changed, 296 insertions(+), 78 deletions(-)
diff --git a/src/backend/executor/nodeIndexscan.c b/src/backend/executor/nodeIndexscan.c
index 5ef5c69..25bb0c3 100644
--- a/src/backend/executor/nodeIndexscan.c
+++ b/src/backend/executor/nodeIndexscan.c
@@ -1207,102 +1207,246 @@ ExecIndexBuildScanKeys(PlanState *planstate, Relation index,
int indnkeyatts;
indnkeyatts = IndexRelationGetNumberOfKeyAttributes(index);
+ // 注意的修改原因是,我们的查询语句是
+ // select * from data where a <-> {....} < 200; Range Query
+ // select * from data order by a <-> {....} limit 5; Knn Query
+ // 对于Range Query 无法执行的原因是 a <-> {....} < 200 会被解析为
+ // OpExpr,然后继续划分他会这样划分(a <-> {....}) < 200,被解析为了
+ // "indexkey op constant",也就是说他会把((a <-> {....})当作一个
+ // indexkey, 然后检查发现不是就报错了,所以要单独处理
if (IsA(clause, OpExpr))
{
- /* indexkey op const or indexkey op expression */
- int flags = 0;
- Datum scanvalue;
+ if (!IsA((Expr*)get_leftop(clause), OpExpr))//����һ��if else���
+ {
+ /* indexkey op const or indexkey op expression */ // TODO: add the indexkey <-> const op const where search pattern
+ int flags = 0;
+ Datum scanvalue;
- opno = ((OpExpr *) clause)->opno;
- opfuncid = ((OpExpr *) clause)->opfuncid;
+ opno = ((OpExpr*)clause)->opno;
+ opfuncid = ((OpExpr*)clause)->opfuncid;
- /*
- * leftop should be the index key Var, possibly relabeled
- */
- leftop = (Expr *) get_leftop(clause);
+ /*
+ * leftop should be the index key Var, possibly relabeled
+ */
+ leftop = (Expr*)get_leftop(clause);
- if (leftop && IsA(leftop, RelabelType))
- leftop = ((RelabelType *) leftop)->arg;
+ if (leftop && IsA(leftop, RelabelType))
+ leftop = ((RelabelType*)leftop)->arg;
- Assert(leftop != NULL);
+ Assert(leftop != NULL);
- if (!(IsA(leftop, Var) &&
- ((Var *) leftop)->varno == INDEX_VAR))
- elog(ERROR, "indexqual doesn't have key on left side");
+ if (!(IsA(leftop, Var) &&
+ ((Var*)leftop)->varno == INDEX_VAR))
+ elog(ERROR, "indexqual doesn't have key on left side");
- varattno = ((Var *) leftop)->varattno;
- if (varattno < 1 || varattno > indnkeyatts)
- elog(ERROR, "bogus index qualification");
+ varattno = ((Var*)leftop)->varattno;
+ if (varattno < 1 || varattno > indnkeyatts)
+ elog(ERROR, "bogus index qualification");
- /*
- * We have to look up the operator's strategy number. This
- * provides a cross-check that the operator does match the index.
- */
- opfamily = index->rd_opfamily[varattno - 1];
+ /*
+ * We have to look up the operator's strategy number. This
+ * provides a cross-check that the operator does match the index.
+ */
+ opfamily = index->rd_opfamily[varattno - 1];
+ const struct TableAmRoutine* tableam = index->rd_tableam;//���˽ṹ��
+
+ get_op_opfamily_properties(opno, opfamily, isorderby,
+ &op_strategy,
+ &op_lefttype,
+ &op_righttype);
- get_op_opfamily_properties(opno, opfamily, isorderby,
- &op_strategy,
- &op_lefttype,
- &op_righttype);
+ if (isorderby)
+ flags |= SK_ORDER_BY;
- if (isorderby)
- flags |= SK_ORDER_BY;
+ /*
+ * rightop is the constant or variable comparison value
+ */
+ rightop = (Expr*)get_rightop(clause);
- /*
- * rightop is the constant or variable comparison value
- */
- rightop = (Expr *) get_rightop(clause);
+ if (rightop && IsA(rightop, RelabelType))
+ rightop = ((RelabelType*)rightop)->arg;
- if (rightop && IsA(rightop, RelabelType))
- rightop = ((RelabelType *) rightop)->arg;
+ Assert(rightop != NULL);
- Assert(rightop != NULL);
+ if (IsA(rightop, Const))
+ {
+ /* OK, simple constant comparison value */
+ scanvalue = ((Const*)rightop)->constvalue;
+ if (((Const*)rightop)->constisnull)
+ flags |= SK_ISNULL;
+ }
+ else
+ {
+ /* Need to treat this one as a runtime key */
+ if (n_runtime_keys >= max_runtime_keys)
+ {
+ if (max_runtime_keys == 0)
+ {
+ max_runtime_keys = 8;
+ runtime_keys = (IndexRuntimeKeyInfo*)
+ palloc(max_runtime_keys * sizeof(IndexRuntimeKeyInfo));
+ }
+ else
+ {
+ max_runtime_keys *= 2;
+ runtime_keys = (IndexRuntimeKeyInfo*)
+ repalloc(runtime_keys, max_runtime_keys * sizeof(IndexRuntimeKeyInfo));
+ }
+ }
+ runtime_keys[n_runtime_keys].scan_key = this_scan_key;
+ runtime_keys[n_runtime_keys].key_expr =
+ ExecInitExpr(rightop, planstate);
+ runtime_keys[n_runtime_keys].key_toastable =
+ TypeIsToastable(op_righttype);
+ n_runtime_keys++;
+ scanvalue = (Datum)0;
+ }
- if (IsA(rightop, Const))
- {
- /* OK, simple constant comparison value */
- scanvalue = ((Const *) rightop)->constvalue;
- if (((Const *) rightop)->constisnull)
- flags |= SK_ISNULL;
+ /*
+ * initialize the scan key's fields appropriately
+ */
+ ScanKeyEntryInitialize(this_scan_key,
+ flags,
+ varattno, /* attribute number to scan */
+ op_strategy, /* op's strategy */
+ op_righttype, /* strategy subtype */
+ ((OpExpr*)clause)->inputcollid, /* collation */
+ opfuncid, /* reg proc to use */
+ scanvalue); /* constant */
+ //=======================================================================================
+ if (((OpExpr*)clause)->KNNValue > 0)
+ {
+ this_scan_key->KNNValues = ((OpExpr*)clause)->KNNValue;
+ }
}
- else
+ else//RangeQuery���������
{
- /* Need to treat this one as a runtime key */
- if (n_runtime_keys >= max_runtime_keys)
+ /* indexkey op const or indexkey op expression */ // TODO: add the indexkey <-> const op const where search pattern
+ int flags = 0;
+ Datum scanvalue;
+ OpExpr* rangeQueryOp;
+ Expr* queryobject;
+ opno = ((OpExpr*)clause)->opno;
+ opfuncid = ((OpExpr*)clause)->opfuncid;
+
+ /*
+ * leftop should be the index key Var, possibly relabeled
+ */
+ rangeQueryOp = (OpExpr*)get_leftop(clause);
+
+ leftop = (Expr*)linitial(rangeQueryOp->args);
+
+ //leftop = (OpExpr*)get_leftop(clause);
+
+ if (leftop && IsA(leftop, RelabelType))
+ leftop = ((RelabelType*)leftop)->arg;
+
+ Assert(leftop != NULL);
+
+ if (!(IsA(leftop, Var) &&
+ ((Var*)leftop)->varno == INDEX_VAR))
+ elog(ERROR, "indexqual doesn't have key on left side");
+
+ varattno = ((Var*)leftop)->varattno;
+ if (varattno < 1 || varattno > indnkeyatts)
+ elog(ERROR, "bogus index qualification");
+
+ /*
+ * We have to look up the operator's strategy number. This
+ * provides a cross-check that the operator does match the index.
+ */
+ opfamily = index->rd_opfamily[varattno - 1];
+ const struct TableAmRoutine* tableam = index->rd_tableam;
+ opno = rangeQueryOp->opno;
+ opfuncid = rangeQueryOp->opfuncid;
+ isorderby = true;
+ get_op_opfamily_properties(opno, opfamily, isorderby,
+ &op_strategy,
+ &op_lefttype,
+ &op_righttype);
+ isorderby = false;
+ if (isorderby)
+ flags |= SK_ORDER_BY;
+
+ /*
+ * rightop is the constant or variable comparison value
+ */
+ rightop = (Expr*)get_rightop(clause);
+
+ if (rightop && IsA(rightop, RelabelType))
+ rightop = ((RelabelType*)rightop)->arg;
+
+ Assert(rightop != NULL);
+
+ if (IsA(rightop, Const))
{
- if (max_runtime_keys == 0)
- {
- max_runtime_keys = 8;
- runtime_keys = (IndexRuntimeKeyInfo *)
- palloc(max_runtime_keys * sizeof(IndexRuntimeKeyInfo));
- }
- else
+ /* OK, simple constant comparison value */
+ scanvalue = ((Const*)rightop)->constvalue;
+ if (((Const*)rightop)->constisnull)
+ flags |= SK_ISNULL;
+ }
+ else
+ {
+ /* Need to treat this one as a runtime key */
+ if (n_runtime_keys >= max_runtime_keys)
{
- max_runtime_keys *= 2;
- runtime_keys = (IndexRuntimeKeyInfo *)
- repalloc(runtime_keys, max_runtime_keys * sizeof(IndexRuntimeKeyInfo));
+ if (max_runtime_keys == 0)
+ {
+ max_runtime_keys = 8;
+ runtime_keys = (IndexRuntimeKeyInfo*)
+ palloc(max_runtime_keys * sizeof(IndexRuntimeKeyInfo));
+ }
+ else
+ {
+ max_runtime_keys *= 2;
+ runtime_keys = (IndexRuntimeKeyInfo*)
+ repalloc(runtime_keys, max_runtime_keys * sizeof(IndexRuntimeKeyInfo));
+ }
}
+ runtime_keys[n_runtime_keys].scan_key = this_scan_key;
+ runtime_keys[n_runtime_keys].key_expr =
+ ExecInitExpr(rightop, planstate);
+ runtime_keys[n_runtime_keys].key_toastable =
+ TypeIsToastable(op_righttype);
+ n_runtime_keys++;
+ scanvalue = (Datum)0;
}
- runtime_keys[n_runtime_keys].scan_key = this_scan_key;
- runtime_keys[n_runtime_keys].key_expr =
- ExecInitExpr(rightop, planstate);
- runtime_keys[n_runtime_keys].key_toastable =
- TypeIsToastable(op_righttype);
- n_runtime_keys++;
- scanvalue = (Datum) 0;
- }
- /*
- * initialize the scan key's fields appropriately
- */
- ScanKeyEntryInitialize(this_scan_key,
- flags,
- varattno, /* attribute number to scan */
- op_strategy, /* op's strategy */
- op_righttype, /* strategy subtype */
- ((OpExpr *) clause)->inputcollid, /* collation */
- opfuncid, /* reg proc to use */
- scanvalue); /* constant */
+ // get the range query object
+ queryobject = (Expr*)lsecond(rangeQueryOp->args);
+
+ if (queryobject && IsA(queryobject, RelabelType))
+ {
+ queryobject = ((RelabelType*)queryobject)->arg;
+ }
+
+ Assert(queryobject != NULL);
+
+ if (IsA(queryobject, Const) && !(((Const*)queryobject)->constisnull))
+ {
+ this_scan_key->query = ((Const*)queryobject)->constvalue;
+ }
+ else {
+ elog(ERROR, "object can't be null when do the range query");
+ }
+ /*
+ * initialize the scan key's fields appropriately
+ */
+ ScanKeyEntryInitialize(this_scan_key,
+ flags,
+ varattno, /* attribute number to scan */
+ op_strategy, /* op's strategy */
+ op_righttype, /* strategy subtype */
+ ((OpExpr*)clause)->inputcollid, /* collation */
+ opfuncid, /* reg proc to use */
+ scanvalue); /* constant */
+
+ if (((OpExpr*)clause)->KNNValue > 0)
+ {
+ this_scan_key->KNNValues = ((OpExpr*)clause)->KNNValue;
+ }
+ }
+ //============================================================================================
}
else if (IsA(clause, RowCompareExpr))
{
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 4c6b1d1..77242d1 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -788,6 +788,15 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count,
path->path.startup_cost = startup_cost;
path->path.total_cost = startup_cost + run_cost;
+
+ //这里李正师兄添加这一块保证在查询的时候强制性的使用索引扫描
+ if (path->indexinfo->relam == 6015 && enable_indexscan && !indexonly)
+ {
+ // 当可匹配spb时,强制使用spb索引
+ path->indextotalcost = 0;
+ path->path.startup_cost = 0;
+ path->path.total_cost = 0;
+ }
}
/*
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index c31fcc9..fbcb625 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -2573,7 +2573,28 @@ match_opclause_to_indexcol(PlannerInfo *root,
indexcol,
index);
}
-
+ if (leftop && IsA(leftop, OpExpr) && strcmp(get_opname(expr_op),"<") == 0) {
+ OpExpr* opert = (OpExpr*)leftop;
+ Node* left = (Node*)linitial(opert->args);
+ Node* right = (Node*)lsecond(opert->args);
+ expr_op = opert->opno;
+ if (left&&match_index_to_operand(left, indexcol, index) &&
+ !bms_is_member(index_relid, rinfo->right_relids) &&
+ !contain_volatile_functions(right))
+ {
+ char* name = get_am_name_me(index->relam);
+ if (strcmp(name,"spb") == 0)
+ {
+ iclause = makeNode(IndexClause);
+ iclause->rinfo = rinfo;
+ iclause->indexquals = list_make1(rinfo);
+ iclause->lossy = false;
+ iclause->indexcol = indexcol;
+ iclause->indexcols = NIL;
+ return iclause;
+ }
+ }
+ }
return NULL;
}
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index ac86ce9..259e1d6 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -5137,8 +5137,10 @@ fix_indexqual_operand(Node *node, IndexOptInfo *index, int indexcol)
result->varno = INDEX_VAR;
result->varattno = indexcol + 1;
return (Node *) result;
- }
- else
+ }else if (IsA(node, OpExpr))
+ {
+ return (Node*)copyObject(node);
+ }else
elog(ERROR, "index key does not match expected index column");
}
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 5d0fd6e..21e430e 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -3408,6 +3408,21 @@ standard_qp_callback(PlannerInfo *root, void *extra)
* much easier, since we know that the parser ensured that one is a
* superset of the other.
*/
+ Node* node = parse->limitCount;
+ int64 count = 0, offset = 0;
+ if (node && IsA(parse->limitCount, Const) && !((Const*)node)->constisnull)
+ {
+ count = DatumGetInt64(((Const*)node)->constvalue);
+ }
+ if (root->sort_pathkeys && count > 0)
+ {
+ ListCell* l;
+ foreach(l, root->sort_pathkeys)
+ {
+ PathKey* key = (PathKey*)lfirst(l);
+ key->limitCount = count;
+ }
+ }
if (root->group_pathkeys)
root->query_pathkeys = root->group_pathkeys;
else if (root->window_pathkeys)
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index a16a63f..994f98d 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -3679,3 +3679,23 @@ get_subscription_name(Oid subid, bool missing_ok)
return subname;
}
+
+/*
+ * get_am_name - given an access method OID name and type, look up its name.
+ */
+char*
+get_am_name_me(Oid amOid)
+{
+ HeapTuple tup;
+ char* result = NULL;
+
+ tup = SearchSysCache1(AMOID, ObjectIdGetDatum(amOid));
+ if (HeapTupleIsValid(tup))
+ {
+ Form_pg_am amform = (Form_pg_am)GETSTRUCT(tup);
+
+ result = pstrdup(NameStr(amform->amname));
+ ReleaseSysCache(tup);
+ }
+ return result;
+}
\ No newline at end of file
diff --git a/src/include/access/skey.h b/src/include/access/skey.h
index b5ab17f..2c3629f 100644
--- a/src/include/access/skey.h
+++ b/src/include/access/skey.h
@@ -70,6 +70,8 @@ typedef struct ScanKeyData
Oid sk_collation; /* collation to use, if needed */
FmgrInfo sk_func; /* lookup info for function to call */
Datum sk_argument; /* data to compare */
+ int KNNValues;
+ Datum query;
} ScanKeyData;
typedef ScanKeyData *ScanKey;
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 6bda383..8256e4b 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1370,6 +1370,9 @@ typedef struct PathKey
Oid pk_opfamily; /* btree opfamily defining the ordering */
int pk_strategy; /* sort direction (ASC or DESC) */
bool pk_nulls_first; /* do NULLs come before normal values? */
+
+ int64 limitCount;
+ int64 limitOffset;
} PathKey;
/*
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 4066133..1437ff5 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -667,6 +667,8 @@ typedef struct OpExpr
/* token location, or -1 if unknown */
int location;
+
+ int KNNValue;
} OpExpr;
/*
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 50f0288..a6f77d3 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -202,7 +202,7 @@ extern Oid get_publication_oid(const char *pubname, bool missing_ok);
extern char *get_publication_name(Oid pubid, bool missing_ok);
extern Oid get_subscription_oid(const char *subname, bool missing_ok);
extern char *get_subscription_name(Oid subid, bool missing_ok);
-
+extern char* get_am_name_me(Oid amOid);
#define type_is_array(typid) (get_element_type(typid) != InvalidOid)
/* type_is_array_domain accepts both plain arrays and domains over arrays */
#define type_is_array_domain(typid) (get_base_element_type(typid) != InvalidOid)
--
1.8.3.1
0003-fix-limit-and-count-bugs.patch (application/octet-stream, 1.4K) download
From 90dbfbb9c7b72f257689baa4502df39d043fa77d Mon Sep 17 00:00:00 2001
From: JackTan25 <jacktby@gmail.com>
Date: Sat, 26 Nov 2022 05:55:44 +0200
Subject: [PATCH 3/3] fix limit and count bugs
---
src/backend/optimizer/path/indxpath.c | 4 ++++
src/include/nodes/pathnodes.h | 4 ++--
2 files changed, 6 insertions(+), 2 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index fbcb625..fb25b20 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -3181,6 +3181,10 @@ match_pathkeys_to_index(IndexOptInfo *index, List *pathkeys,
pathkey->pk_opfamily);
if (expr)
{
+ if (pathkey->limitCount != -1)
+ {
+ ((OpExpr*)expr)->KNNValue = pathkey->limitCount;
+ }
orderby_clauses = lappend(orderby_clauses, expr);
clause_columns = lappend_int(clause_columns, indexcol);
found = true;
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 8256e4b..228aa9b 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1371,8 +1371,8 @@ typedef struct PathKey
int pk_strategy; /* sort direction (ASC or DESC) */
bool pk_nulls_first; /* do NULLs come before normal values? */
- int64 limitCount;
- int64 limitOffset;
+ int limitCount;
+ int limitOffset;
} PathKey;
/*
--
1.8.3.1
Message-ID: <202212041332222668251@gmail.com>
Permalink: ../202212041332222668251@gmail.com/
Also on: postgresql.org/message-id/202212041332222668251@gmail.com
copy link · copy postgr.es · copy git am
reply 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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: jacktby@gmail.com
Subject: Re: How to repair my plan modify error?
In-Reply-To: <202212041332222668251@gmail.com>
References: <202212041332222668251@gmail.com>
* 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