unexpected plan with id = any('{}') condition
<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 <= 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>
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
Hello
Thank you for the explanation!
unreadable HTML mess
ouch, sorry. "Nobody uses plain text mail, we dropped this thing in the interface” said yandex team. (I know that some members of the Yandex team read mailing lists, could you ping your colleagues?)
but I'm not convinced we want to expend
extra cycles looking for such cases.
Agree. This is an application bug and needs to be fixed there. There is no point in slowing down all queries for the sake of improving an inherently erroneous query condition.
regards, Sergei
Sergei Kornilov <sk@zsrv.org> writes:
unreadable HTML mess
ouch, sorry. "Nobody uses plain text mail, we dropped this thing in the interface” said yandex team.
I'll confess to being a troglodyte, but it's unreadable in the archives
too [1]/messages/by-id/1910161636627509@mail.yandex.ru, so there was some misconfiguration somewhere.
regards, tom lane