Horton
Menu

Re: Hash-based MCV matching for large IN-lists

Zsolt Parragi <zsolt.parragi@percona.com>

View thread
From: Zsolt Parragi <zsolt.parragi@percona.com>
To: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
Cc: David Geier <geidav.pg@gmail.com>, Chengpeng Yan <chengpeng_yan@outlook.com>, Tatsuya Kawata <kawatatatsuya0913@gmail.com>, "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Date: 2026-03-02T21:37:01Z
Lists: pgsql-hackers 
Hello!

+ if (vardata.isunique && vardata.rel && vardata.rel->tuples >= 1.0)
+ {
+ s2 = 1.0 / vardata.rel->tuples;
+ if (HeapTupleIsValid(vardata.statsTuple))
+ {
+ Form_pg_statistic stats = (Form_pg_statistic) GETSTRUCT(vardata.statsTuple);
+ if (isInequality)
+ s2 = 1.0 - s2 - stats->stanullfrac;
+ }
+ }


Isn't there's a corner case where this if order returns an incorrect
estimate/regression?
See the following test:

CREATE TABLE test AS SELECT generate_series(1, 1000) AS id;
CREATE UNIQUE INDEX ON test(id);
-- no ANALYZE

EXPLAIN SELECT * FROM test WHERE id <> ALL(ARRAY[1, 2, 3]);
-- Actual:   rows=1
-- Expected: rows=997

ANALYZE test;
EXPLAIN SELECT * FROM test WHERE id <> ALL(ARRAY[1, 2, 3]);
-- Correct: rows=997

DROP TABLE test;

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Short-circuit row estimation in NOT IN containing NULL consts