Hi pgsql-hackers, clauselist_selectivity() treats exact duplicates of the same qual as independent and multiplies their selectivities together, so each repetition of an identical IS NULL test compounds the underestimate. With a 50%-NULL column (observed on 17.x, reproduced on 18.4):
CREATE TABLE records AS
SELECT i::text AS id,
CASE WHEN i % 2 = 1 THEN now() END AS deleted_at
FROM generate_series(1, 100000) i;
ANALYZE records;
EXPLAIN SELECT * FROM records WHERE deleted_at IS NULL;
-- Seq Scan on records (rows=~50090)
EXPLAIN SELECT * FROM records
WHERE deleted_at IS NULL AND deleted_at IS NULL;
-- rows=~25090
-- three copies: rows=~12568; four copies: rows=~6295
All of these match exactly 50,000 rows; each repeated clause just
halves the estimate again.
We hit this in production with machine-generated queries that emitted
the same soft-delete condition more than once. In the worst case the
estimate dropped from ~150k rows to ~12, and deduplicating the clauses
at generation time flipped some plans, taking queries from ~50 seconds
to under 100 ms.
I've found some sparse conversation around similar issues in the past
[1]. However,
I think this falls into a slightly different category of similarity checks.
I've added a patch that fixes the observed behavior and tested the
patched version
against the cases that I created in my reproduction repo [2].
In the spirit of full disclosure I want to mention that
I used Anthropic's Fable LLM to generate the patch and reproduction case repo.
However, I have read the code, compiled and tested the patch applied to
master and observed the results against my failure cases and
ensured that we did not trip regression tests.
Thanks for reading,
Logan Saso
[1]
https://www.postgresql.org/message-id/flat/[email protected]#d40a851c1a1b2f381180219ffceaf73b
[2] https://github.com/loganintech/pg-isnull-rowestimate-bug-repro/
v1-0001-Ignore-duplicate-IS-NOT-NULL-clauses-in-clauselis.patch
Description: Binary data
