Horton

Re: Allowing extensions to supply operator-/function-specific info

Paul Ramsey <pramsey@cleverelephant.ca>

View thread
From: Paul Ramsey <pramsey@cleverelephant.ca>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2019-02-25T22:38:28Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Allow extensions to generate lossy index conditions.

  2. Build out the planner support function infrastructure.

  3. Create the infrastructure for planner support functions.

  4. Disable transforms that replaced AT TIME ZONE with RelabelType.

On Mon, Jan 28, 2019 at 9:51 AM Tom Lane <tgl@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.