Re: unexpected plan with id = any('{}') condition
Sergei Kornilov 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], so there was some misconfiguration somewhere. regards, tom lane [1] https://www.postgresql.org/message-id/1910161636627509%40mail.yandex.ru
Re: unexpected plan with id = any('{}') condition
Sergei Kornilov 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,100) 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
unexpected plan with id = any('{}') condition
Hello I have such case: create table test (id int not null, status text);insert into test select i, 'foo' from generate_series(1,100) 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
