Обсуждение: index scan is performed when index-only scan is possible (partial index)

Hello!

It seems to me there is a situation where index-only scan is possible
but not performed.

-----
[ACTIONS]
 > 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
 > analyze t;
ANALYZE
 > EXPLAIN select a from t where a < 10000 and b = 1 order by a;
                              QUERY PLAN
---------------------------------------------------------------------
  Index Scan using t_i1 on t  (cost=0.42..14013.07 rows=5094 width=4)
    Index Cond: (a < 10000)
(2 rows)

 > create index t_i2 on t (a, b) where b = 1;
CREATE INDEX
 > EXPLAIN select a from t where a < 10000 and b = 1 order by a;
                                 QUERY PLAN
--------------------------------------------------------------------------
  Index Only Scan using t_i2 on t  (cost=0.42..14009.07 rows=5094 width=4)
    Index Cond: ((a < 10000) AND (b = 1))
(2 rows)

[EXPECTED]
in both cases index-only scan is performed

[RECEIVED]
in the first case index scan is performed, though index-only scan is
possible, as no conditions rechecking is required
-----

adding (b=1) expression to index as a column does not help

Regards, Alexey

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