Hi,
While working on the "IOS with partial indexes" patch, I've noticed a
bit strange plan. It's unrelated to that particular patch (reproducible
on master), so I'm starting a new thread for it.
To reproduce it, all you have to do is this (on a new cluster, all
settings on default):
CREATE TABLE t AS SELECT i AS a, i AS b
FROM generate_series(1,10000000) s(i);
CREATE INDEX idx001 ON t (a) where b < 100;
CREATE INDEX idx002 ON t (a) where b < 200;
CREATE INDEX idx003 ON t (a) where b < 300;
ANALYZE t;
EXPLAIN SELECT a FROM t WHERE b < 100;
QUERY PLAN
--------------------------------------------------------------------
Bitmap Heap Scan on t (cost=9.01..13.02 rows=1000 width=4)
Recheck Cond: ((b < 300) AND (b < 200))
Filter: (b < 100)
-> BitmapAnd (cost=9.01..9.01 rows=1 width=0)
-> Bitmap Index Scan on idx003
(cost=0.00..4.13 rows=1000 width=0)
-> Bitmap Index Scan on idx002
(cost=0.00..4.13 rows=1000 width=0)
Now, that's strange IMHO. There's a perfectly matching partial index,
with exactly the same predicate (b<100), but we instead choose the two
other indexes, and combine them using BitmapAnd. That seems a bit
strange - choosing one of them over the perfectly matching one would be
strange too, but why use two and combine them?
Another thing is that this gets fixed by a simple VACUUM on the table.
EXPLAIN SELECT a FROM t WHERE b < 100;
QUERY PLAN
--------------------------------------------------------------------
Index Scan using idx001 on t (cost=0.14..29.14 rows=1000 width=4)
Any idea what's going on here? FWIW all this was on 51d0fe5d (July 23).
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers