Re: PATCH: index-only scans with partial indexes

On Fri, Jul 10, 2015 at 11:29 AM, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com
> wrote:

> Hi,
>
> currently partial indexes end up not using index only scans in most cases,
> because check_index_only() is overly conservative, as explained in this
> comment:
>
> * XXX this is overly conservative for partial indexes, since we will
> * consider attributes involved in the index predicate as required even
> * though the predicate won't need to be checked at runtime. (The same
> * is true for attributes used only in index quals, if we are certain
> * that the index is not lossy.) However, it would be quite expensive
> * to determine that accurately at this point, so for now we take the
> * easy way out.
>
> In other words, unless you include columns from the index predicate to the
> index, the planner will decide index only scans are not possible. Which is
> a bit unfortunate, because those columns are not needed at runtime, and
> will only increase the index size (and the main benefit of partial indexes
> is size reduction).
>
> The attached patch fixes this by only considering clauses that are not
> implied by the index predicate. The effect is simple:
>
> create table t as select i as a, i as b from
> generate_series(1,10000000) s(i);
>
> create index tidx_partial on t(b) where a > 1000 and a < 2000;
>
> vacuum freeze t;
> analyze t;
>
> explain analyze select count(b) from t where a > 1000 and a < 2000;
>

However, "explain analyze select sum(b) from t where a > 1000 and a <
1999;" still doesn't use the index only
scan. Isn't that also implied by the predicate?

Cheers,

Jeff