Re: PATCH: index-only scans with partial indexes - Mailing list pgsql-hackers

From Jeff Janes
Subject Re: PATCH: index-only scans with partial indexes
Date
Msg-id CAMkU=1w_Vvtw7mHN4e3HcFcrik0Hsr89DUoHFZU90Oid921Few@mail.gmail.com
Whole thread Raw
In response to PATCH: index-only scans with partial indexes  (Tomas Vondra <tomas.vondra@2ndquadrant.com>)
Responses Re: PATCH: index-only scans with partial indexes  (Anastasia Lubennikova <a.lubennikova@postgrespro.ru>)
List pgsql-hackers
On Fri, Jul 10, 2015 at 11:29 AM, Tomas Vondra <tomas.vondra@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

pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: Error message with plpgsql CONTINUE
Next
From: Josh Berkus
Date:
Subject: Re: Planned release for PostgreSQL 9.5