(unnamed)

text/html

Download

<div>Hello</div><div> </div><div>I have such case:</div><div> </div><div><div>create table test (id int not null, status text);</div><div>insert into test select i, 'foo' from generate_series(1,1000000) i;</div><div>update test set status = 'bar' where id &lt;= 10;</div><div>create index test_id on test (id );</div><div>create index test_status_partial on test (status) where status = 'bar';</div><div>analyze test ;</div><div>explain (analyze) select * from test where id = any('{}');</div><div> </div><div>Gives query plan:</div><div><div>                                                        QUERY PLAN                                                        </div><div>--------------------------------------------------------------------------------------------------------------------------</div><div> 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)</div><div>   Filter: (id = ANY ('{}'::integer[]))</div><div>   Rows Removed by Filter: 10</div><div> Planning Time: 0.327 ms</div><div> Execution Time: 0.048 ms</div><div> </div><div><div>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?</div><div> </div><div><div>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)</div><div> </div><div>regards, Sergei</div></div></div></div></div>