Re: BUG #11441: Weird (and seems wrong) behavior of partial indexes with order by/limit

Some update now with full reproducible test case (and some surprising
results):

Test case initialization:

drop table if exists test;
create table test as (select g.i as id, (random()*100)::integer as
is_finished from generate_series(1,1000000) as g(i));
create index test_1_key on test(id, is_finished) where is_finished = ANY
(ARRAY[0, 5]);
vacuum analyze test;

Good (but not expected in that case) plan:

explain analyze select * from test where is_finished=0 or is_finished=5
order by id limit 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.24 rows=1 width=8) (actual time=0.052..0.052 rows=1
loops=1)
-> Index Only Scan using test_1_key on test (cost=0.00..4493.05
rows=18921 width=8) (actual time=0.052..0.052 rows=1 loops=1)
Heap Fetches: 1
Total runtime: 0.066 ms
(i'm very surprised than the PostgreSQL managed deduct is_finished = ANY
(ARRAY[0, 5]) from (is_finished=0 or is_finished=5))

Bad plan (techically the same query and even better suitable for the
partial index and should have the same plan but no luck):

explain analyze select * from test where is_finished IN (0,5) order by id
limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=4809.18..4809.19 rows=1 width=8) (actual time=15.410..15.410
rows=1 loops=1)
-> Sort (cost=4809.18..4999.44 rows=19026 width=8) (actual
time=15.408..15.408 rows=1 loops=1)
Sort Key: id
Sort Method: top-N heapsort Memory: 25kB
-> Index Only Scan using test_1_key on test (cost=0.00..4428.66
rows=19026 width=8) (actual time=0.051..12.277 rows=15222 loops=1)
Index Cond: (is_finished = ANY ('{0,5}'::integer[]))
Heap Fetches: 15222
Total runtime: 15.469 ms

--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/ <http://www.postgresql-consulting.com/>

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim(dot)boguk(at)gmail(dot)com
МойКруг: http://mboguk.moikrug.ru/

"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."