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+jJ+TX2zc7hz_57BM0q+9JeZ-NE12iNyWLVAnT+JWSgNQQ@mail.gmail.com>
References: <15193.1548028093@sss.pgh.pa.us>
 <CANP8+jJ+TX2zc7hz_57BM0q+9JeZ-NE12iNyWLVAnT+JWSgNQQ@mail.gmail.com>
Comments: In-reply-to Simon Riggs <simon@2ndquadrant.com>
	message dated "Sun, 27 Jan 2019 08:00:31 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <523.1548602263.1@sss.pgh.pa.us>
Date: Sun, 27 Jan 2019 10:17:43 -0500
Message-ID: <524.1548602263@sss.pgh.pa.us>
Precedence: bulk

Simon Riggs <simon@2ndquadrant.com> writes:
> On Sun, 20 Jan 2019 at 23:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> What I'm envisioning therefore is that we allow an auxiliary function ...

> Does this help with these cases?

> * Allow a set returning function to specify number of output rows, in cases
> where that is variable and dependent upon the input params?

Yes, within the usual limits of what the planner can know.  The 0004
patch I posted yesterday correctly estimates the number of rows for
constant-arguments cases of generate_series() and unnest(anyarray),
and it also understands unnest(array[x,y,z,...]) even when some of the
array[] elements aren't constants.  There's room to add knowledge about
other SRFs, but those are cases I can recall hearing complaints about.

> * Allow a normal term to match a functional index, e.g. WHERE x =
> 'abcdefgh' => WHERE substr(x, 1 , 5) = 'abcde' AND x = 'abcdefgh'

I'm a bit confused about what you think this example means.  I do
intend to work on letting extensions define rules for extracting
index clauses from function calls, because that's the requirement
that PostGIS is after in the thread that started this.  I don't
know whether that would satisfy your concern, because I'm not clear
on what your concern is.

> * Allow us to realise that ORDER BY f(x) => ORDER BY x so we can use
> ordered paths from indexes, or avoid sorts.

Hm.  That's not part of what I'm hoping to get done for v12, but you
could imagine a future extension to add a support request type that
allows deriving related pathkeys.  There would be a lot of work to do
to make that happen, but the aspect of it that requires adding
function-specific knowledge could usefully be packaged as a
support-function request.

			regards, tom lane