Re: Hash-based MCV matching for large IN-lists
Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
From: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
To: Zsolt Parragi <zsolt.parragi@percona.com>,
David Geier <geidav.pg@gmail.com>, Chengpeng Yan
<chengpeng_yan@outlook.com>, Tatsuya Kawata <kawatatatsuya0913@gmail.com>
Cc: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Date: 2026-03-02T09:35:15Z
Lists: pgsql-hackers
Attachments
- v7-0003-Use-hash-based-MCV-matching-for-ScalarArrayOpExpr.patch (text/x-patch) patch v7-0003
- v7-0002-Use-O-1-selectivity-formula-for-eqsel-neqsel-IN-A.patch (text/x-patch) patch v7-0002
- v7-0001-Reduce-planning-time-for-large-NOT-IN-lists-conta.patch (text/x-patch) patch v7-0001
I've addressed the previously mentioned issues in v7 patches.
I also retested the hash-based MCV path using bytea as the data type.
```
CREATE TABLE t (val bytea);
INSERT INTO t SELECT int4send(i) FROM generate_series(1, 10000) AS i,
generate_series(1, 50);
ALTER TABLE t ALTER COLUMN val SET STATISTICS 10000;
ANALYZE t;
SELECT string_agg(format('int4send(%s)', v), ',') FROM
generate_series(1, 10000) AS gs(v) \gset
EXPLAIN (SUMMARY) SELECT * FROM t WHERE val =
ANY(ARRAY[:string_agg]::bytea[]);
```
Planning Time Speedup
default_statistics_target | Before (ms) | After (ms) | Speedup (x)
--------------------------------------------------------------------
100 | 0.984 | 0.697 | 1.41
500 | 1.260 | 0.984 | 1.28
1000 | 4.183 | 1.825 | 2.29
2500 | 64.715 | 1.298 | 49.86
5000 | 251.619 | 4.751 | 52.96
7500 | 562.775 | 2.895 | 194.40
10000 | 998.330 | 3.561 | 280.36
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Short-circuit row estimation in NOT IN containing NULL consts
- c95cd2991f1e 19 (unreleased) cited