public inbox for [email protected]  

help / color / mirror / Atom feed

From: Zsolt Parragi <[email protected]>
To: Ilia Evdokimov <[email protected]>
Cc: David Geier <[email protected]>
Cc: Chengpeng Yan <[email protected]>
Cc: Tatsuya Kawata <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Hash-based MCV matching for large IN-lists
Date: Mon, 2 Mar 2026 21:37:01 +0000
Message-ID: <CAN4CZFO3Y25iCqqP_zS1ipgbrBXvAkxeLK2hPuamddyW9ouAzQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAHza6qdAH99C0xa27YDTixiNVFa99j90QYquUPAxL0JwahmggA@mail.gmail.com>
	<[email protected]>
	<CAN4CZFPb+iqQkby4AGvZSvL2OkwQ+S9CVvmJiQ=u6cckWPetqA@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>

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;

view thread (8+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Hash-based MCV matching for large IN-lists
  In-Reply-To: <CAN4CZFO3Y25iCqqP_zS1ipgbrBXvAkxeLK2hPuamddyW9ouAzQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox