Hello
I have such case:
create table test (id int not null, status text);
insert into test select i, 'foo' from generate_series(1,1000000) i;
update test set status = 'bar' where id <= 10;
create index test_id on test (id );
create index test_status_partial on test (status) where status = 'bar';
analyze test ;
explain (analyze) select * from test where id = any('{}');
Gives query plan:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using test_status_partial on test (cost=0.12..4.14 rows=1 width=8) (actual time=0.024..0.025 rows=0 loops=1)
Filter: (id = ANY ('{}'::integer[]))
Rows Removed by Filter: 10
Planning Time: 0.327 ms
Execution Time: 0.048 ms
I don't understand why the planner chose such an unrelated partial index. I expected "One-Time Filter: false" here or use of test_id index. I agree, a strange condition, the application should avoid such condition, but why use such an index?
Initially was spotted on 13.3 production system (slow query due too much Rows Removed by Filter), then I checked this behaviour on 14.0 and fresh HEAD (db9f287711ac49d9799f93f664d6d101ff8f5891)
regards, Sergei
