From: Tom Lane <tgl@sss.pgh.pa.us>
To: Simon Riggs <simon@2ndquadrant.com>
cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Allowing extensions to supply operator-/function-specific info
In-reply-to: 
 <CANP8+jLMj6xwZ_1kBXD_CAfMKPo1Hvo=HvEgqbg+va6k5DGoCQ@mail.gmail.com>
References: <15193.1548028093@sss.pgh.pa.us>
 <CANP8+jJ+TX2zc7hz_57BM0q+9JeZ-NE12iNyWLVAnT+JWSgNQQ@mail.gmail.com>
 <524.1548602263@sss.pgh.pa.us>
 <CANP8+jLMj6xwZ_1kBXD_CAfMKPo1Hvo=HvEgqbg+va6k5DGoCQ@mail.gmail.com>
Comments: In-reply-to Simon Riggs <simon@2ndquadrant.com>
	message dated "Tue, 29 Jan 2019 05:33:03 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <30310.1548741347.1@sss.pgh.pa.us>
Date: Tue, 29 Jan 2019 00:55:47 -0500
Message-ID: <30311.1548741347@sss.pgh.pa.us>
Precedence: bulk

Simon Riggs <simon@2ndquadrant.com> writes:
> On Sun, 27 Jan 2019 at 19:17, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> ... I don't
>> know whether that would satisfy your concern, because I'm not clear
>> on what your concern is.

> To be able to extract indexable clauses where none existed before.

That's a pretty vague statement, because it describes what I want
to do perfectly, but this doesn't:

> Hash functions assume that x = N => hash(x) = hash(N) AND x = N
> so I want to be able to assume
> x = K => f(x) = f(K) AND x = K
> for specific f()
> to allow indexable operations when we have an index on f(x) only

The problem with that is that if the only thing that's in the query is
"x = K" then there is nothing to cue the planner that it'd be worth
expending cycles thinking about f(x).  Sure, you could hang a planner
support function on the equals operator that would go off and expend
arbitrary amounts of computation looking for speculative matches ...
but nobody is going to accept that as a patch, because the cost/benefit
ratio is going to be awful for 99% of users.

The mechanism I'm proposing is based on the thought that for
specialized functions (or operators) like PostGIS' ST_Intersects(),
it'll be worth expending extra cycles when one of those shows up
in WHERE.  I don't think that scales to plain-vanilla equality though.

Conceivably, you could turn that around and look for support functions
attached to the functions/operators that are in an index expression,
and give them the opportunity to derive lossy indexquals based on
comparing the index expression to query quals.  I have no particular
interest in working on that right now, because it doesn't respond to
what I understand PostGIS' need to be, and there are only so many
hours in the day.  But maybe it could be made workable in the future.

			regards, tom lane