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

Commit8f2ec8c

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 parent3547035 commit8f2ec8c

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

35583558
/*
35593559
* If the index is not unique, or not immediately enforced, or if it's
3560-
* a partial index that doesn't match the query, it's useless here.
3560+
* a partial index, it's useless here. We're unable to make use of
3561+
* predOK partial unique indexes due to the fact that
3562+
* check_index_predicates() also makes use of join predicates to
3563+
* determine if the partial index is usable. Here we need proofs that
3564+
* hold true before any joins are evaluated.
35613565
*/
3562-
if (!ind->unique|| !ind->immediate||
3563-
(ind->indpred!=NIL&& !ind->predOK))
3566+
if (!ind->unique|| !ind->immediate||ind->indpred!=NIL)
35643567
continue;
35653568

35663569
/*

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

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

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

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

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

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

6506+
create unique index j1_id2_idx on j1(id2) where id2 is not null;
6507+
-- ensure we don't use a partial unique index as unique proofs
6508+
explain (verbose, costs off)
6509+
select * from j1
6510+
inner join j2 on j1.id2 = j2.id2;
6511+
QUERY PLAN
6512+
------------------------------------------
6513+
Nested Loop
6514+
Output: j1.id1, j1.id2, j2.id1, j2.id2
6515+
Join Filter: (j1.id2 = j2.id2)
6516+
-> Seq Scan on public.j2
6517+
Output: j2.id1, j2.id2
6518+
-> Seq Scan on public.j1
6519+
Output: j1.id1, j1.id2
6520+
(7 rows)
6521+
6522+
drop index j1_id2_idx;
65066523
-- validate logic in merge joins which skips mark and restore.
65076524
-- it should only do this if all quals which were used to detect the unique
65086525
-- 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
@@ -2263,6 +2263,15 @@ explain (verbose, costs off)
22632263
select*from j1
22642264
left join j2onj1.id1=j2.id1wherej1.id2=1;
22652265

2266+
createunique indexj1_id2_idxon j1(id2)where id2is not null;
2267+
2268+
-- ensure we don't use a partial unique index as unique proofs
2269+
explain (verbose, costs off)
2270+
select*from j1
2271+
inner join j2onj1.id2=j2.id2;
2272+
2273+
dropindex j1_id2_idx;
2274+
22662275
-- validate logic in merge joins which skips mark and restore.
22672276
-- it should only do this if all quals which were used to detect the unique
22682277
-- are present as join quals, and not plain quals.

0 commit comments

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