Content-Type: multipart/mixed; boundary="------------0wNHHiEuAvEi07m8vSyammKU"
Message-ID: <e200bf38-6b45-446a-83fd-48617211feff@postgrespro.ru>
Date: Mon, 24 Nov 2025 11:32:18 +0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-US
To: pgsql-hackers@lists.postgresql.org
From: Sergei Glukhov <s.glukhov@postgrespro.ru>
Subject: Partial hash index is not used for implied qual.
Archived-At: 
 <https://www.postgresql.org/message-id/e200bf38-6b45-446a-83fd-48617211feff%40postgrespro.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------0wNHHiEuAvEi07m8vSyammKU
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hi!

Partial hash index is not used if qual is an implied qual
since this qual is not added to indrestrictinfo and we cannot
get the keys needed to make hash index scan possible.
Suggested fix is to add implied qual for the indexes
which requires the presence of a key to scan the index.

How to repeat:

CREATE TABLE hash_partial(x) AS
        SELECT x as y from generate_series(1, 1000) as x;
ANALYZE hash_partial;
CREATE INDEX partial_idx ON hash_partial USING hash(x) WHERE x = 1;
EXPLAIN (COSTS OFF) SELECT x FROM hash_partial WHERE x = 1;
...
          QUERY PLAN
--------------------------
  Seq Scan on hash_partial
    Filter: (x = 1)
  ...

  Regards,
  Sergei Glukhov


--------------0wNHHiEuAvEi07m8vSyammKU
Content-Type: text/x-patch; charset=UTF-8;
 name="partial_hash_index_with_implied_qual.patch"
Content-Disposition: attachment;
 filename="partial_hash_index_with_implied_qual.patch"
Content-Transfer-Encoding: base64

