Hi, Attached is v1 of a patch fixing selectivity estimates from partial unique indexes. Currently, a partial index key can be treated as unique when predOK was established using join clauses. For example:
CREATE TABLE parent (id int); CREATE TABLE child (parent_id int, code int); -- Code is unique only when parent_id is not NULL. CREATE UNIQUE INDEX ON child (code) WHERE parent_id IS NOT NULL; INSERT INTO parent VALUES (1); -- Insert 1000 rows outside the predicate, and one inside it. INSERT INTO child SELECT NULL, 42 FROM generate_series(1, 1000); INSERT INTO child VALUES (1, 42); ANALYZE parent, child; SET enable_indexscan = off; SET enable_bitmapscan = off; EXPLAIN SELECT * FROM parent p JOIN child c ON c.parent_id = p.id WHERE c.code = 42; An unpatched server estimates one row for the child scan, although code = 42 matches all 1001 rows; the join eliminates the 1000 NULL rows only later. Bug #17975 exposed the same proof-scope problem in relation uniqueness [1]. Commit f6345f03f35 fixed it by rejecting all partial indexes in relation_has_unique_index_for(). That was a simple and safe fix for a correctness bug that needed backpatching. We could do the same here, but base-proven uniqueness is still valid and useful for estimates. The patch therefore adds predOKBase, proved from baserestrictinfo only, rather than rejecting all partial indexes. Simple-column and expression-index estimates use predOKBase. predOK retains exactly the same result: a base-only proof sets both flags; otherwise, the existing join-aware proof is still used. This is the same base-only distinction David proposed for relation uniqueness [2]. The child scan above is then estimated at 1001 rows. That relation-uniqueness change is not part of v1, but I would be happy to follow up if useful. [1] https://postgr.es/m/17975-98a90c156f25c952%40postgresql.org [2] https://postgr.es/m/CAApHDvo4s_1TzzYzd5VKgfrnPpOm18HBT544L%3DxTv0jcVrqFXg%40mail.gmail.com -- Best regards, Chengpeng Yan
v1-0001-Restrict-partial-unique-index-estimates-to-base-q.patch
Description: v1-0001-Restrict-partial-unique-index-estimates-to-base-q.patch
