Make the planner assume that the entries in a VALUES list are distinct.

Previously, if we had to estimate the number of distinct values in a
VALUES column, we fell back on the default behavior used whenever we lack
statistics, which effectively is that there are Min(# of entries, 200)
distinct values.  This can be very badly off with a large VALUES list,
as noted by Jeff Janes.

We could consider actually running an ANALYZE-like scan on the VALUES,
but that seems unduly expensive, and anyway it could not deliver reliable
info if the entries are not all constants.  What seems like a better choice
is to assume that the values are all distinct.  This will sometimes be just
as wrong as the old code, but it seems more likely to be more nearly right
in many common cases.  Also, it is more consistent with what happens in
some related cases, for example WHERE x = ANY(ARRAY[1,2,3,...,n]) and
WHERE x = ANY(VALUES (1),(2),(3),...,(n)) now are estimated similarly.

This was discussed some time ago, but consensus was it'd be better
to slip it in at the start of a development cycle not near the end.
(It should've gone into v10, really, but I forgot about it.)

Discussion: https://postgr.es/m/CAMkU=1xHkyPa8VQgGcCNg3RMFFvVxUdOpus1gKcFuvVi0w6Acg@mail.gmail.com

diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index a7a06146a06cfd007d77aa4658e2a4e0c080e339..23e5526a8e1453a8e1c47459061b2b602e913f8c 100644 (file)
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -5009,6 +5009,17 @@ get_variable_numdistinct(VariableStatData *vardata, bool *isdefault)
         */
        stadistinct = 2.0;
    }
+   else if (vardata->rel && vardata->rel->rtekind == RTE_VALUES)
+   {
+       /*
+        * If the Var represents a column of a VALUES RTE, assume it's unique.
+        * This could of course be very wrong, but it should tend to be true
+        * in well-written queries.  We could consider examining the VALUES'
+        * contents to get some real statistics; but that only works if the
+        * entries are all constants, and it would be pretty expensive anyway.
+        */
+       stadistinct = -1.0;     /* unique (and all non null) */
+   }
    else
    {
        /*

diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index be2028867a85d01f4b07ab0fe03362e14c6c56a0..3ccc9d1b037cdb35dbab64beb652f6a362959405 100644 (file)
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -407,7 +407,7 @@ typedef struct PlannerInfo
  *
  *     relid - RTE index (this is redundant with the relids field, but
  *             is provided for convenience of access)
- *     rtekind - distinguishes plain relation, subquery, or function RTE
+ *     rtekind - copy of RTE's rtekind field
  *     min_attr, max_attr - range of valid AttrNumbers for rel
  *     attr_needed - array of bitmapsets indicating the highest joinrel
  *             in which each attribute is needed; if bit 0 is set then
@@ -552,7 +552,7 @@ typedef struct RelOptInfo
    /* information about a base rel (not set for join rels!) */
    Index       relid;
    Oid         reltablespace;  /* containing tablespace */
-   RTEKind     rtekind;        /* RELATION, SUBQUERY, or FUNCTION */
+   RTEKind     rtekind;        /* RELATION, SUBQUERY, FUNCTION, etc */
    AttrNumber  min_attr;       /* smallest attrno of rel (often <0) */
    AttrNumber  max_attr;       /* largest attrno of rel */
    Relids     *attr_needed;    /* array indexed [min_attr .. max_attr] */