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

On Tue, 29 Jan 2019 at 09:55, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > On Sun, 27 Jan 2019 at 19:17, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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).

I agree. That is the equivalent of a SeqScan; the wrong way to approach it.

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

That way around is the right way. If an index exists, explore whether it
can be used or not. If there are no indexes with appropriate support
functions, it will cost almost nothing to normal queries.

The problem of deriving potentially useful indexes is more expensive, I
understand.

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

I thought the whole exercise was about adding generic tools for everybody
to use. The Tom I've worked with for more than a few years would not have
said that; that is my normal line! You said PostGIS was looking to
"automatically convert WHERE clauses into lossy index quals." which sounds
very similar to what I outlined.

Either way, thanks.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services