Alexey Bashtanov <bashtanov@imap.cc> writes:
>>> create table t as select a, a % 2 b, a % 2 c from generate_series(1,
> 1000000) a order by random();
> SELECT 1000000
>>> create index t_i1 on t (a, c) where b = 1;
> CREATE INDEX
>>> EXPLAIN select a from t where a < 10000 and b = 1 order by a;
> [ doesn't do index-only scan ]
Yeah, see the comment in check_index_only():
/*
* Check that all needed attributes of the relation are available from the
* index.
*
* 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.
*/
This code knows that b is referenced in the query, which would ordinarily
defeat using an index-only scan with this index. There's no very good way
to tell that the only such reference is in a qual that we will later
decide doesn't need to be checked at runtime.
So, yeah, it'd be nice if that worked ... but don't hold your breath.
regards, tom lane
