Build out the planner support function infrastructure

Enterprise / PostgreSQL - Tom Lane [sss.pgh.pa.us] - 9 February 2019 23:32 EST

Add support function requests for estimating the selectivity, cost, and number of result rows (if a SRF) of the target function.

The lack of a way to estimate selectivity of a boolean-returning function in WHERE has been a recognized deficiency of the planner since Berkeley days. This commit finally fixes it.

In addition, non-constant estimates of cost and number of output rows are now possible. We still fall back to looking at procost and prorows if the support function doesn't service the request, of course.

To make concrete use of the possibility of estimating output rowcount for SRFs, this commit adds support functions for array_unnest(anyarray) and the integer variants of generate_series; the lack of plausible rowcount estimates for those, even when it's obvious to a human, has been a repeated subject of complaints. Obviously, much more could now be done in this line, but I'm mostly just trying to get the infrastructure in place.

Discussion: https://postgr.es/m/15193.1548028093@sss.pgh.pa.us

a391ff3c3d Build out the planner support function infrastructure.
contrib/postgres_fdw/postgres_fdw.c | 3 +-
doc/src/sgml/xfunc.sgml | 21 +++
src/backend/optimizer/path/clausesel.c | 15 ++
src/backend/optimizer/path/costsize.c | 50 ++++---
src/backend/optimizer/util/clauses.c | 27 ++--
src/backend/optimizer/util/pathnode.c | 2 +-
src/backend/optimizer/util/plancat.c | 182 +++++++++++++++++++++++
src/backend/utils/adt/arrayfuncs.c | 34 +++++
src/backend/utils/adt/int.c | 74 +++++++++
src/backend/utils/adt/int8.c | 73 +++++++++
src/backend/utils/adt/selfuncs.c | 13 +-
src/backend/utils/cache/lsyscache.c | 45 ++----
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_proc.dat | 25 +++-
src/include/nodes/nodes.h | 5 +-
src/include/nodes/pathnodes.h | 2 +-
src/include/nodes/supportnodes.h | 100 +++++++++++++
src/include/optimizer/clauses.h | 2 +-
src/include/optimizer/plancat.h | 14 ++
src/include/utils/lsyscache.h | 3 +-
src/test/regress/expected/misc_functions.out | 60 ++++++++
src/test/regress/expected/subselect.out | 8 +-
src/test/regress/input/create_function_1.source | 5 +
src/test/regress/output/create_function_1.source | 4 +
src/test/regress/regress.c | 77 ++++++++++
src/test/regress/sql/misc_functions.sql | 32 ++++
src/test/regress/sql/subselect.sql | 4 +-
27 files changed, 792 insertions(+), 90 deletions(-)

Upstream: git.postgresql.org


  • Share