Re: Allowing extensions to supply operator-/function-specific info - Mailing list pgsql-hackers

Paul Ramsey <pramsey@cleverelephant.ca> writes:
> 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.

OK.

> 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!

Yes, you do have operator families: there's no such thing as an operator
class without a containing operator family, and hasn't been for quite
a long time.  If you write CREATE OPERATOR CLASS without a FAMILY
clause, the command silently creates an opfamily with the same name you
specified for the opclass, and links the two together.

> 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.

Right.  So the hard part here is to figure out whether the OID you're
handed matches one of these operator families.  As I mentioned (in
the other thread [1], maybe you didn't see it?) the best short-term
idea I've got for that is to look up the opfamily by OID (see the
OPFAMILYOID syscache) and check to see if its name matches one of
the above.  You might want to verify that the index AM's OID is what
you expect, too, just for a little extra safety.

> 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?

It's whichever one the index column's opclass belongs to.  Basically what
you're trying to do here is verify whether the index will support the
optimization you want to perform.

> 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).

You definitely want to read this:

https://www.postgresql.org/docs/devel/xindex.html#XINDEX-OPFAMILY

and maybe some of the surrounding sections.

> 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.

Sure, but that was a pretty lame way of getting the optimization,
as you well know because you've been fighting its deficiencies for
so long.

Perhaps at some point we'll have some infrastructure that makes this
less painful, but it's not happening for v12.

            regards, tom lane

[1] https://www.postgresql.org/message-id/22876.1550591107@sss.pgh.pa.us