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
Date: Tue, 29 Jan 2019 00:55:47 -0500
Message-ID: <30311.1548741347@sss.pgh.pa.us> (raw)
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>
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
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, simon@2ndquadrant.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Allowing extensions to supply operator-/function-specific info
In-Reply-To: <30311.1548741347@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