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

Commit7fcd7ef

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 parenta14e75e commit7fcd7ef

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

35503550
/*
35513551
* If the index is not unique, or not immediately enforced, or if it's
3552-
* a partial index that doesn't match the query, it's useless here.
3552+
* a partial index, it's useless here. We're unable to make use of
3553+
* predOK partial unique indexes due to the fact that
3554+
* check_index_predicates() also makes use of join predicates to
3555+
* determine if the partial index is usable. Here we need proofs that
3556+
* hold true before any joins are evaluated.
35533557
*/
3554-
if (!ind->unique|| !ind->immediate||
3555-
(ind->indpred!=NIL&& !ind->predOK))
3558+
if (!ind->unique|| !ind->immediate||ind->indpred!=NIL)
35563559
continue;
35573560

35583561
/*

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

Lines changed: 4 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -801,18 +801,17 @@ rel_supports_distinctness(PlannerInfo *root, RelOptInfo *rel)
801801
/*
802802
* For a plain relation, we only know how to prove uniqueness by
803803
* reference to unique indexes. Make sure there's at least one
804-
* suitable unique index. It must be immediately enforced, andif
805-
*it's apartial index, it must match the query.(Keep these
806-
*conditions in sync withrelation_has_unique_index_for!)
804+
* suitable unique index. It must be immediately enforced, andnot a
805+
* partial index.(Keep these conditions in sync with
806+
* relation_has_unique_index_for!)
807807
*/
808808
ListCell*lc;
809809

810810
foreach(lc,rel->indexlist)
811811
{
812812
IndexOptInfo*ind= (IndexOptInfo*)lfirst(lc);
813813

814-
if (ind->unique&&ind->immediate&&
815-
(ind->indpred==NIL||ind->predOK))
814+
if (ind->unique&&ind->immediate&&ind->indpred==NIL)
816815
return true;
817816
}
818817
}

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

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

7635+
create unique index j1_id2_idx on j1(id2) where id2 is not null;
7636+
-- ensure we don't use a partial unique index as unique proofs
7637+
explain (verbose, costs off)
7638+
select * from j1
7639+
inner join j2 on j1.id2 = j2.id2;
7640+
QUERY PLAN
7641+
------------------------------------------
7642+
Nested Loop
7643+
Output: j1.id1, j1.id2, j2.id1, j2.id2
7644+
Join Filter: (j2.id2 = j1.id2)
7645+
-> Seq Scan on public.j2
7646+
Output: j2.id1, j2.id2
7647+
-> Seq Scan on public.j1
7648+
Output: j1.id1, j1.id2
7649+
(7 rows)
7650+
7651+
drop index j1_id2_idx;
76357652
-- validate logic in merge joins which skips mark and restore.
76367653
-- it should only do this if all quals which were used to detect the unique
76377654
-- 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
@@ -2757,6 +2757,15 @@ explain (verbose, costs off)
27572757
select*from j1
27582758
left join j2onj1.id1=j2.id1wherej1.id2=1;
27592759

2760+
createunique indexj1_id2_idxon j1(id2)where id2is not null;
2761+
2762+
-- ensure we don't use a partial unique index as unique proofs
2763+
explain (verbose, costs off)
2764+
select*from j1
2765+
inner join j2onj1.id2=j2.id2;
2766+
2767+
dropindex j1_id2_idx;
2768+
27602769
-- validate logic in merge joins which skips mark and restore.
27612770
-- it should only do this if all quals which were used to detect the unique
27622771
-- are present as join quals, and not plain quals.

0 commit comments

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