How to make Postgres use the correct index here?
1
vote
2
answers
163
views
Here's a test setup:
I create some partial indexes for other queries and one index specifically for the query below:
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;
[Fiddle](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=f356fd56a920ea8a93c192f5a8c16b1c)
Data now looks like this:
SELECT cropped, resized, count(*)
FROM t
GROUP BY 1,2;

CREATE INDEX idx_cropped ON t(cropped) WHERE NOT cropped;
CREATE INDEX idx_resized ON t(resized) WHERE NOT resized;
CREATE INDEX specific ON t(cropped,resized) WHERE cropped AND NOT resized;
Now I run my query:
EXPLAIN ANALYZE
SELECT count(*) FROM t WHERE cropped AND NOT resized AND create_date 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 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 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.
-----
Edit: 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? [Fiddle](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=dc7d8aea14e90f08ab6537a855f34d8c)
Asked by AndreKR
(607 rep)
Apr 18, 2022, 01:20 AM
Last activity: Jul 6, 2025, 04:06 PM
Last activity: Jul 6, 2025, 04:06 PM