[PATCH] inet << indexability (take 3)
Started by Alex Pilosovover 24 years ago2 messages
I think this addresses all Tom's concerns. Tom? :)
--
Alex Pilosov | http://www.acecape.com/dsl
CTO - Acecape, Inc. | AceDSL:The best ADSL in Bell Atlantic area
325 W 38 St. Suite 1005 | (Stealth Marketing Works! :)
New York, NY 10018 |
Attachments:
inet-idx2.difftext/plain; charset=US-ASCII; name=inet-idx2.diffDownload
Index: src/backend/utils/adt/network.c
===================================================================
RCS file: /cvs/pgsql/pgsql/src/backend/utils/adt/network.c,v
retrieving revision 1.31
retrieving revision 1.31.1000.1
diff --unified -r1.31 -r1.31.1000.1
--- src/backend/utils/adt/network.c 2001/06/13 21:08:59 1.31
+++ src/backend/utils/adt/network.c 2001/06/17 00:21:34 1.31.1000.1
@@ -490,6 +490,32 @@
PG_RETURN_INT32(ip_bits(ip));
}
+
+/* These functions are used by planner when generating index paths
+ * for clause a << b
+ */
+
+/* return the minimal value for an IP on a given network */
+Datum
+network_scan_first(Datum in)
+{
+ return DirectFunctionCall1(network_network, in);
+}
+
+/* return "last" IP on a given network. Its the broadcast address,
+ * however, masklen has to be set to 32, since
+ * 192.168.0.255/24 is considered less than 192.168.0.255/32
+ */
+Datum
+network_scan_last(Datum in)
+{
+ return DirectFunctionCall2( inet_set_masklen,
+ DirectFunctionCall1(network_broadcast, in),
+ Int32GetDatum(32)
+ );
+
+}
+
Datum
network_broadcast(PG_FUNCTION_ARGS)
{
Index: src/backend/optimizer/path/indxpath.c
===================================================================
RCS file: /cvs/pgsql/pgsql/src/backend/optimizer/path/indxpath.c,v
retrieving revision 1.106
retrieving revision 1.106.1000.1
diff --unified -r1.106 -r1.106.1000.1
--- src/backend/optimizer/path/indxpath.c 2001/06/05 17:13:51 1.106
+++ src/backend/optimizer/path/indxpath.c 2001/06/17 00:21:33 1.106.1000.1
@@ -95,6 +95,7 @@
bool indexkey_on_left);
static List *prefix_quals(Var *leftop, Oid expr_op,
char *prefix, Pattern_Prefix_Status pstatus);
+static List *network_prefix_quals(Var *leftop, Oid expr_op, Datum rightop);
static Oid find_operator(const char *opname, Oid datatype);
static Datum string_to_datum(const char *str, Oid datatype);
static Const *string_to_const(const char *str, Oid datatype);
@@ -1761,6 +1762,11 @@
pfree(patt);
}
break;
+ case OID_INET_SUB_OP:
+ case OID_INET_SUBEQ_OP:
+ case OID_CIDR_SUB_OP:
+ case OID_CIDR_SUBEQ_OP:
+ isIndexable = 1;
}
/* done if the expression doesn't look indexable */
@@ -1810,6 +1816,14 @@
!op_class(find_operator("<", NAMEOID), opclass, relam))
isIndexable = false;
break;
+ case OID_INET_SUB_OP:
+ case OID_INET_SUBEQ_OP:
+ case OID_CIDR_SUB_OP:
+ case OID_CIDR_SUBEQ_OP:
+ if (!op_class(find_operator(">=", INETOID), opclass, relam) ||
+ !op_class(find_operator("<", INETOID), opclass, relam))
+ isIndexable = false;
+ break;
}
return isIndexable;
@@ -1924,6 +1938,15 @@
pfree(patt);
break;
+ case OID_INET_SUB_OP:
+ case OID_INET_SUBEQ_OP:
+ case OID_CIDR_SUB_OP:
+ case OID_CIDR_SUBEQ_OP:
+ constvalue = ((Const *) rightop)->constvalue;
+ resultquals = nconc(resultquals,
+ network_prefix_quals(leftop, expr_op, constvalue));
+ break;
+
default:
resultquals = lappend(resultquals, clause);
break;
@@ -1931,6 +1954,86 @@
}
return resultquals;
+}
+
+/*
+ * Given a leftop and a rightop, and a inet-class sup/sub operator,
+ * generate suitable indexqual condition(s). expr_op is the original
+ * operator.
+ * translations:
+ * a << b to (a > network(b)) and (a <= set_masklen(broadcast(b, 32)))
+ * a <<= b to (a >= network(b)) and (a <= set_masklen(broadcast(b, 32)))
+ */
+static List *
+network_prefix_quals(Var *leftop, Oid expr_op, Datum rightop)
+{
+ bool is_eq=false;
+ char *opr1name;
+ Datum opr1right;
+ Datum opr2right;
+ Oid opr1oid;
+ Oid opr2oid;
+
+ List *result;
+ Oid datatype;
+ Oper *op;
+ Expr *expr;
+
+ switch (expr_op)
+ {
+ case OID_INET_SUB_OP:
+ datatype = INETOID;
+ is_eq=false;
+ break;
+ case OID_INET_SUBEQ_OP:
+ datatype = INETOID;
+ is_eq=true;
+ break;
+ case OID_CIDR_SUB_OP:
+ datatype = CIDROID;
+ is_eq=false;
+ break;
+ case OID_CIDR_SUBEQ_OP:
+ datatype = CIDROID;
+ is_eq=true;
+ break;
+ default:
+ elog(ERROR, "network_prefix_quals: unexpected operator %u", expr_op);
+ return NIL;
+ }
+
+/* create clause "key >= network_scan_first( rightop )" */
+
+ opr1name = is_eq?">=":">";
+ opr1oid = find_operator(opr1name, datatype);
+ if (opr1oid == InvalidOid)
+ elog(ERROR, "network_prefix_quals: no %s operator for type %u", opr1name, datatype);
+
+ opr1right = network_scan_first( rightop );
+
+ op = makeOper(opr1oid, InvalidOid, BOOLOID);
+ expr = make_opclause(op, leftop,
+ (Var *) makeConst(datatype, -1, opr1right,
+ false, false, false, false )
+ );
+ result = makeList1(expr);
+
+/* create clause "key <= network_scan_last( rightop )" */
+
+ opr2right = network_scan_last( rightop );
+
+ opr2oid = find_operator("<=", datatype);
+ if (opr2oid == InvalidOid)
+ elog(ERROR, "network_prefix_quals: no <= operator for type %u", datatype);
+
+ op = makeOper(opr2oid, InvalidOid, BOOLOID);
+ expr = make_opclause(op, leftop,
+ (Var *) makeConst(datatype, -1, opr2right,
+ false, false, false, false )
+ );
+ result = lappend(result, expr);
+
+ return result;
}
/*
Index: src/include/catalog/pg_operator.h
===================================================================
RCS file: /cvs/pgsql/pgsql/src/include/catalog/pg_operator.h,v
retrieving revision 1.90
retrieving revision 1.90.1000.1
diff --unified -r1.90 -r1.90.1000.1
--- src/include/catalog/pg_operator.h 2001/06/10 22:32:35 1.90
+++ src/include/catalog/pg_operator.h 2001/06/17 00:21:34 1.90.1000.1
@@ -674,9 +674,13 @@
DATA(insert OID = 1205 ( ">" PGUID 0 b t f 869 869 16 1203 1204 0 0 network_gt scalargtsel scalargtjoinsel ));
DATA(insert OID = 1206 ( ">=" PGUID 0 b t f 869 869 16 1204 1203 0 0 network_ge scalargtsel scalargtjoinsel ));
DATA(insert OID = 931 ( "<<" PGUID 0 b t f 869 869 16 933 0 0 0 network_sub - - ));
+#define OID_INET_SUB_OP 931
DATA(insert OID = 932 ( "<<=" PGUID 0 b t f 869 869 16 934 0 0 0 network_subeq - - ));
+#define OID_INET_SUBEQ_OP 932
DATA(insert OID = 933 ( ">>" PGUID 0 b t f 869 869 16 931 0 0 0 network_sup - - ));
+#define OID_INET_SUP_OP 933
DATA(insert OID = 934 ( ">>=" PGUID 0 b t f 869 869 16 932 0 0 0 network_supeq - - ));
+#define OID_INET_SUPEQ_OP 934
/* CIDR type */
DATA(insert OID = 820 ( "=" PGUID 0 b t f 650 650 16 820 821 822 822 network_eq eqsel eqjoinsel ));
@@ -686,9 +690,13 @@
DATA(insert OID = 824 ( ">" PGUID 0 b t f 650 650 16 822 823 0 0 network_gt scalargtsel scalargtjoinsel ));
DATA(insert OID = 825 ( ">=" PGUID 0 b t f 650 650 16 823 822 0 0 network_ge scalargtsel scalargtjoinsel ));
DATA(insert OID = 826 ( "<<" PGUID 0 b t f 650 650 16 828 0 0 0 network_sub - - ));
+#define OID_CIDR_SUB_OP 826
DATA(insert OID = 827 ( "<<=" PGUID 0 b t f 650 650 16 1004 0 0 0 network_subeq - - ));
+#define OID_CIDR_SUBEQ_OP 827
DATA(insert OID = 828 ( ">>" PGUID 0 b t f 650 650 16 826 0 0 0 network_sup - - ));
+#define OID_CIDR_SUP_OP 828
DATA(insert OID = 1004 ( ">>=" PGUID 0 b t f 650 650 16 827 0 0 0 network_supeq - - ));
+#define OID_CIDR_SUPEQ_OP 1004
/* case-insensitive LIKE hacks */
DATA(insert OID = 1625 ( "~~*" PGUID 0 b t f 19 25 16 0 1626 0 0 nameiclike iclikesel iclikejoinsel ));
Index: src/include/utils/builtins.h
===================================================================
RCS file: /cvs/pgsql/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.154
retrieving revision 1.154.1000.1
diff --unified -r1.154 -r1.154.1000.1
--- src/include/utils/builtins.h 2001/06/14 01:09:22 1.154
+++ src/include/utils/builtins.h 2001/06/17 00:21:34 1.154.1000.1
@@ -527,6 +527,8 @@
extern Datum text_cidr(PG_FUNCTION_ARGS);
extern Datum text_inet(PG_FUNCTION_ARGS);
extern Datum inet_set_masklen(PG_FUNCTION_ARGS);
+extern Datum network_scan_first(Datum in);
+extern Datum network_scan_last(Datum in);
/* mac.c */
extern Datum macaddr_in(PG_FUNCTION_ARGS);
Index: src/test/regress/expected/inet.out
===================================================================
RCS file: /cvs/pgsql/pgsql/src/test/regress/expected/inet.out,v
retrieving revision 1.12
diff --unified -r1.12 inet.out
--- src/test/regress/expected/inet.out 2001/06/13 21:08:59 1.12
+++ src/test/regress/expected/inet.out 2001/06/17 00:29:32
@@ -7,6 +7,10 @@
CREATE TABLE INET_TBL (c cidr, i inet);
INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.226/24');
INSERT INTO INET_TBL (c, i) VALUES ('192.168.1.0/24', '192.168.1.226');
+INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.0/24');
+INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.0/25');
+INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.255/24');
+INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.255/25');
INSERT INTO INET_TBL (c, i) VALUES ('10', '10.1.2.3/8');
INSERT INTO INET_TBL (c, i) VALUES ('10.0.0.0', '10.1.2.3/8');
INSERT INTO INET_TBL (c, i) VALUES ('10.1.2.3', '10.1.2.3/32');
@@ -26,6 +30,10 @@
-----+----------------+------------------
| 192.168.1.0/24 | 192.168.1.226/24
| 192.168.1.0/24 | 192.168.1.226
+ | 192.168.1.0/24 | 192.168.1.0/24
+ | 192.168.1.0/24 | 192.168.1.0/25
+ | 192.168.1.0/24 | 192.168.1.255/24
+ | 192.168.1.0/24 | 192.168.1.255/25
| 10.0.0.0/8 | 10.1.2.3/8
| 10.0.0.0/32 | 10.1.2.3/8
| 10.1.2.3/32 | 10.1.2.3
@@ -34,7 +42,7 @@
| 10.0.0.0/8 | 10.1.2.3/8
| 10.0.0.0/8 | 11.1.2.3/8
| 10.0.0.0/8 | 9.1.2.3/8
-(10 rows)
+(14 rows)
-- now test some support functions
SELECT '' AS ten, i AS inet, host(i), text(i) FROM INET_TBL;
@@ -42,6 +50,10 @@
-----+------------------+---------------+------------------
| 192.168.1.226/24 | 192.168.1.226 | 192.168.1.226/24
| 192.168.1.226 | 192.168.1.226 | 192.168.1.226/32
+ | 192.168.1.0/24 | 192.168.1.0 | 192.168.1.0/24
+ | 192.168.1.0/25 | 192.168.1.0 | 192.168.1.0/25
+ | 192.168.1.255/24 | 192.168.1.255 | 192.168.1.255/24
+ | 192.168.1.255/25 | 192.168.1.255 | 192.168.1.255/25
| 10.1.2.3/8 | 10.1.2.3 | 10.1.2.3/8
| 10.1.2.3/8 | 10.1.2.3 | 10.1.2.3/8
| 10.1.2.3 | 10.1.2.3 | 10.1.2.3/32
@@ -50,7 +62,7 @@
| 10.1.2.3/8 | 10.1.2.3 | 10.1.2.3/8
| 11.1.2.3/8 | 11.1.2.3 | 11.1.2.3/8
| 9.1.2.3/8 | 9.1.2.3 | 9.1.2.3/8
-(10 rows)
+(14 rows)
SELECT '' AS ten, c AS cidr, broadcast(c),
i AS inet, broadcast(i) FROM INET_TBL;
@@ -58,6 +70,10 @@
-----+----------------+------------------+------------------+------------------
| 192.168.1.0/24 | 192.168.1.255/24 | 192.168.1.226/24 | 192.168.1.255/24
| 192.168.1.0/24 | 192.168.1.255/24 | 192.168.1.226 | 192.168.1.226
+ | 192.168.1.0/24 | 192.168.1.255/24 | 192.168.1.0/24 | 192.168.1.255/24
+ | 192.168.1.0/24 | 192.168.1.255/24 | 192.168.1.0/25 | 192.168.1.127/25
+ | 192.168.1.0/24 | 192.168.1.255/24 | 192.168.1.255/24 | 192.168.1.255/24
+ | 192.168.1.0/24 | 192.168.1.255/24 | 192.168.1.255/25 | 192.168.1.255/25
| 10.0.0.0/8 | 10.255.255.255/8 | 10.1.2.3/8 | 10.255.255.255/8
| 10.0.0.0/32 | 10.0.0.0 | 10.1.2.3/8 | 10.255.255.255/8
| 10.1.2.3/32 | 10.1.2.3 | 10.1.2.3 | 10.1.2.3
@@ -66,7 +82,7 @@
| 10.0.0.0/8 | 10.255.255.255/8 | 10.1.2.3/8 | 10.255.255.255/8
| 10.0.0.0/8 | 10.255.255.255/8 | 11.1.2.3/8 | 11.255.255.255/8
| 10.0.0.0/8 | 10.255.255.255/8 | 9.1.2.3/8 | 9.255.255.255/8
-(10 rows)
+(14 rows)
SELECT '' AS ten, c AS cidr, network(c) AS "network(cidr)",
i AS inet, network(i) AS "network(inet)" FROM INET_TBL;
@@ -74,6 +90,10 @@
-----+----------------+----------------+------------------+------------------
| 192.168.1.0/24 | 192.168.1.0/24 | 192.168.1.226/24 | 192.168.1.0/24
| 192.168.1.0/24 | 192.168.1.0/24 | 192.168.1.226 | 192.168.1.226/32
+ | 192.168.1.0/24 | 192.168.1.0/24 | 192.168.1.0/24 | 192.168.1.0/24
+ | 192.168.1.0/24 | 192.168.1.0/24 | 192.168.1.0/25 | 192.168.1.0/25
+ | 192.168.1.0/24 | 192.168.1.0/24 | 192.168.1.255/24 | 192.168.1.0/24
+ | 192.168.1.0/24 | 192.168.1.0/24 | 192.168.1.255/25 | 192.168.1.128/25
| 10.0.0.0/8 | 10.0.0.0/8 | 10.1.2.3/8 | 10.0.0.0/8
| 10.0.0.0/32 | 10.0.0.0/32 | 10.1.2.3/8 | 10.0.0.0/8
| 10.1.2.3/32 | 10.1.2.3/32 | 10.1.2.3 | 10.1.2.3/32
@@ -82,7 +102,7 @@
| 10.0.0.0/8 | 10.0.0.0/8 | 10.1.2.3/8 | 10.0.0.0/8
| 10.0.0.0/8 | 10.0.0.0/8 | 11.1.2.3/8 | 11.0.0.0/8
| 10.0.0.0/8 | 10.0.0.0/8 | 9.1.2.3/8 | 9.0.0.0/8
-(10 rows)
+(14 rows)
SELECT '' AS ten, c AS cidr, masklen(c) AS "masklen(cidr)",
i AS inet, masklen(i) AS "masklen(inet)" FROM INET_TBL;
@@ -90,6 +110,10 @@
-----+----------------+---------------+------------------+---------------
| 192.168.1.0/24 | 24 | 192.168.1.226/24 | 24
| 192.168.1.0/24 | 24 | 192.168.1.226 | 32
+ | 192.168.1.0/24 | 24 | 192.168.1.0/24 | 24
+ | 192.168.1.0/24 | 24 | 192.168.1.0/25 | 25
+ | 192.168.1.0/24 | 24 | 192.168.1.255/24 | 24
+ | 192.168.1.0/24 | 24 | 192.168.1.255/25 | 25
| 10.0.0.0/8 | 8 | 10.1.2.3/8 | 8
| 10.0.0.0/32 | 32 | 10.1.2.3/8 | 8
| 10.1.2.3/32 | 32 | 10.1.2.3 | 32
@@ -98,7 +122,7 @@
| 10.0.0.0/8 | 8 | 10.1.2.3/8 | 8
| 10.0.0.0/8 | 8 | 11.1.2.3/8 | 8
| 10.0.0.0/8 | 8 | 9.1.2.3/8 | 8
-(10 rows)
+(14 rows)
SELECT '' AS four, c AS cidr, masklen(c) AS "masklen(cidr)",
i AS inet, masklen(i) AS "masklen(inet)" FROM INET_TBL
@@ -113,10 +137,11 @@
SELECT '' AS six, c AS cidr, i AS inet FROM INET_TBL
WHERE c = i;
- six | cidr | inet
------+-------------+----------
- | 10.1.2.3/32 | 10.1.2.3
-(1 row)
+ six | cidr | inet
+-----+----------------+----------------
+ | 192.168.1.0/24 | 192.168.1.0/24
+ | 10.1.2.3/32 | 10.1.2.3
+(2 rows)
SELECT '' AS ten, i, c,
i < c AS lt, i <= c AS le, i = c AS eq,
@@ -128,6 +153,10 @@
-----+------------------+----------------+----+----+----+----+----+----+----+-----+-----+-----
| 192.168.1.226/24 | 192.168.1.0/24 | f | f | f | t | t | t | f | t | f | t
| 192.168.1.226 | 192.168.1.0/24 | f | f | f | t | t | t | t | t | f | f
+ | 192.168.1.0/24 | 192.168.1.0/24 | f | t | t | t | f | f | f | t | f | t
+ | 192.168.1.0/25 | 192.168.1.0/24 | f | f | f | t | t | t | t | t | f | f
+ | 192.168.1.255/24 | 192.168.1.0/24 | f | f | f | t | t | t | f | t | f | t
+ | 192.168.1.255/25 | 192.168.1.0/24 | f | f | f | t | t | t | t | t | f | f
| 10.1.2.3/8 | 10.0.0.0/8 | f | f | f | t | t | t | f | t | f | t
| 10.1.2.3/8 | 10.0.0.0/32 | t | t | f | f | f | t | f | f | t | t
| 10.1.2.3 | 10.1.2.3/32 | f | t | t | t | f | f | f | t | f | t
@@ -136,14 +165,18 @@
| 10.1.2.3/8 | 10.0.0.0/8 | f | f | f | t | t | t | f | t | f | t
| 11.1.2.3/8 | 10.0.0.0/8 | f | f | f | t | t | t | f | f | f | f
| 9.1.2.3/8 | 10.0.0.0/8 | t | t | f | f | f | t | f | f | f | f
-(10 rows)
+(14 rows)
-- check the conversion to/from text and set_netmask
select '' AS ten, set_masklen(inet(text(i)), 24) FROM INET_TBL;
- ten | set_masklen
+ ten | set_masklen
-----+------------------
| 192.168.1.226/24
| 192.168.1.226/24
+ | 192.168.1.0/24
+ | 192.168.1.0/24
+ | 192.168.1.255/24
+ | 192.168.1.255/24
| 10.1.2.3/24
| 10.1.2.3/24
| 10.1.2.3/24
@@ -152,5 +185,29 @@
| 10.1.2.3/24
| 11.1.2.3/24
| 9.1.2.3/24
-(10 rows)
+(14 rows)
+
+-- check that index works correctly
+create index inet_idx1 on inet_tbl(i);
+set enable_seqscan to off;
+select * from inet_tbl where i<<'192.168.1.0/24'::cidr;
+ c | i
+----------------+------------------
+ 192.168.1.0/24 | 192.168.1.0/25
+ 192.168.1.0/24 | 192.168.1.255/25
+ 192.168.1.0/24 | 192.168.1.226
+(3 rows)
+
+select * from inet_tbl where i<<='192.168.1.0/24'::cidr;
+ c | i
+----------------+------------------
+ 192.168.1.0/24 | 192.168.1.0/24
+ 192.168.1.0/24 | 192.168.1.226/24
+ 192.168.1.0/24 | 192.168.1.255/24
+ 192.168.1.0/24 | 192.168.1.0/25
+ 192.168.1.0/24 | 192.168.1.255/25
+ 192.168.1.0/24 | 192.168.1.226
+(6 rows)
+set enable_seqscan to on;
+drop index inet_idx1;
Index: src/test/regress/sql/inet.sql
===================================================================
RCS file: /cvs/pgsql/pgsql/src/test/regress/sql/inet.sql,v
retrieving revision 1.6
diff --unified -r1.6 inet.sql
--- src/test/regress/sql/inet.sql 2001/06/13 21:09:00 1.6
+++ src/test/regress/sql/inet.sql 2001/06/17 00:29:32
@@ -8,6 +8,10 @@
CREATE TABLE INET_TBL (c cidr, i inet);
INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.226/24');
INSERT INTO INET_TBL (c, i) VALUES ('192.168.1.0/24', '192.168.1.226');
+INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.0/24');
+INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.0/25');
+INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.255/24');
+INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.255/25');
INSERT INTO INET_TBL (c, i) VALUES ('10', '10.1.2.3/8');
INSERT INTO INET_TBL (c, i) VALUES ('10.0.0.0', '10.1.2.3/8');
INSERT INTO INET_TBL (c, i) VALUES ('10.1.2.3', '10.1.2.3/32');
@@ -49,3 +53,11 @@
-- check the conversion to/from text and set_netmask
select '' AS ten, set_masklen(inet(text(i)), 24) FROM INET_TBL;
+-- check that index works correctly
+create index inet_idx1 on inet_tbl(i);
+set enable_seqscan to off;
+select * from inet_tbl where i<<'192.168.1.0/24'::cidr;
+select * from inet_tbl where i<<='192.168.1.0/24'::cidr;
+set enable_seqscan to on;
+drop index inet_idx1;
+
