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

Commitce6e31d

Browse files
committed
Teach the planner to treat a partial unique index as proving a variable is
unique for a particular query, if the index predicate is satisfied. Thisrequires a bit of reordering of operations so that we check the predicatesbefore doing any selectivity estimates, but shouldn't really cause anynoticeable slowdown. Per a comment from Michal Politowski.
1 parent57b5984 commitce6e31d

File tree

5 files changed

+70
-39
lines changed

5 files changed

+70
-39
lines changed

‎src/backend/optimizer/path/allpaths.c

Lines changed: 11 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/path/allpaths.c,v 1.179 2009/01/01 17:23:43 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/allpaths.c,v 1.180 2009/02/15 20:16:21 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -225,19 +225,25 @@ set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
225225
return;
226226
}
227227

228+
/*
229+
* Test any partial indexes of rel for applicability. We must do this
230+
* first since partial unique indexes can affect size estimates.
231+
*/
232+
check_partial_indexes(root,rel);
233+
228234
/* Mark rel with estimated output rows, width, etc */
229235
set_baserel_size_estimates(root,rel);
230236

231-
/* Test any partial indexes of rel for applicability */
232-
check_partial_indexes(root,rel);
233-
234237
/*
235238
* Check to see if we can extract any restriction conditions from join
236239
* quals that are OR-of-AND structures. If so, add them to the rel's
237-
* restriction list, andrecompute thesize estimates.
240+
* restriction list, andredo theabove steps.
238241
*/
239242
if (create_or_index_quals(root,rel))
243+
{
244+
check_partial_indexes(root,rel);
240245
set_baserel_size_estimates(root,rel);
246+
}
241247

242248
/*
243249
* Generate paths and add them to the rel's pathlist.

‎src/backend/optimizer/path/indxpath.c

Lines changed: 10 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/optimizer/path/indxpath.c,v 1.235 2009/01/01 17:23:43 momjian Exp $
12+
* $PostgreSQL: pgsql/src/backend/optimizer/path/indxpath.c,v 1.236 2009/02/15 20:16:21 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -1367,8 +1367,12 @@ match_rowcompare_to_indexcol(IndexOptInfo *index,
13671367

13681368
/*
13691369
* check_partial_indexes
1370-
*Check each partial index of the relation, and mark it predOK or not
1371-
*depending on whether the predicate is satisfied for this query.
1370+
*Check each partial index of the relation, and mark it predOK if
1371+
*the index's predicate is satisfied for this query.
1372+
*
1373+
* Note: it is possible for this to get re-run after adding more restrictions
1374+
* to the rel; so we might be able to prove more indexes OK. We assume that
1375+
* adding more restrictions can't make an index not OK.
13721376
*/
13731377
void
13741378
check_partial_indexes(PlannerInfo*root,RelOptInfo*rel)
@@ -1383,6 +1387,9 @@ check_partial_indexes(PlannerInfo *root, RelOptInfo *rel)
13831387
if (index->indpred==NIL)
13841388
continue;/* ignore non-partial indexes */
13851389

1390+
if (index->predOK)
1391+
continue;/* don't repeat work if already proven OK */
1392+
13861393
index->predOK=predicate_implied_by(index->indpred,
13871394
restrictinfo_list);
13881395
}

‎src/backend/optimizer/path/orindxpath.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/path/orindxpath.c,v 1.87 2009/02/06 23:43:23 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/orindxpath.c,v 1.88 2009/02/15 20:16:21 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -181,6 +181,6 @@ create_or_index_quals(PlannerInfo *root, RelOptInfo *rel)
181181
/* It isn't an outer join clause, so no need to adjust outer_selec */
182182
}
183183

184-
/* Tell caller to recomputerel's rows estimate */
184+
/* Tell caller to recomputepartial index status and rowcount estimate */
185185
return true;
186186
}

‎src/backend/optimizer/util/plancat.c

Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.154 2009/01/07 22:40:49 tgl Exp $
12+
* $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.155 2009/02/15 20:16:21 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -939,15 +939,16 @@ has_unique_index(RelOptInfo *rel, AttrNumber attno)
939939

940940
/*
941941
* Note: ignore partial indexes, since they don't allow us to conclude
942-
* that all attr values are distinct. We don't take any interest in
943-
* expressional indexes either. Also, a multicolumn unique index
944-
* doesn't allow us to conclude that just the specified attr is
945-
* unique.
942+
* that all attr values are distinct, *unless* they are marked predOK
943+
* which means we know the index's predicate is satisfied by the query.
944+
* We don't take any interest in expressional indexes either. Also, a
945+
* multicolumn unique index doesn't allow us to conclude that just the
946+
* specified attr is unique.
946947
*/
947948
if (index->unique&&
948949
index->ncolumns==1&&
949950
index->indexkeys[0]==attno&&
950-
index->indpred==NIL)
951+
(index->indpred==NIL||index->predOK))
951952
return true;
952953
}
953954
return false;

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

