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

On Sat, Sep 20, 2014 at 12:05 AM, David G Johnston <
david.g.johnston@gmail.com> wrote:

> Kyotaro HORIGUCHI-2 wrote
> > Hello, I think this is a behavior as desinged.
>
> It may not technically be a bug but it definitely could use some TLC from
> Tom Lane.
>
> I'm curious, but too busy to check myself, how these three queries would
> perform with the index columns placed in reverse order.  Then create a
> summary post with the 3 queries x 2 indexes, and the plan summaries (not
> the
> entire explain) would let someone quickly see the end result without
> sifting
> through 3 posts and lots of explanation.
>
> Also, can you test on 9.3 and/or 9.4?  Someone knowledgable may know
> outright but otherwise this may have been recently improved and, because it
> is not really a bug, not back-patched.  Release notes should indicate this
> but testing is more accurate.
>

Hi David,
full test sequence as you requested:

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;
--q1
explain analyze select * from test where is_finished=0 or is_finished=5
order by id limit 1;
--q2
explain analyze select * from test where is_finished=0 or is_finished=5
order by is_finished,id limit 1;
--q3
explain analyze select * from test where is_finished IN (0,5) order by id
limit 1;
--q4
explain analyze select * from test where is_finished IN (0,5) order by
is_finished,id limit 1;

drop index test_1_key;
create index test_2_key on test(is_finished, id) where is_finished = ANY
(ARRAY[0, 5]);
vacuum analyze test;
--q1
explain analyze select * from test where is_finished=0 or is_finished=5
order by id limit 1;
--q2
explain analyze select * from test where is_finished=0 or is_finished=5
order by is_finished,id limit 1;
--q3
explain analyze select * from test where is_finished IN (0,5) order by id
limit 1;
--q4
explain analyze select * from test where is_finished IN (0,5) order by
is_finished,id limit 1;


The tests had been performed on versions 9.2.9, 9.3.5 and 9.4beta2, all
produced the same plans for every version.

Result table:
             test_1_key                test_2_key
q1           IOS+limit                   IOS+sort/limit
q2           IOS+sort/limit            IOS+limit
q3           IOS+sort/limit            IOS+sort/limit
q4           IOS+sort/limit            IOS+limit

Everything work as expected except the problem case test_1_key + q3:
(
create index test_1_key on test(id, is_finished) where is_finished = ANY
(ARRAY[0, 5]);
and
select * from test where is_finished IN (0,5) order by id limit 1;
).

PS: Results of q1+test_1_key verified and found to be correct so there are
no problem in using IOS+limit plan for query.

Kind Regards,
Maksym