The topic has been previously discussed[0] on the -performance mailing list,
about four years ago.
In that thread, Tom suggested[0] the planner could be made to "expand
"intcol <@
'x,y'::int4range" into "intcol between x and y", using something similar
to the
index LIKE optimization (ie, the "special operator" stuff in indxpath.c)".
This patch tries to do exactly that. It's not tied to any specific datatype,
and has been tested with both builtin types and custom range types. Most
of the
checking for proper datatypes, operators, and btree index happens before
this
code, so I haven't run into any issues yet in my testing. But I'm not
familiar
enough with the internals to be able to confidently say it can handle
all cases
just yet.
[0]:
https://www.postgresql.org/message-id/flat/9860.1364013108%40sss.pgh.pa.us#9860.1364013...@sss.pgh.pa.us
--
#!/usr/bin/env regards
Chhatoi Pritam Baral
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 2952bfb7c2..84dfd8362a 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -30,21 +30,23 @@
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
#include "optimizer/predtest.h"
#include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
#include "optimizer/var.h"
#include "utils/builtins.h"
#include "utils/bytea.h"
#include "utils/lsyscache.h"
#include "utils/pg_locale.h"
+#include "utils/rangetypes.h"
#include "utils/selfuncs.h"
+#include "utils/typcache.h"
#define IsBooleanOpfamily(opfamily) \
((opfamily) == BOOL_BTREE_FAM_OID || (opfamily) == BOOL_HASH_FAM_OID)
#define IndexCollMatchesExprColl(idxcollation, exprcollation) \
((idxcollation) == InvalidOid || (idxcollation) == (exprcollation))
/* Whether we are looking for plain indexscan, bitmap scan, or either */
typedef enum
@@ -180,20 +182,22 @@ static Expr *expand_boolean_index_clause(Node *clause, int indexcol,
IndexOptInfo *index);
static List *expand_indexqual_opclause(RestrictInfo *rinfo,
Oid opfamily, Oid idxcollation);
static RestrictInfo *expand_indexqual_rowcompare(RestrictInfo *rinfo,
IndexOptInfo *index,
int indexcol);
static List *prefix_quals(Node *leftop, Oid opfamily, Oid collation,
Const *prefix, Pattern_Prefix_Status pstatus);
static List *network_prefix_quals(Node *leftop, Oid expr_op, Oid opfamily,
Datum rightop);
+static List *range_elem_contained_quals(Node *leftop, Oid expr_op, Oid opfamily,
+ Datum rightop);
static Datum string_to_datum(const char *str, Oid datatype);
static Const *string_to_const(const char *str, Oid datatype);
/*
* create_index_paths()
* Generate all interesting index paths for the given relation.
* Candidate paths are added to the rel's pathlist (using add_path).
*
* To be considered for an index scan, an index must match one or more
@@ -3286,20 +3290,23 @@ match_special_index_operator(Expr *clause, Oid opfamily, Oid idxcollation,
/* the right-hand const is type text for all of these */
pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC, expr_coll,
&prefix, NULL);
isIndexable = (pstatus != Pattern_Prefix_None);
break;
case OID_INET_SUB_OP:
case OID_INET_SUBEQ_OP:
isIndexable = true;
break;
+ case OID_RANGE_ELEM_CONTAINED_OP:
+ isIndexable = true;
+ break;
}
if (prefix)
{
pfree(DatumGetPointer(prefix->constvalue));
pfree(prefix);
}
/* done if the expression doesn't look indexable */
if (!isIndexable)
@@ -3614,20 +3621,27 @@ expand_indexqual_opclause(RestrictInfo *rinfo, Oid opfamily, Oid idxcollation)
break;
case OID_INET_SUB_OP:
case OID_INET_SUBEQ_OP:
if (!op_in_opfamily(expr_op, opfamily))
{
return network_prefix_quals(leftop, expr_op, opfamily,
patt->constvalue);
}
break;
+ case OID_RANGE_ELEM_CONTAINED_OP:
+ if (!op_in_opfamily(expr_op, opfamily))
+ {
+ return range_elem_contained_quals(leftop, expr_op, opfamily,
+ patt->constvalue);
+ }
+ break;
}
/* Default case: just make a list of the unmodified indexqual */
return list_make1(rinfo);
}
/*
* expand_indexqual_rowcompare --- expand a single indexqual condition
* that is a RowCompareExpr
*
@@ -4096,20 +4110,124 @@ network_prefix_quals(Node *leftop, Oid expr_op, Oid opfamily, Datum rightop)
InvalidOid, /* not collatable */
-1, opr2right,
false, false),
InvalidOid, InvalidOid);
result = lappend(result, make_simple_restrictinfo(expr));
return result;
}
/*
+ * Given an element leftop and a range rightop, and an elem contained-by range
+ * operator, generate suitable indexqual condition(s).
+ */
+static List *
+range_elem_contained_quals(Node *leftop, Datum rightop)
+{
+ Oid datatype;
+ Oid opfamily;
+ Oid opr1oid;
+ Oid opr2oid;
+ List *result = NIL;
+ Expr *expr;
+ RangeType *range;
+ TypeCacheEntry *rangetypcache;
+ RangeBound lbound;
+ RangeBound ubound;
+ bool empty;
+
+ switch (leftop->type)
+ {
+ case T_Var:
+ datatype = ((Var *)leftop)->vartype;
+ break;
+ default:
+ elog(ERROR, "unexpected NodeTag: %u", leftop->type);
+ return NIL;
+ }
+
+ opfamily = lookup_type_cache(datatype, TYPECACHE_BTREE_OPFAMILY)->btree_opf;
+
+ range = DatumGetRangeType(rightop);
+ rangetypcache = lookup_type_cache(RangeTypeGetOid(range), TYPECACHE_RANGE_INFO);
+ range_deserialize(rangetypcache, range, &lbound, &ubound, &empty);
+
+ if (empty)
+ {
+ return NIL;
+ }
+
+ if (!lbound.infinite)
+ {
+ if (lbound.inclusive)
+ {
+ opr1oid = get_opfamily_member(opfamily, datatype, datatype,
+ BTGreaterEqualStrategyNumber);
+ if (opr1oid == InvalidOid)
+ {
+ elog(ERROR, "no >= operator for opfamily %u", opfamily);
+ return NIL;
+ }
+ } else {
+ opr1oid = get_opfamily_member(opfamily, datatype, datatype,
+ BTGreaterStrategyNumber);
+ if (opr1oid == InvalidOid)
+ {
+ elog(ERROR, "no > operator for opfamily %u", opfamily);
+ return NIL;
+ }
+ }
+
+ expr = make_opclause(opr1oid, BOOLOID, false,
+ (Expr *) leftop,
+ (Expr *) makeConst(rangetypcache->rngelemtype->type_id, -1,
+ rangetypcache->rng_collation,
+ rangetypcache->rngelemtype->typlen, lbound.val,
+ false, rangetypcache->rngelemtype->typbyval),
+ InvalidOid, InvalidOid);
+ result = lappend(result, make_simple_restrictinfo(expr));
+ }
+
+ if (!ubound.infinite)
+ {
+ if (ubound.inclusive)
+ {
+ opr2oid = get_opfamily_member(opfamily, datatype, datatype,
+ BTLessEqualStrategyNumber);
+ if (opr2oid == InvalidOid)
+ {
+ elog(ERROR, "no <= operator for opfamily %u", opfamily);
+ return NIL;
+ }
+ } else {
+ opr2oid = get_opfamily_member(opfamily, datatype, datatype,
+ BTLessStrategyNumber);
+ if (opr1oid == InvalidOid)
+ {
+ elog(ERROR, "no < operator for opfamily %u", opfamily);
+ return NIL;
+ }
+ }
+
+ expr = make_opclause(opr2oid, BOOLOID, false,
+ (Expr *) leftop,
+ (Expr *) makeConst(rangetypcache->rngelemtype->type_id, -1,
+ rangetypcache->rng_collation,
+ rangetypcache->rngelemtype->typlen, ubound.val,
+ false, rangetypcache->rngelemtype->typbyval),
+ InvalidOid, InvalidOid);
+ result = lappend(result, make_simple_restrictinfo(expr));
+ }
+ return result;
+}
+
+/*
* Handy subroutines for match_special_index_operator() and friends.
*/
/*
* Generate a Datum of the appropriate type from a C string.
* Note that all of the supported types are pass-by-ref, so the
* returned value should be pfree'd if no longer needed.
*/
static Datum
string_to_datum(const char *str, Oid datatype)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