Y29tbWl0IDg5ZThmNWRiMDhiN2NmMDQ1Yjc0M2I3ZDUyYjBiMzc1NGFmZmY0MDUKQXV0aG9y
OiBTZXJnZWkgR2x1a2hvdiA8cy5nbHVraG92QHBvc3RncmVzcHJvLnJ1PgpEYXRlOiAgIE1v
biBOb3YgMjQgMTE6MTI6NDYgMjAyNSArMDQwMAoKICAgIFBhcnRpYWwgaGFzaCBpbmRleCBp
cyBub3QgdXNlZCBmb3IgaW1wbGllZCBxdWFscy4KICAgIAogICAgUGFydGlhbCBoYXNoIGlu
ZGV4IGlzIG5vdCB1c2VkIGlmIHF1YWwgaXMgYW4gaW1wbGllZCBxdWFsCiAgICBzaW5jZSB0
aGlzIHF1YWwgaXMgbm90IGFkZGVkIHRvIGluZHJlc3RyaWN0aW5mbyBhbmQgd2UgY2Fubm90
CiAgICBnZXQgdGhlIGtleXMgbmVlZGVkIHRvIG1ha2UgaGFzaCBpbmRleCBzY2FuIHBvc3Np
YmxlLgogICAgU3VnZ2VzdGVkIGZpeCBpcyB0byBhZGQgaW1wbGllZCBxdWFsIHRvIGluZHJl
c3RyaWN0aW5mbwogICAgZm9yIHRoZSBpbmRleGVzIHRoYXQgcmVxdWlyZSBhIGtleSB0byBz
Y2FuIHRoZSBpbmRleC4KCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGF0
aC9pbmR4cGF0aC5jIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgvaW5keHBhdGguYwpp
bmRleCBjNjJlM2Y4NzcyNC4uMDQ0NWJiZjg3NzUgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5k
L29wdGltaXplci9wYXRoL2luZHhwYXRoLmMKKysrIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVy
L3BhdGgvaW5keHBhdGguYwpAQCAtNDA1OSw4ICs0MDU5LDEwIEBAIGNoZWNrX2luZGV4X3By
ZWRpY2F0ZXMoUGxhbm5lckluZm8gKnJvb3QsIFJlbE9wdEluZm8gKnJlbCkKIAogCQkJLyog
cHJlZGljYXRlX2ltcGxpZWRfYnkoKSBhc3N1bWVzIGZpcnN0IGFyZyBpcyBpbW11dGFibGUg
Ki8KIAkJCWlmIChjb250YWluX211dGFibGVfZnVuY3Rpb25zKChOb2RlICopIHJpbmZvLT5j
bGF1c2UpIHx8Ci0JCQkJIXByZWRpY2F0ZV9pbXBsaWVkX2J5KGxpc3RfbWFrZTEocmluZm8t
PmNsYXVzZSksCi0JCQkJCQkJCQkgIGluZGV4LT5pbmRwcmVkLCBmYWxzZSkpCisJCQkJKGlu
ZGV4LT5hbW9wdGlvbmFsa2V5ICYmCisJCQkJICFwcmVkaWNhdGVfaW1wbGllZF9ieShsaXN0
X21ha2UxKHJpbmZvLT5jbGF1c2UpLAorCQkJCQkJCQkJICAgaW5kZXgtPmluZHByZWQsIGZh
bHNlKSkgfHwKKwkJCQkhaW5kZXgtPmFtb3B0aW9uYWxrZXkpCiAJCQkJaW5kZXgtPmluZHJl
c3RyaWN0aW5mbyA9IGxhcHBlbmQoaW5kZXgtPmluZHJlc3RyaWN0aW5mbywgcmluZm8pOwog
CQl9CiAJfQpkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9oYXNoX2lu
ZGV4Lm91dCBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvaGFzaF9pbmRleC5vdXQKaW5k
ZXggMGQ0YmRiMmFkZWYuLjUyMTM3NDJiMTk5IDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdy
ZXNzL2V4cGVjdGVkL2hhc2hfaW5kZXgub3V0CisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhw
ZWN0ZWQvaGFzaF9pbmRleC5vdXQKQEAgLTMzMywzICszMzMsMjIgQEAgQ1JFQVRFIElOREVY
IGhhc2hfZjhfaW5kZXgyIE9OIGhhc2hfZjhfaGVhcCBVU0lORyBoYXNoIChyYW5kb20gZmxv
YXQ4X29wcykKIAlXSVRIIChmaWxsZmFjdG9yPTEwMSk7CiBFUlJPUjogIHZhbHVlIDEwMSBv
dXQgb2YgYm91bmRzIGZvciBvcHRpb24gImZpbGxmYWN0b3IiCiBERVRBSUw6ICBWYWxpZCB2
YWx1ZXMgYXJlIGJldHdlZW4gIjEwIiBhbmQgIjEwMCIuCistLSBQYXJ0aWFsIGhhc2ggaW5k
ZXggd2l0aCBhbiBpbXBsaWVkIHF1YWwuCitDUkVBVEUgVEFCTEUgaGFzaF9wYXJ0aWFsKHgp
IEFTCisJU0VMRUNUIHggYXMgeSBmcm9tIGdlbmVyYXRlX3NlcmllcygxLCAxMDAwKSBhcyB4
OworQU5BTFlaRSBoYXNoX3BhcnRpYWw7CitDUkVBVEUgSU5ERVggcGFydGlhbF9pZHggT04g
aGFzaF9wYXJ0aWFsIFVTSU5HIGhhc2goeCkgV0hFUkUgeCA9IDE7CitFWFBMQUlOIChDT1NU
UyBPRkYpIFNFTEVDVCB4IEZST00gaGFzaF9wYXJ0aWFsIFdIRVJFIHggPSAxOworICAgICAg
ICAgICAgICAgICAgUVVFUlkgUExBTiAgICAgICAgICAgICAgICAgIAorLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQorIEluZGV4IFNjYW4gdXNpbmcg
cGFydGlhbF9pZHggb24gaGFzaF9wYXJ0aWFsCisgICBJbmRleCBDb25kOiAoeCA9IDEpCiso
MiByb3dzKQorCitTRUxFQ1QgeCBGUk9NIGhhc2hfcGFydGlhbCBXSEVSRSB4ID0gMTsKKyB4
IAorLS0tCisgMQorKDEgcm93KQorCitEUk9QIFRBQkxFIGhhc2hfcGFydGlhbDsKZGlmZiAt
LWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2hhc2hfaW5kZXguc3FsIGIvc3JjL3Rlc3Qv
cmVncmVzcy9zcWwvaGFzaF9pbmRleC5zcWwKaW5kZXggMjE5ZGE4Mjk4MTYuLmY0ZjFkMTk1
MjE5IDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNzL3NxbC9oYXNoX2luZGV4LnNxbAor
KysgYi9zcmMvdGVzdC9yZWdyZXNzL3NxbC9oYXNoX2luZGV4LnNxbApAQCAtMzIxLDMgKzMy
MSwxMyBAQCBDUkVBVEUgSU5ERVggaGFzaF9mOF9pbmRleDIgT04gaGFzaF9mOF9oZWFwIFVT
SU5HIGhhc2ggKHJhbmRvbSBmbG9hdDhfb3BzKQogCVdJVEggKGZpbGxmYWN0b3I9OSk7CiBD
UkVBVEUgSU5ERVggaGFzaF9mOF9pbmRleDIgT04gaGFzaF9mOF9oZWFwIFVTSU5HIGhhc2gg
KHJhbmRvbSBmbG9hdDhfb3BzKQogCVdJVEggKGZpbGxmYWN0b3I9MTAxKTsKKworLS0gUGFy
dGlhbCBoYXNoIGluZGV4IHdpdGggYW4gaW1wbGllZCBxdWFsLgorQ1JFQVRFIFRBQkxFIGhh
c2hfcGFydGlhbCh4KSBBUworCVNFTEVDVCB4IGFzIHkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMo
MSwgMTAwMCkgYXMgeDsKK0FOQUxZWkUgaGFzaF9wYXJ0aWFsOworQ1JFQVRFIElOREVYIHBh
cnRpYWxfaWR4IE9OIGhhc2hfcGFydGlhbCBVU0lORyBoYXNoKHgpIFdIRVJFIHggPSAxOwor
RVhQTEFJTiAoQ09TVFMgT0ZGKSBTRUxFQ1QgeCBGUk9NIGhhc2hfcGFydGlhbCBXSEVSRSB4
ID0gMTsKK1NFTEVDVCB4IEZST00gaGFzaF9wYXJ0aWFsIFdIRVJFIHggPSAxOworCitEUk9Q
IFRBQkxFIGhhc2hfcGFydGlhbDsK

--------------0wNHHiEuAvEi07m8vSyammKU--