From: | Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> |
---|---|
To: | Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> |
Cc: | Maxim Boguk <maxim(dot)boguk(at)postgresql-consulting(dot)com>, pgsql-bugs <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #11441: Weird (and seems wrong) behavior of partial indexes with order by/limit |
Date: | 2014-09-19 10:49:47 |
Message-ID: | CAK-MWwR7hzFTV2K=PrfhmesZKHaMckCXpOFiGokw+uVjaTpx1g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
>
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Kapila | 2014-09-19 11:35:23 | Re: BUG #11350: ALTER SYSTEM is not DDL? |
Previous Message | Kyotaro HORIGUCHI | 2014-09-19 08:15:44 | Re: BUG #11441: Weird (and seems wrong) behavior of partial indexes with order by/limit |