Thread: index scan is performed when index-only scan is possible (partial index)
index scan is performed when index-only scan is possible (partial index)
From
Alexey Bashtanov
Date:
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