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

Commit9e77323

Browse files
committed
Allow indexscans on partial hash indexes with implied quals.
Normally, if a WHERE clause is implied by the predicate of a partialindex, we drop that clause from the set of quals used with the index,since it's redundant to test it if we're scanning that index.However, if it's a hash index (or any !amoptionalkey index), thiscould result in dropping all available quals for the index's firstkey, preventing us from generating an indexscan.It's fair to question the practical usefulness of this case. Sincehash only supports equality quals, the situation could only ariseif the index's predicate is "WHERE indexkey = constant", implyingthat the index contains only one hash value, which would make hasha really poor choice of index type. However, perhaps there areother !amoptionalkey index AMs out there with which such cases aremore plausible.To fix, just don't filter the candidate indexquals this way ifthe index is !amoptionalkey. That's a bit hokey because it mayresult in testing quals we didn't need to test, but to do itmore accurately we'd have to redundantly identify which candidatequals are actually usable with the index, something we don't knowat this early stage of planning. Doesn't seem worth the effort.Reported-by: Sergei Glukhov <s.glukhov@postgrespro.ru>Author: Tom Lane <tgl@sss.pgh.pa.us>Reviewed-by: David Rowley <dgrowleyml@gmail.com>Discussion:https://postgr.es/m/e200bf38-6b45-446a-83fd-48617211feff@postgrespro.ruBackpatch-through: 14
1 parent9a26ff8 commit9e77323

File tree

5 files changed

+44
-0
lines changed

5 files changed

+44
-0
lines changed

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

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3424,6 +3424,16 @@ check_index_predicates(PlannerInfo *root, RelOptInfo *rel)
34243424
if (is_target_rel)
34253425
continue;
34263426

3427+
/*
3428+
* If index is !amoptionalkey, also leave indrestrictinfo as set
3429+
* above. Otherwise we risk removing all quals for the first index
3430+
* key and then not being able to generate an indexscan at all. It
3431+
* would be better to be more selective, but we've not yet identified
3432+
* which if any of the quals match the first index key.
3433+
*/
3434+
if (!index->amoptionalkey)
3435+
continue;
3436+
34273437
/* Else compute indrestrictinfo as the non-implied quals */
34283438
index->indrestrictinfo=NIL;
34293439
foreach(lcr,rel->baserestrictinfo)

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1271,6 +1271,8 @@ CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);
12711271
CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
12721272
CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
12731273
CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=60);
1274+
CREATE INDEX hash_i4_partial_index ON hash_i4_heap USING hash (seqno)
1275+
WHERE seqno = 9999;
12741276
CREATE UNLOGGED TABLE unlogged_hash_table (id int4);
12751277
CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id int4_ops);
12761278
DROP TABLE unlogged_hash_table;

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

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -82,6 +82,25 @@ SELECT * FROM hash_f8_heap
8282
-------+--------
8383
(0 rows)
8484

85+
--
86+
-- partial hash index
87+
--
88+
EXPLAIN (COSTS OFF)
89+
SELECT * FROM hash_i4_heap
90+
WHERE seqno = 9999;
91+
QUERY PLAN
92+
--------------------------------------------------------
93+
Index Scan using hash_i4_partial_index on hash_i4_heap
94+
Index Cond: (seqno = 9999)
95+
(2 rows)
96+
97+
SELECT * FROM hash_i4_heap
98+
WHERE seqno = 9999;
99+
seqno | random
100+
-------+------------
101+
9999 | 1227676208
102+
(1 row)
103+
85104
--
86105
-- hash index
87106
-- grep '^90[^0-9]' hashovfl.data

‎src/test/regress/sql/create_index.sql‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -374,6 +374,9 @@ CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
374374

375375
CREATEINDEXhash_f8_indexON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=60);
376376

377+
CREATEINDEXhash_i4_partial_indexON hash_i4_heap USING hash (seqno)
378+
WHERE seqno=9999;
379+
377380
CREATE UNLOGGED TABLE unlogged_hash_table (id int4);
378381
CREATEINDEXunlogged_hash_indexON unlogged_hash_table USING hash (id int4_ops);
379382
DROPTABLE unlogged_hash_table;

‎src/test/regress/sql/hash_index.sql‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -55,6 +55,16 @@ SELECT * FROM hash_f8_heap
5555
SELECT*FROM hash_f8_heap
5656
WHEREhash_f8_heap.random='88888888'::float8;
5757

58+
--
59+
-- partial hash index
60+
--
61+
EXPLAIN (COSTS OFF)
62+
SELECT*FROM hash_i4_heap
63+
WHERE seqno=9999;
64+
65+
SELECT*FROM hash_i4_heap
66+
WHERE seqno=9999;
67+
5868
--
5969
-- hash index
6070
-- grep '^90[^0-9]' hashovfl.data

0 commit comments

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