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

Commitdcef5b0

Browse files
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 makesuse of partial unique indexes as uniqueness proofs. It is incorrect touse these as the predicates used by check_index_predicates() to setpredOK makes use of not only baserestrictinfo quals as proofs, but alsoqual from join conditions. For relation_has_unique_index_for()'s case, weneed to know the relation is unique for a given set of columns before anyjoins are evaluated, so if predOK was only set to true due to some joinqual, then it's unsafe to use such indexes inrelation_has_unique_index_for(). The final plan may not even make useof that index, which could result in reading tuples that are not asunique as the planner previously expected them to be.Bug: #17975Reported-by: Tor Erik LinnerudBackpatch-through: 11, all supported versionsDiscussion:https://postgr.es/m/17975-98a90c156f25c952%40postgresql.org
1 parent2f6e826 commitdcef5b0

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
@@ -3593,10 +3593,13 @@ relation_has_unique_index_for(PlannerInfo *root, RelOptInfo *rel,
35933593

35943594
/*
35953595
* If the index is not unique, or not immediately enforced, or if it's
3596-
* a partial index that doesn't match the query, it's useless here.
3596+
* a partial index, it's useless here. We're unable to make use of
3597+
* predOK partial unique indexes due to the fact that
3598+
* check_index_predicates() also makes use of join predicates to
3599+
* determine if the partial index is usable. Here we need proofs that
3600+
* hold true before any joins are evaluated.
35973601
*/
3598-
if (!ind->unique|| !ind->immediate||
3599-
(ind->indpred!=NIL&& !ind->predOK))
3602+
if (!ind->unique|| !ind->immediate||ind->indpred!=NIL)
36003603
continue;
36013604

36023605
/*

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

Lines changed: 4 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -602,18 +602,17 @@ rel_supports_distinctness(PlannerInfo *root, RelOptInfo *rel)
602602
/*
603603
* For a plain relation, we only know how to prove uniqueness by
604604
* reference to unique indexes. Make sure there's at least one
605-
* suitable unique index. It must be immediately enforced, andif
606-
*it's apartial index, it must match the query.(Keep these
607-
*conditions in sync withrelation_has_unique_index_for!)
605+
* suitable unique index. It must be immediately enforced, andnot a
606+
* partial index.(Keep these conditions in sync with
607+
* relation_has_unique_index_for!)
608608
*/
609609
ListCell*lc;
610610

611611
foreach(lc,rel->indexlist)
612612
{
613613
IndexOptInfo*ind= (IndexOptInfo*)lfirst(lc);
614614

615-
if (ind->unique&&ind->immediate&&
616-
(ind->indpred==NIL||ind->predOK))
615+
if (ind->unique&&ind->immediate&&ind->indpred==NIL)
617616
return true;
618617
}
619618
}

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

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

6179+
create unique index j1_id2_idx on j1(id2) where id2 is not null;
6180+
-- ensure we don't use a partial unique index as unique proofs
6181+
explain (verbose, costs off)
6182+
select * from j1
6183+
inner join j2 on j1.id2 = j2.id2;
6184+
QUERY PLAN
6185+
------------------------------------------
6186+
Nested Loop
6187+
Output: j1.id1, j1.id2, j2.id1, j2.id2
6188+
Join Filter: (j1.id2 = j2.id2)
6189+
-> Seq Scan on public.j2
6190+
Output: j2.id1, j2.id2
6191+
-> Seq Scan on public.j1
6192+
Output: j1.id1, j1.id2
6193+
(7 rows)
6194+
6195+
drop index j1_id2_idx;
61796196
-- validate logic in merge joins which skips mark and restore.
61806197
-- it should only do this if all quals which were used to detect the unique
61816198
-- 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
@@ -2125,6 +2125,15 @@ explain (verbose, costs off)
21252125
select*from j1
21262126
left join j2onj1.id1=j2.id1wherej1.id2=1;
21272127

2128+
createunique indexj1_id2_idxon j1(id2)where id2is not null;
2129+
2130+
-- ensure we don't use a partial unique index as unique proofs
2131+
explain (verbose, costs off)
2132+
select*from j1
2133+
inner join j2onj1.id2=j2.id2;
2134+
2135+
dropindex j1_id2_idx;
2136+
21282137
-- validate logic in merge joins which skips mark and restore.
21292138
-- it should only do this if all quals which were used to detect the unique
21302139
-- are present as join quals, and not plain quals.

0 commit comments

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