On Mon, Jan 28, 2019 at 9:51 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > is people like PostGIS, who already cleared that bar. I hope that > we'll soon have a bunch of examples, like those in the 0004 patch, > that people can look at to see how to do things in this area. I see > no reason to believe it'll be all that much harder than anything > else extension authors have to do.
It's a little harder :) So... trying to figure out how to use SupportRequestIndexCondition to convert a call to Intersects() in to a call that also has the operator && as well. Looking at the examples, they are making use of the opfamily that comes in SupportRequestIndexCondition.opfamily. That opfamily Oid is the first one in the IndexOptInfo.opfamily array. Here's where my thread of understanding fails to follow. I have, in PostGIS, actually no operator families defined (CREATE OPERATOR FAMILY). I do, however, have quite a few operator classes defined for geometry: 10, actually! btree_geometry_ops hash_geometry_ops gist_geometry_ops_2d gist_geometry_ops_nd brin_geometry_inclusion_ops_2d brin_geometry_inclusion_ops_3d brin_geometry_inclusion_ops_4d spgist_geometry_ops_2d spgist_geometry_ops_nd spgist_geometry_ops_nd Some of those are not useful to me (btree, hash) for sure. Some of them (gist_geometry_ops_2d, spgist_geometry_ops_2d ) use the && operator to indicate the lossy operation I would like to combine with ST_Intersects. Some of them (gist_geometry_ops_nd, spgist_geometry_ops_nd) use the &&& operator to indicate the lossy operation I would like to combine with ST_Intersects. A given call to ST_Intersects(tbl1.geom, tbl2.geom) could have two indexes to apply the problem, but SupportRequestIndexCondition.opfamily will, I assume, only be exposing one to me: which one? Anyways, to true up how hard this is, I've been carefully reading the implementations for network address types and LIKE, and I'm still barely at the WTF stage. The selectivity and the number of rows support modes I could do. The SupportRequestIndexCondition is based on a detailed knowledge of what an operator family is, an operator class is, how those relate to types... I think I can get there, but it's going to be far from easy (for me). And it'll put a pretty high bar in front of anyone who previously just whacked an inline SQL function in place to get an index assisted function up and running. P.
