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 =3D ANY
(ARRAY[0, 5]);
vacuum analyze test;

Good (but not expected in that case) plan:

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



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 PLA=
N
---------------------------------------------------------------------------=
-------------------------------------------------------------------
 Limit  (cost=3D4809.18..4809.19 rows=3D1 width=3D8) (actual time=3D15.410.=
.15.410
rows=3D1 loops=3D1)
   ->  Sort  (cost=3D4809.18..4999.44 rows=3D19026 width=3D8) (actual
time=3D15.408..15.408 rows=3D1 loops=3D1)
         Sort Key: id
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Index Only Scan using test_1_key on test  (cost=3D0.00..4428.6=
6
rows=3D19026 width=3D8) (actual time=3D0.051..12.277 rows=3D15222 loops=3D1=
)
               Index Cond: (is_finished =3D ANY ('{0,5}'::integer[]))
               Heap Fetches: 15222
 Total runtime: 15.469 ms


--=20
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.boguk@gmail.com
=D0=9C=D0=BE=D0=B9=D0=9A=D1=80=D1=83=D0=B3: 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."