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

>
> If my understnding is correct, it repeats scanning the index
> using non-array restrictions for every array element, or every
> possible combination of elements of multiple scalar arrays, so
> the index-order generally won't be preserved in the result
> tuples.
>
> The one obvious exception is the case of the scalar-array
> operation on the first index column. The value in the array is
> sorted before the iterations mentioned above, so the the planner
> can determine it to be ordered *only* for this case.
>
> The result could be ordered if the all restrictions on all index
> columns before scalar-array-op column are equal conditions, but
> the case is judged to be abandoned from the viewpoint of cost and
> modularitly.
>
>
> Therefore, the planner eliminates the sort for the following
> example, even though no meaning in itself.
>
> 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_2_key on test(is_finished, id) where is_finished = ANY
> (ARRAY[0, 5]);
> vacuum analyze test;
>
> explain (costs off) select * from test where is_finished IN (0,5) order by
> is_finished, id limit 1;
>
>                           QUERY PLAN
> --------------------------------------------------------------
>  Limit
>    ->  Index Only Scan using test_2_key on test
>          Index Cond: (is_finished = ANY ('{0,5}'::integer[]))
>
>
>
Hi,

But why index scan working for completely equivalent query with OR
condition than?

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)


"is_finished = ANY ('{0,5}'::integer[])" is equivalent to  " is_finished=0
or is_finished=5"
what's more planner aware about it for sure (or it will not be able use
conditional index with "where is_finished = ANY (ARRAY[0, 5]);" for the OR
query).


Kind Regards,
Maksym