On Sun, Sep 21, 2014 at 6:21 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> maxim.boguk@postgresql-consulting.com writes:
> > create index qqq_test_2_key on qqq_test(resume_id, is_finished) where
> > (is_finished = ANY (ARRAY[0, 5]));
>
> > (postgres@[local]:5432)=# explain analyze select * from qqq_test where
> > is_finished = ANY (ARRAY[0, 5]) order by resume_id limit 1;
> > [ doesn't use the index ]
>
> The reason why not is that it starts by generating a path that uses the
> is_finished = ANY() clause as an indexqual, and decides that such a
> path will not produce data that's ordered by resume_id. Which is correct:
> it won't, because there will first be a scan to find the is_finished = 0
> data and then another scan to find the is_finished = 5 data.
>
> Now in point of fact, we don't need to use that clause as an indexqual
> because it's implied by the index predicate. However, indxpath.c has
> never tested for such cases and I'm a bit hesitant to add the cycles that
> would be required to do so. This sort of case doesn't really seem
> compelling enough to justify slowing down planning for *every* query on
> tables having partial indexes, which would be the likely outcome. If it
> were compelling, we'd have heard about it before ...
>
> regards, tom lane
>
Hi Tom,
I'm very sorry but are you sure that your analysis fully correct?
I see two potential problem here:
1)I always thought that the index on (a,b) during index scan will provide
results ordered by 'a' in any case and independently of which additional
conditions on the 'b' we have in query. I made a mistake on that assumption?
So index scan using 'qqq_test(resume_id, is_finished) where (is_finished =
ANY (ARRAY[0, 5]))' index will be ordered by resume_id anyway?
2)How to planner found the same index pretty suitable for the equivalent
query:
select * from qqq_test where is_finished = 0 OR is_finished=5 order by
resume_id limit 1;
?
As I see the planner able to substitute 'is_finished = 0 OR is_finished=5'
with equivalent 'is_finished = ANY (ARRAY[0, 5])' (without such
substitution the partial index will be not used). So i assume the same
logic should be applicable, but PostgreSQL able use index only scan for
that case:
explain analyze select * from qqq_test where is_finished = 0 OR
is_finished=5 order by resume_id limit 1;;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.90 rows=1 width=12) (actual time=0.016..0.016 rows=0
loops=1)
-> Index Only Scan using qqq_test_2_key on qqq_test (cost=0.00..1.81
rows=2 width=12) (actual time=0.016..0.016 rows=0 loops=1)
Heap Fetches: 0
Total runtime: 0.032 ms
Kind Regards,
Maksym
