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