8000 Don't use partial unique indexes for unique proofs in the planner · postgres/postgres@f6345f0 · GitHub
[go: up one dir, main page]
Skip to content

Commit f6345f0

Browse files
david-rowleydavid-rowley
committed
Don't use partial unique indexes for unique proofs in the planner
Here we adjust relation_has_unique_index_for() so that it no longer makes use of partial unique indexes as uniqueness proofs. It is incorrect to use these as the predicates used by check_index_predicates() to set predOK makes use of not only baserestrictinfo quals as proofs, but also qual from join conditions. For relation_has_unique_index_for()'s case, we need to know the relation is unique for a given set of columns before any joins are evaluated, so if predOK was only set to true due to some join qual, then it's unsafe to use such indexes in relation_has_unique_index_for(). The final plan may not even make use of that index, which could result in reading tuples that are not as unique as the planner previously expected them to be. Bug: #17975 Reported-by: Tor Erik Linnerud Backpatch-through: 11, all supported versions Discussion: https://postgr.es/m/17975-98a90c156f25c952%40postgresql.org
1 parent b103d61 commit f6345f0

File tree

4 files changed

+36
-8
lines changed

4 files changed

+36
-8
lines changed

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

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2988,10 +2988,13 @@ relation_has_unique_index_for(PlannerInfo *root, RelOptInfo *rel,
29882988

29892989
/*
29902990
* If the index is not unique, or not immediately enforced, or if it's
2991-
* a partial index that doesn't match the query, it's useless here.
2991+
* a partial index, it's useless here. We're unable to make use of
2992+
* predOK partial unique indexes due to the fact that
2993+
* check_index_predicates() also makes use of join predicates to
2994+
* determine if the partial index is usable. Here we need proofs that
2995+
* hold true before any joins are evaluated.
29922996
*/
2993-
if (!ind->unique || !ind->immediate ||
2994-
(ind->indpred != NIL && !ind->predOK))
2997+
if (!ind->unique || !ind->immediate || ind->indpred != NIL)
29952998
continue;
29962999

29973000
/*

‎src/backend/optimizer/plan/analyzejoins.c‎

Lines changed: 4 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -599,18 +599,17 @@ rel_supports_distinctness(PlannerInfo *root, RelOptInfo *rel)
599599
/*
600600
* For a plain relation, we only know how to prove uniqueness by
601601
* reference to unique indexes. Make sure there's at least one
602-
* suitable unique index. It must be immediately enforced, and if
603-
* it's a partial index, it must match the query. (Keep these
604-
* conditions in sync with relation_has_unique_index_for!)
602+
* suitable unique index. It must be immediately enforced, and not a
603+
* partial index. (Keep these conditions in sync with
604+
* relation_has_unique_index_for!)
605605
*/
606606
ListCell *lc;
607607

608608
foreach(lc, rel->indexlist)
609609
{
610610
IndexOptInfo *ind = (IndexOptInfo *) lfirst(lc);
611611

612-
if (ind->unique && ind->immediate &&
613-
(ind->indpred == NIL || ind->predOK))
612+
if (ind->unique && ind->immediate && ind->indpred == NIL)
614613
return true;
615614
}
616615
}

‎src/test/regress/expected/join.out‎

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5975,6 +5975,23 @@ left join j2 on j1.id1 = j2.id1 where j1.id2 = 1;
59755975
Output: j2.id1, j2.id2
59765976
(8 rows)
59775977

5978+
create unique index j1_id2_idx on j1(id2) where id2 is not null;
5979+
-- ensure we don't use a partial unique index as unique proofs
5980+
explain (verbose, costs off)
5981+
select * from j1
5982+
inner join j2 on j1.id2 = j2.id2;
5983+
QUERY PLAN
5984+
------------------------------------------
5985+
Nested Loop
5986+
Output: j1.id1, j1.id2, j2.id1, j2.id2
5987+
Join Filter: (j1.id2 = j2.id2)
5988+
-> Seq Scan on public.j2
5989+
Output: j2.id1, j2.id2
5990+
-> Seq Scan on public.j1
5991+
Output: j1.id1, j1.id2
5992+
(7 rows)
5993+
5994+
drop index j1_id2_idx;
59785995
-- validate logic in merge joins which skips mark and restore.
59795996
-- it should only do this if all quals which were used to detect the unique
59805997
-- are present as join quals, and not plain quals.

‎src/test/regress/sql/join.sql‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2012,6 +2012,15 @@ explain (verbose, costs off)
20122012
select * from j1
20132013
left join j2 on j1.id1 = j2.id1 where j1.id2 = 1;
20142014

2015+
create unique index j1_id2_idx on j1(id2) where id2 is not null;
2016+
2017+
-- ensure we don't use a partial unique index as unique proofs
2018+
explain (verbose, costs off)
2019+
select * from j1
2020+
inner join j2 on j1.id2 = j2.id2;
2021+
2022+
drop index j1_id2_idx;
2023+
20152024
-- validate logic in merge joins which skips mark and restore.
20162025
-- it should only do this if all quals which were used to detect the unique
20172026
-- are present as join quals, and not plain quals.

0 commit comments

Comments
 (0)
0