To make the queries fired by the RI triggers GIN indexed. We need to ‒ as
Tom Lane has previously suggested[1] ‒ to replace the query
SELECT 1 FROM ONLY fktable x WHERE $1 = ANY (fkcol) FOR SHARE OF x;
with
SELECT 1 FROM ONLY fktable x WHERE ARRAY[$1] <@ fkcol FOR SHARE OF x;
but since we have @<(anyarray, anyelement) it can be improved to
SELECT 1 FROM ONLY fktable x WHERE $1 @> fkcol FOR SHARE OF x;
and the piece of code responsible for all of this is ri_GenerateQual in
ri_triggers.c.
How to accomplish that is the next step. I don't know if we should hardcode
the "@>" symbol or if we just index the fk table then ri_GenerateQual would
be able to find the operator on it's own.
*What I plan to do:*
- study how to index the fk table upon its creation. I suspect this can
be done in tablecmds.c
*Questions:*
- how can you programmatically in C index a table?
[1] https://www.postgresql.org/message-id/28389.1351094795%40sss.pgh.pa.us
Best Regards,
Mark Rofail
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 3a25ba52f3..0045f64c9e 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -2650,7 +2650,7 @@ quoteRelationName(char *buffer, Relation rel)
* ri_GenerateQual --- generate a WHERE clause equating two variables
*
* The idea is to append " sep leftop op rightop" to buf, or if fkreftype is
- * FKCONSTR_REF_EACH_ELEMENT, append " sep leftop op ANY(rightop)" to buf.
+ * FKCONSTR_REF_EACH_ELEMENT, append " sep leftop <@ rightop" to buf.
*
* The complexity comes from needing to be sure that the parser will select
* the desired operator. We always name the operator using
@@ -2697,17 +2697,10 @@ ri_GenerateQual(StringInfo buf,
appendStringInfo(buf, " %s %s", sep, leftop);
if (leftoptype != operform->oprleft)
ri_add_cast_to(buf, operform->oprleft);
-
- appendStringInfo(buf, " OPERATOR(%s.%s) ",
- quote_identifier(nspname), oprname);
-
- if (fkreftype == FKCONSTR_REF_EACH_ELEMENT)
- appendStringInfoString(buf, "ANY (");
+ appendStringInfo(buf, " @> ");
appendStringInfoString(buf, rightop);
if (rightoptype != oprright)
ri_add_cast_to(buf, oprright);
- if (fkreftype == FKCONSTR_REF_EACH_ELEMENT)
- appendStringInfoChar(buf, ')');
ReleaseSysCache(opertup);
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
