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

Commit06286f8

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 parentd5300bc commit06286f8

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

35753575
/*
35763576
* If the index is not unique, or not immediately enforced, or if it's
3577-
* a partial index that doesn't match the query, it's useless here.
3577+
* a partial index, it's useless here. We're unable to make use of
3578+
* predOK partial unique indexes due to the fact that
3579+
* check_index_predicates() also makes use of join predicates to
3580+
* determine if the partial index is usable. Here we need proofs that
3581+
* hold true before any joins are evaluated.
35783582
*/
3579-
if (!ind->unique|| !ind->immediate||
3580-
(ind->indpred!=NIL&& !ind->predOK))
3583+
if (!ind->unique|| !ind->immediate||ind->indpred!=NIL)
35813584
continue;
35823585

35833586
/*

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

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

604604
foreach(lc,rel->indexlist)
605605
{
606606
IndexOptInfo*ind= (IndexOptInfo*)lfirst(lc);
607607

608-
if (ind->unique&&ind->immediate&&
609-
(ind->indpred==NIL||ind->predOK))
608+
if (ind->unique&&ind->immediate&&ind->indpred==NIL)
610609
return true;
611610
}
612611
}

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

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

6349+
create unique index j1_id2_idx on j1(id2) where id2 is not null;
6350+
-- ensure we don't use a partial unique index as unique proofs
6351+
explain (verbose, costs off)
6352+
select * from j1
6353+
inner join j2 on j1.id2 = j2.id2;
6354+
QUERY PLAN
6355+
------------------------------------------
6356+
Nested Loop
6357+
Output: j1.id1, j1.id2, j2.id1, j2.id2
6358+
Join Filter: (j1.id2 = j2.id2)
6359+
-> Seq Scan on public.j2
6360+
Output: j2.id1, j2.id2
6361+
-> Seq Scan on public.j1
6362+
Output: j1.id1, j1.id2
6363+
(7 rows)
6364+
6365+
drop index j1_id2_idx;
63496366
-- validate logic in merge joins which skips mark and restore.
63506367
-- it should only do this if all quals which were used to detect the unique
63516368
-- 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
@@ -2204,6 +2204,15 @@ explain (verbose, costs off)
22042204
select*from j1
22052205
left join j2onj1.id1=j2.id1wherej1.id2=1;
22062206

2207+
createunique indexj1_id2_idxon j1(id2)where id2is not null;
2208+
2209+
-- ensure we don't use a partial unique index as unique proofs
2210+
explain (verbose, costs off)
2211+
select*from j1
2212+
inner join j2onj1.id2=j2.id2;
2213+
2214+
dropindex j1_id2_idx;
2215+
22072216
-- validate logic in merge joins which skips mark and restore.
22082217
-- it should only do this if all quals which were used to detect the unique
22092218
-- are present as join quals, and not plain quals.

0 commit comments

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