Re: PATCH: index-only scans with partial indexes

25.08.2015 20:19, Jeff Janes пишет:
> On Fri, Jul 10, 2015 at 11:29 AM, Tomas Vondra
> <tomas(dot)vondra(at)2ndquadrant(dot)com <mailto: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?
>

In this example it doesn't use IndexOnlyScan correctly. If I understand
partial indexes right, if index predicate and search clause are not
equal, index scan must recheck values when it's fetching them.
'tidx_partial' in example above has no information about 'a' attribute,
beside the index->indpred, so it is impossible to recheck qual without
referencing to table.

In example:
create index tidx_partial on t(a) where a > 1000 and a < 2000;
explain analyze select sum(a) from t where a > 1000 and a < 1999;
it can use IndexOnlyScan.

--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company