From: | Alexey Bashtanov <bashtanov(at)imap(dot)cc> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | index scan is performed when index-only scan is possible (partial index) |
Date: | 2014-02-10 12:33:43 |
Message-ID: | 52F8C727.3020409@imap.cc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-02-10 15:50:56 | Re: BUG #9175: REINDEX on functional index fails |
Previous Message | ia.shumilova | 2014-02-10 12:16:20 | BUG #9175: REINDEX on functional index fails |