Bad estimate with partial index - Mailing list pgsql-hackers

Hi list,

I have a case where Postgres chooses the wrong index and I'm not sure what
to do about it:

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=f356fd56a920ea8a93c192f5a8c16b
1c

Setup:

CREATE TABLE t (
    filename int,
    cropped bool not null default false,
    resized bool not null default false,
    create_date date not null default '1970-01-01'
);

INSERT INTO t
SELECT generate_series(1, 1000000);

UPDATE t SET cropped = true, resized = true
WHERE filename IN (SELECT filename FROM t ORDER BY random() LIMIT 900000);
UPDATE t SET resized = false
WHERE filename IN (SELECT filename FROM t WHERE cropped = true ORDER BY
random() LIMIT 1000);

VACUUM FULL t;
ANALYZE t;

Data now looks like this:

SELECT cropped, resized, count(*)
FROM t
GROUP BY 1,2;

I create two indexes:

CREATE INDEX idx_resized ON t(resized) WHERE NOT resized;
CREATE INDEX specific ON t(cropped,resized) WHERE cropped AND NOT resized;

And then run my query:

EXPLAIN ANALYZE
    SELECT count(*) FROM t WHERE cropped AND NOT resized AND create_date <
CURRENT_DATE;

Aggregate  (cost=4001.25..4001.26 rows=1 width=8) (actual
time=478.557..478.558 rows=1 loops=1)
  ->  Index Scan using idx_resized on t  (cost=0.29..3777.71 rows=89415
width=0) (actual time=478.177..478.480 rows=1000 loops=1)
        Filter: (cropped AND (create_date < CURRENT_DATE))
        Rows Removed by Filter: 100000

It takes 478 ms on dbfiddle.uk (on my machine it's faster but the difference
is still visible).

Now I delete an index:

DROP INDEX idx_resized;

and run the same query again and I get a much better plan:

Aggregate  (cost=11876.27..11876.28 rows=1 width=8) (actual
time=0.315..0.316 rows=1 loops=1)
  ->  Bitmap Heap Scan on t  (cost=35.50..11652.73 rows=89415 width=0)
(actual time=0.054..0.250 rows=1000 loops=1)
        Recheck Cond: (cropped AND (NOT resized))
        Filter: (create_date < CURRENT_DATE)
        Heap Blocks: exact=6
        ->  Bitmap Index Scan on specific  (cost=0.00..13.15 rows=89415
width=0) (actual time=0.040..0.040 rows=1000 loops=1)

which uses the index specific and completes in less than a ms on both
dbfiddle.uk and my machine.

Additional mystery - when I set the values not with an UPDATE but with a
DEFAULT, then the correct index is chosen. What is going on?
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=dc7d8aea14e90f08ab6537a855f34d
8c

Regards,
André