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
