Simon Riggs <si...@2ndquadrant.com> writes: > On Sun, 20 Jan 2019 at 23:48, Tom Lane <t...@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