Selectivity estimation for intarray
Hackers,
currently built-in &&, @>, <@ array operators have selectivity estimations
while same operator in intarray contrib haven't them. Problem is that
operators in intarray contrib still use contsel and contjoinsel functions
for selectivity estimation even when built-in operators receive their
specific selectivity estimations.
Attached patch adds selectivity estimation functions to &&, @>, <@
operators in intarray contrib. In order to have less code duplication they
are just wrappers over built-in selectivity estimation functions.
However, I faced a problem of migration scripts. Currently, ALTER OPERATOR
can only change owner and schema of operator not operator parameters
themselves. Change pg_operator directly is also not an option. At first, it
would be kludge. At second, in order to correctly find corresponding
operator in pg_operator migration script need to know schema where
extension is installed. But it can't refer @extschema@ because extension is
relocatable.
My proposal is to let ALTER OPERATOR change restrict and join selectivity
functions of the operator. Also it would be useful to be able to change
commutator and negator of operator: extension could add commutators and
negators in further versions. Any thoughts?
------
With best regards,
Alexander Korotkov.
Attachments:
intarray-sel-1.patchapplication/octet-stream; name=intarray-sel-1.patchDownload
diff --git a/contrib/intarray/Makefile b/contrib/intarray/Makefile
new file mode 100644
index 920c5b1..c37aeb9
*** a/contrib/intarray/Makefile
--- b/contrib/intarray/Makefile
***************
*** 2,8 ****
MODULE_big = _int
OBJS = _int_bool.o _int_gist.o _int_op.o _int_tool.o \
! _intbig_gist.o _int_gin.o $(WIN32RES)
EXTENSION = intarray
DATA = intarray--1.0.sql intarray--unpackaged--1.0.sql
--- 2,8 ----
MODULE_big = _int
OBJS = _int_bool.o _int_gist.o _int_op.o _int_tool.o \
! _intbig_gist.o _int_gin.o _int_selfuncs.o $(WIN32RES)
EXTENSION = intarray
DATA = intarray--1.0.sql intarray--unpackaged--1.0.sql
diff --git a/contrib/intarray/_int_selfuncs.c b/contrib/intarray/_int_selfuncs.c
new file mode 100644
index ...495aac8
*** a/contrib/intarray/_int_selfuncs.c
--- b/contrib/intarray/_int_selfuncs.c
***************
*** 0 ****
--- 1,83 ----
+ /*-------------------------------------------------------------------------
+ *
+ * _int_selfuncs.c
+ * Functions for selectivity estimation of intarray operators
+ *
+ * Portions Copyright (c) 1996-2014, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ * contrib/intarray/_int_selfuncs.c
+ *
+ *-------------------------------------------------------------------------
+ */
+ #include "postgres.h"
+
+ #include "access/htup_details.h"
+ #include "catalog/pg_operator.h"
+ #include "utils/selfuncs.h"
+ #include "utils/syscache.h"
+
+ PG_FUNCTION_INFO_V1(_int_contsel);
+ PG_FUNCTION_INFO_V1(_int_contjoinsel);
+
+ Datum _int_contsel(PG_FUNCTION_ARGS);
+ Datum _int_contjoinsel(PG_FUNCTION_ARGS);
+
+ static Oid transformOperator(Oid oprOid);
+
+ static Oid
+ transformOperator(Oid oprOid)
+ {
+ HeapTuple tup;
+ Form_pg_operator op;
+ Oid result = InvalidOid;
+
+ tup = SearchSysCache1(OPEROID, ObjectIdGetDatum(oprOid));
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "Invalid operator: %u", oprOid);
+
+ op = (Form_pg_operator) GETSTRUCT(tup);
+
+ if (!strcmp(op->oprname.data, "&&"))
+ result = OID_ARRAY_OVERLAP_OP;
+ else if (!strcmp(op->oprname.data, "@>"))
+ result = OID_ARRAY_CONTAINS_OP;
+ else if (!strcmp(op->oprname.data, "<@"))
+ result = OID_ARRAY_CONTAINED_OP;
+
+ ReleaseSysCache(tup);
+
+ if (!OidIsValid(result))
+ elog(ERROR, "Invalid operator: %u", oprOid);
+
+ return result;
+ }
+
+ /*
+ * _int_contsel -- restriction selectivity for array @>, &&, <@ operators
+ */
+ Datum
+ _int_contsel(PG_FUNCTION_ARGS)
+ {
+ PG_RETURN_DATUM(DirectFunctionCall4(arraycontsel,
+ PG_GETARG_DATUM(0),
+ ObjectIdGetDatum(transformOperator(PG_GETARG_OID(1))),
+ PG_GETARG_DATUM(2),
+ PG_GETARG_DATUM(3)));
+ }
+
+ /*
+ * _int_contjoinsel -- join selectivity for array @>, &&, <@ operators
+ */
+ Datum
+ _int_contjoinsel(PG_FUNCTION_ARGS)
+ {
+ PG_RETURN_DATUM(DirectFunctionCall5(arraycontjoinsel,
+ PG_GETARG_DATUM(0),
+ ObjectIdGetDatum(transformOperator(PG_GETARG_OID(1))),
+ PG_GETARG_DATUM(2),
+ PG_GETARG_DATUM(3),
+ PG_GETARG_DATUM(4)));
+ }
diff --git a/contrib/intarray/intarray--1.0.sql b/contrib/intarray/intarray--1.0.sql
new file mode 100644
index 0b89e0f..72fb4c2
*** a/contrib/intarray/intarray--1.0.sql
--- b/contrib/intarray/intarray--1.0.sql
*************** RETURNS _int4
*** 117,122 ****
--- 117,132 ----
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT IMMUTABLE;
+ CREATE FUNCTION _int_contsel(internal, oid, internal, integer)
+ RETURNS float8
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT STABLE;
+
+ CREATE FUNCTION _int_contjoinsel(internal, oid, internal, smallint, internal)
+ RETURNS float8
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT STABLE;
+
--
-- OPERATORS
--
*************** CREATE OPERATOR && (
*** 126,133 ****
RIGHTARG = _int4,
PROCEDURE = _int_overlap,
COMMUTATOR = '&&',
! RESTRICT = contsel,
! JOIN = contjoinsel
);
--CREATE OPERATOR = (
--- 136,143 ----
RIGHTARG = _int4,
PROCEDURE = _int_overlap,
COMMUTATOR = '&&',
! RESTRICT = _int_contsel,
! JOIN = _int_contjoinsel
);
--CREATE OPERATOR = (
*************** CREATE OPERATOR @> (
*** 157,164 ****
RIGHTARG = _int4,
PROCEDURE = _int_contains,
COMMUTATOR = '<@',
! RESTRICT = contsel,
! JOIN = contjoinsel
);
CREATE OPERATOR <@ (
--- 167,174 ----
RIGHTARG = _int4,
PROCEDURE = _int_contains,
COMMUTATOR = '<@',
! RESTRICT = _int_contsel,
! JOIN = _int_contjoinsel
);
CREATE OPERATOR <@ (
*************** CREATE OPERATOR <@ (
*** 166,173 ****
RIGHTARG = _int4,
PROCEDURE = _int_contained,
COMMUTATOR = '@>',
! RESTRICT = contsel,
! JOIN = contjoinsel
);
-- obsolete:
--- 176,183 ----
RIGHTARG = _int4,
PROCEDURE = _int_contained,
COMMUTATOR = '@>',
! RESTRICT = _int_contsel,
! JOIN = _int_contjoinsel
);
-- obsolete:
Any chance to have this patch in 9.5 ? Many intarray users will be happy.
On Wed, Apr 29, 2015 at 1:48 PM, Alexander Korotkov <aekorotkov@gmail.com>
wrote:
Show quoted text
Hackers,
currently built-in &&, @>, <@ array operators have selectivity estimations
while same operator in intarray contrib haven't them. Problem is that
operators in intarray contrib still use contsel and contjoinsel functions
for selectivity estimation even when built-in operators receive their
specific selectivity estimations.Attached patch adds selectivity estimation functions to &&, @>, <@
operators in intarray contrib. In order to have less code duplication they
are just wrappers over built-in selectivity estimation functions.However, I faced a problem of migration scripts. Currently, ALTER OPERATOR
can only change owner and schema of operator not operator parameters
themselves. Change pg_operator directly is also not an option. At first, it
would be kludge. At second, in order to correctly find corresponding
operator in pg_operator migration script need to know schema where
extension is installed. But it can't refer @extschema@ because extension
is relocatable.My proposal is to let ALTER OPERATOR change restrict and join selectivity
functions of the operator. Also it would be useful to be able to change
commutator and negator of operator: extension could add commutators and
negators in further versions. Any thoughts?------
With best regards,
Alexander Korotkov.--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Alexander Korotkov <aekorotkov@gmail.com> writes:
My proposal is to let ALTER OPERATOR change restrict and join selectivity
functions of the operator. Also it would be useful to be able to change
commutator and negator of operator: extension could add commutators and
negators in further versions. Any thoughts?
I'm pretty dubious about this, because we lack any mechanism for undoing
parser/planner decisions based on operator properties. And there's quite
a lot of stuff that is based on the assumption that operator properties
will never change.
An example of the pitfalls here is that we can never allow ALTER OPERATOR
RENAME, because for example if you rename '<' to '~<~' that will change
its precedence, and we have no way to fix the parse trees embedded in
stored views to reflect that.
For the specific cases you mention, perhaps it would be all right if we
taught plancache.c to blow away *all* cached plans upon seeing any change
in pg_operator; but that seems like a brute-force solution.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Oleg Bartunov <obartunov@gmail.com> writes:
Any chance to have this patch in 9.5 ? Many intarray users will be happy.
Considering how desperately behind we are on reviewing/committing patches
that were submitted by the deadline, I don't think it would be appropriate
or fair to add on major new patches that came in months late. Please add
this to the first 9.6 commitfest, instead.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Alexander Korotkov <aekorotkov@gmail.com> writes:
My proposal is to let ALTER OPERATOR change restrict and join selectivity
functions of the operator. Also it would be useful to be able to change
commutator and negator of operator: extension could add commutators and
negators in further versions. Any thoughts?I'm pretty dubious about this, because we lack any mechanism for undoing
parser/planner decisions based on operator properties. And there's quite
a lot of stuff that is based on the assumption that operator properties
will never change.An example of the pitfalls here is that we can never allow ALTER OPERATOR
RENAME, because for example if you rename '<' to '~<~' that will change
its precedence, and we have no way to fix the parse trees embedded in
stored views to reflect that.For the specific cases you mention, perhaps it would be all right if we
taught plancache.c to blow away *all* cached plans upon seeing any change
in pg_operator; but that seems like a brute-force solution.
Agreed that it is- but is that really a problem...? I've not run into
many (any?) systems where pg_operator is getting changed often... The
worst part would be adding new operators/extensions, but perhaps we
could exclude that specific case from triggering the cache invalidation?
Thanks!
Stephen
Stephen Frost <sfrost@snowman.net> writes:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
For the specific cases you mention, perhaps it would be all right if we
taught plancache.c to blow away *all* cached plans upon seeing any change
in pg_operator; but that seems like a brute-force solution.
Agreed that it is- but is that really a problem...?
Perhaps it isn't; we certainly have assumptions that pg_amop, for
instance, changes seldom enough that it's not worth tracking individual
changes. The same might be true of pg_operator. I'm not sure though.
The core point I'm trying to make is that making pg_operator entries
mutable is something that's going to require very careful review.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers