On Fri, Sep 4, 2015 at 4:28 AM, Anastasia Lubennikova < a.lubennik...@postgrespro.ru> wrote:
> > > 25.08.2015 20:19, Jeff Janes пишет: > > On Fri, Jul 10, 2015 at 11:29 AM, Tomas Vondra < > 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. > Yes, of course. Thanks for the explanation, it is obvious now that you have explained it. I kept slipping into thinking that the predicate-dependent variables are included in the index but only when the predicate is met, but that isn't the case. How can we evaluate Tom's performance concerns? I tried turning log_planner_stats on and using the regression test as a load generator, but I don't think that that is very demanding of a test. Thanks, Jeff
