Re: PATCH: index-only scans with partial indexes

From: Anastasia Lubennikova <a(dot)lubennikova(at)postgrespro(dot)ru>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: PATCH: index-only scans with partial indexes
Date: 2015-09-04 11:28:29
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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:
The Russian Postgres Company

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Thom Brown 2015-09-04 12:04:53 Truncating/vacuuming relations on full tablespaces
Previous Message Etsuro Fujita 2015-09-04 10:50:46 Re: Foreign join pushdown vs EvalPlanQual