BUG #11441: Weird (and seems wrong) behavior of partial indexes with order by/limit - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      11441
Logged by:          Maksym Boguk
Email address:      maxim.boguk@postgresql-consulting.com
PostgreSQL version: 9.2.9
Operating system:   Linux
Description:

Today I found very strange behavior of partial indexes with correlating
order by/limit.

Simple test case look like:

(postgres@[local]:5432)=# \d qqq_test
      Table "public.qqq_test"
   Column    |  Type   | Modifiers
-------------+---------+-----------
 resume_id   | integer |
 is_finished | integer |

10M rows, vacuumed and analyzed

1.Initial query (no problem found):
select * from qqq_test where is_finished=0 order by resume_id limit 1;
with index:
create index qqq_test_1_key on qqq_test(resume_id, is_finished) where
is_finished=0;
there are no issue:
(postgres@[local]:5432)=# explain analyze select * from qqq_test where
is_finished=0 order by resume_id limit 1;
                                                                 QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.32 rows=1 width=8) (actual time=0.062..0.062 rows=1
loops=1)
   ->  Index Only Scan using qqq_test_1_key on qqq_test
(cost=0.00..44145.36 rows=138808 width=8) (actual time=0.062..0.062 rows=1
loops=1)
         Index Cond: (is_finished = 0)
 Total runtime: 0.035 ms
(index only scan as expected).

2.Now a bit more complicated case:
select * from qqq_test where is_finished = ANY (ARRAY[0, 5]) order by
resume_id limit 1;
With index:
create index qqq_test_3_key on qqq_test(resume_id) where (is_finished = ANY
(ARRAY[0, 5]));
also no issue:
(postgres@[local]:5432)=# explain analyze select * from qqq_test where
is_finished = ANY (ARRAY[0, 5]) order by resume_id limit 1;
                                                               QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.31 rows=1 width=8) (actual time=0.035..0.035 rows=1
loops=1)
   ->  Index Scan using qqq_test_3_key on qqq_test  (cost=0.00..54166.69
rows=173510 width=8) (actual time=0.034..0.034 rows=1 loops=1)
 Total runtime: 0.049 ms
(index scan as expected).

3.Now it would be nice to have index only scan for the second query (lets
add is_finished to index description to let IOS be used), and there problem
begin:
drop index qqq_test_3_key;
create index qqq_test_2_key on qqq_test(resume_id, is_finished) where
(is_finished = ANY (ARRAY[0, 5]));
And now oops:
(postgres@[local]:5432)=# explain analyze select * from qqq_test where
is_finished = ANY (ARRAY[0, 5]) order by resume_id limit 1;
                                                                     QUERY
PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=38740.23..38740.24 rows=1 width=8) (actual time=54.251..54.251
rows=1 loops=1)
   ->  Sort  (cost=38740.23..40359.65 rows=161942 width=8) (actual
time=54.251..54.251 rows=1 loops=1)
         Sort Key: resume_id
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Index Only Scan using qqq_test_2_key on qqq_test
(cost=0.00..35501.39 rows=161942 width=8) (actual time=0.030..33.449
rows=145278 loops=1)
               Index Cond: (is_finished = ANY ('{0,5}'::integer[]))
               Heap Fetches: 0
 Total runtime: 54.282 ms


Is there any reason why index scan/index only scan could not be used for
this query instead of slow sort+limit?
It's bug or planner/executor limitation (set enable_sort to 0 - have no
effect)?
Query/index pair looks like pretty suitable for simple IOS without
sort+limit.

Now I cannot think any way to perform such query using IOS because with no
is_finished column in index there will be no IOS, and if I add is_finished
to the index there no index scan but slow order by+limit plan.

Kind Regards,
Maksym