While playing around with the BOX and POINT datatypes, I was surprised to
note that BOX @> POINT (and likewise POINT <@ BOX) queries were not using
the GiST index I had created on the BOX column. The attached patch adds a
new strategy @>(BOX,POINT) to the box_ops opclass. Internally,
gist_box_consistent simply transforms the POINT into its corresponding BOX.
This is my first Postgres patch, and I wasn't able to figure out how to go
about creating a regression test for this change. (All existing tests do
pass, but none of them seem to specifically test index behaviour.)
I know it is quite late in the CommitFest, should I add this to CF-Next?
-Andrew
diff --git a/src/backend/access/gist/gistproc.c b/src/backend/access/gist/gistproc.c
index 86a5d90..a2c6cb6 100644
*** a/src/backend/access/gist/gistproc.c
--- b/src/backend/access/gist/gistproc.c
*************** gist_box_consistent(PG_FUNCTION_ARGS)
*** 96,101 ****
--- 96,113 ----
if (DatumGetBoxP(entry->key) == NULL || query == NULL)
PG_RETURN_BOOL(FALSE);
+ if (strategy == 27)
+ {
+ /* Convert BOX @> POINT to the equivalent BOX @> BOX query */
+ Point *q_point = PG_GETARG_POINT_P(1);
+ BOX q_box;
+
+ q_box.low = *q_point;
+ q_box.high = *q_point;
+ query = &q_box;
+ strategy = 7; /* Strategy number for BOX @> BOX */
+ }
+
/*
* if entry is not leaf, use rtree_internal_consistent, else use
* gist_box_leaf_consistent
diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h
index aabb900..eb03255 100644
*** a/src/include/catalog/pg_amop.h
--- b/src/include/catalog/pg_amop.h
*************** DATA(insert ( 2593 603 603 11 s 2573 7
*** 595,600 ****
--- 595,601 ----
DATA(insert ( 2593 603 603 12 s 2572 783 0 ));
DATA(insert ( 2593 603 603 13 s 2863 783 0 ));
DATA(insert ( 2593 603 603 14 s 2862 783 0 ));
+ DATA(insert ( 2593 603 600 27 s 433 783 0 ));
/*
* gist point_ops
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
