index scan is performed when index-only scan is possible (partial index) - Mailing list pgsql-bugs

From Alexey Bashtanov
Subject index scan is performed when index-only scan is possible (partial index)
Date
Msg-id 52F8C727.3020409@imap.cc
Whole thread Raw
Responses Re: index scan is performed when index-only scan is possible (partial index)
List pgsql-bugs
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

pgsql-bugs by date:

Previous
From: Heikki Linnakangas
Date:
Subject: Re: BUG #9169: Replica (v 9.3.2) crashed with "PANIC: WAL contains references to invalid pages"
Next
From: ste013@hotmail.fr
Date:
Subject: BUG #9164: impossible to install