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(dot)g(dot)johnston(at)gmail(dot)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