From: Tom Lane <tgl@sss.pgh.pa.us>
To: Paul Ramsey <pramsey@cleverelephant.ca>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Allowing extensions to supply operator-/function-specific info
Date: Mon, 25 Feb 2019 18:01:14 -0500
Message-ID: <7671.1551135674@sss.pgh.pa.us> (raw)
In-Reply-To: <CACowWR3jZ4Xo1atU5xTDpEoeeKSw1==sKPhCy3a29HtcE9oQVQ@mail.gmail.com>
References: <15193.1548028093@sss.pgh.pa.us>
<6953.1548297558@sss.pgh.pa.us>
<6044.1548524131@sss.pgh.pa.us>
<CA+TgmoYkLjVcbZZ8PYyaCTMYZJCQ8eiod8MSRMDaczjBw=a0bQ@mail.gmail.com>
<19993.1548697885@sss.pgh.pa.us>
<CACowWR3jZ4Xo1atU5xTDpEoeeKSw1==sKPhCy3a29HtcE9oQVQ@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, pramsey@cleverelephant.ca, robertmhaas@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Allowing extensions to supply operator-/function-specific info
In-Reply-To: <7671.1551135674@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox