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

maxim(dot)boguk(at)postgresql-consulting(dot)com writes:
> create index qqq_test_2_key on qqq_test(resume_id, is_finished) where
> (is_finished = ANY (ARRAY[0, 5]));

> (postgres(at)[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