Lines changed: 40 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
*
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.258 2009/01/01 17:23:50 momjian Exp $
18+
* $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.259 2009/02/15 20:16:21 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -234,6 +234,15 @@ var_eq_const(VariableStatData *vardata, Oid operator,
234234
if (constisnull)
235235
return0.0;
236236

237+
/*
238+
* If we matched the var to a unique index, assume there is exactly one
239+
* match regardless of anything else. (This is slightly bogus, since
240+
* the index's equality operator might be different from ours, but it's
241+
* more likely to be right than ignoring the information.)
242+
*/
243+
if (vardata->isunique&&vardata->rel&&vardata->rel->tuples >=1.0)
244+
return1.0 /vardata->rel->tuples;
245+
237246
if (HeapTupleIsValid(vardata->statsTuple))
238247
{
239248
Form_pg_statisticstats;
@@ -357,6 +366,15 @@ var_eq_non_const(VariableStatData *vardata, Oid operator,
357366
{
358367
doubleselec;
359368

369+
/*
370+
* If we matched the var to a unique index, assume there is exactly one
371+
* match regardless of anything else. (This is slightly bogus, since
372+
* the index's equality operator might be different from ours, but it's
373+
* more likely to be right than ignoring the information.)
374+
*/
375+
if (vardata->isunique&&vardata->rel&&vardata->rel->tuples >=1.0)
376+
return1.0 /vardata->rel->tuples;
377+
360378
if (HeapTupleIsValid(vardata->statsTuple))
361379
{
362380
Form_pg_statisticstats;
@@ -3969,6 +3987,8 @@ get_join_variables(PlannerInfo *root, List *args, SpecialJoinInfo *sjinfo,
39693987
*atttype, atttypmod: type data to pass to get_attstatsslot(). This is
39703988
*commonly the same as the exposed type of the variable argument,
39713989
*but can be different in binary-compatible-type cases.
3990+
*isunique: TRUE if we were able to match the var to a unique index,
3991+
*implying its values are unique for this query.
39723992
*
39733993
* Caller is responsible for doing ReleaseVariableStats() before exiting.
39743994
*/
@@ -4005,6 +4025,7 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
40054025
vardata->rel=find_base_rel(root,var->varno);
40064026
vardata->atttype=var->vartype;
40074027
vardata->atttypmod=var->vartypmod;
4028+
vardata->isunique=has_unique_index(vardata->rel,var->varattno);
40084029

40094030
rte=root->simple_rte_array[var->varno];
40104031

@@ -4121,13 +4142,6 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
41214142
if (indexpr_item==NULL)
41224143
continue;/* no expressions here... */
41234144

4124-
/*
4125-
* Ignore partial indexes since they probably don't reflect
4126-
* whole-relation statistics. Possibly reconsider this later.
4127-
*/
4128-
if (index->indpred)
4129-
continue;
4130-
41314145
for (pos=0;pos<index->ncolumns;pos++)
41324146
{
41334147
if (index->indexkeys[pos]==0)
@@ -4147,9 +4161,19 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
41474161
*/
41484162
if (index->unique&&
41494163
index->ncolumns==1&&
4150-
index->indpred==NIL)
4164+
(index->indpred==NIL||index->predOK))
41514165
vardata->isunique= true;
4152-
/* Has it got stats? */
4166+
4167+
/*
4168+
* Has it got stats? We only consider stats for
4169+
* non-partial indexes, since partial indexes
4170+
* probably don't reflect whole-relation statistics;
4171+
* the above check for uniqueness is the only info
4172+
* we take from a partial index.
4173+
*
4174+
* An index stats hook, however, must make its own
4175+
* decisions about what to do with partial indexes.
4176+
*/
41534177
if (get_index_stats_hook&&
41544178
(*get_index_stats_hook) (root,index->indexoid,
41554179
pos+1,vardata))
@@ -4163,7 +4187,7 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
41634187
!vardata->freefunc)
41644188
elog(ERROR,"no function provided to release variable stats with");
41654189
}
4166-
else
4190+
elseif (index->indpred==NIL)
41674191
{
41684192
vardata->statsTuple=
41694193
SearchSysCache(STATRELATT,
@@ -4254,19 +4278,12 @@ get_variable_numdistinct(VariableStatData *vardata)
42544278

42554279
/*
42564280
* If there is a unique index for the variable, assume it is unique no
4257-
* matter what pg_statistic says (the statistics could be out of date).
4258-
* Can skip search if we already think it's unique.
4281+
* matter what pg_statistic says; the statistics could be out of date,
4282+
* or we might have found a partial unique index that proves the var
4283+
* is unique for this query.
42594284
*/
4260-
if (stadistinct!=-1.0)
4261-
{
4262-
if (vardata->isunique)
4263-
stadistinct=-1.0;
4264-
elseif (vardata->var&&IsA(vardata->var,Var)&&
4265-
vardata->rel&&
4266-
has_unique_index(vardata->rel,
4267-
((Var*)vardata->var)->varattno))
4268-
stadistinct=-1.0;
4269-
}
4285+
if (vardata->isunique)
4286+
stadistinct=-1.0;
42704287

42714288
/*
42724289
* If we had an absolute estimate, use that.

0 commit comments

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