Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit2b74303

Browse files
committed
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 aVALUES column, we fell back on the default behavior used whenever we lackstatistics, 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 reliableinfo if the entries are not all constants. What seems like a better choiceis to assume that the values are all distinct. This will sometimes be justas wrong as the old code, but it seems more likely to be more nearly rightin many common cases. Also, it is more consistent with what happens insome related cases, for example WHERE x = ANY(ARRAY[1,2,3,...,n]) andWHERE x = ANY(VALUES (1),(2),(3),...,(n)) now are estimated similarly.This was discussed some time ago, but consensus was it'd be betterto 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
1 parentac883ac commit2b74303

File tree

2 files changed

+13
-2
lines changed

2 files changed

+13
-2
lines changed

‎src/backend/utils/adt/selfuncs.c

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5009,6 +5009,17 @@ get_variable_numdistinct(VariableStatData *vardata, bool *isdefault)
50095009
*/
50105010
stadistinct=2.0;
50115011
}
5012+
elseif (vardata->rel&&vardata->rel->rtekind==RTE_VALUES)
5013+
{
5014+
/*
5015+
* If the Var represents a column of a VALUES RTE, assume it's unique.
5016+
* This could of course be very wrong, but it should tend to be true
5017+
* in well-written queries. We could consider examining the VALUES'
5018+
* contents to get some real statistics; but that only works if the
5019+
* entries are all constants, and it would be pretty expensive anyway.
5020+
*/
5021+
stadistinct=-1.0;/* unique (and all non null) */
5022+
}
50125023
else
50135024
{
50145025
/*

‎src/include/nodes/relation.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -407,7 +407,7 @@ typedef struct PlannerInfo
407407
*
408408
*relid - RTE index (this is redundant with the relids field, but
409409
*is provided for convenience of access)
410-
*rtekind -distinguishes plain relation, subquery, or function RTE
410+
*rtekind -copy of RTE's rtekind field
411411
*min_attr, max_attr - range of valid AttrNumbers for rel
412412
*attr_needed - array of bitmapsets indicating the highest joinrel
413413
*in which each attribute is needed; if bit 0 is set then
@@ -552,7 +552,7 @@ typedef struct RelOptInfo
552552
/* information about a base rel (not set for join rels!) */
553553
Indexrelid;
554554
Oidreltablespace;/* containing tablespace */
555-
RTEKindrtekind;/* RELATION, SUBQUERY,orFUNCTION */
555+
RTEKindrtekind;/* RELATION, SUBQUERY, FUNCTION, etc */
556556
AttrNumbermin_attr;/* smallest attrno of rel (often <0) */
557557
AttrNumbermax_attr;/* largest attrno of rel */
558558
Relids*attr_needed;/* array indexed [min_attr .. max_attr] */

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp