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? Cheers, Jeff
