Re: unexpected plan with id = any('{}') condition

Sergei Kornilov <sk@zsrv.org> writes:
> [ unreadable HTML mess ]

For the archives, the test case here looks like

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 select * from test where id = any('{}');

and the question is why you get

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Index Scan using test_status_partial on test  (cost=0.12..4.14 rows=1 width=8)
   Filter: (id = ANY ('{}'::integer[]))
(2 rows)

when that partial index seems unrelated to the query conditions.

The answer is that predicate_implied_by() returns TRUE, because
it can see that the WHERE condition is equivalent to constant FALSE,
and FALSE implies anything.  So the partial index is accepted as
proven by the WHERE condition, and then it looks a trifle cheaper
to scan than the complete index, which gets an estimate like

 Index Scan using test_id on test  (cost=0.42..4.44 rows=1 width=8)
   Index Cond: (id = ANY ('{}'::integer[]))

I don't see anything wrong here.  Maybe one could wish that the
planner reduced the WHERE to constant-FALSE outright, which would
yield an even cheaper plan; but I'm not convinced we want to expend
extra cycles looking for such cases.

            regards, tom lane