The following bug has been logged online:
Bug reference: 2441
Logged by: Arjen van der Meijden
Email address: acmmailing@tweakers.net
PostgreSQL version: 8.2devel, 05-16
Operating system: Solaris 10
Description: All useable indexes for an IN are used, not just one
Details:
Using this table:
Column | Type | Modifiers
------------+---------+-----------
field1 | integer |
somefk | integer |
someswitch | boolean |
somefield | integer |
Indexes:
"testcase_1" btree (somefk)
"testcase_2" btree (somefk) WHERE someswitch
"testcase_3" btree (somefk, somefield)
filled with enough data to have it use indexes, it yields a plan involving
all three indexes with this query:
select * from testcase where somefk in (1, 2) and someswitch;
Bitmap Heap Scan on testcase (cost=7.43..330.58 rows=136 width=13)
Recheck Cond: ((somefk = ANY ('{1,2}'::integer[])) OR (somefk = ANY
('{1,2}'::integer[])) OR (somefk = ANY ('{1,2}'::integer[])))
Filter: ((somefk = ANY ('{1,2}'::integer[])) AND someswitch)
-> BitmapOr (cost=7.43..7.43 rows=410 width=0)
-> Bitmap Index Scan on testcase_3 (cost=0.00..2.48 rows=137
width=0)
Index Cond: (somefk = ANY ('{1,2}'::integer[]))
-> Bitmap Index Scan on testcase_2 (cost=0.00..2.48 rows=136
width=0)
Index Cond: (somefk = ANY ('{1,2}'::integer[]))
-> Bitmap Index Scan on testcase_1 (cost=0.00..2.48 rows=137
width=0)
Index Cond: (somefk = ANY ('{1,2}'::integer[]))
If the in-condition is changed to just one item, only one index is used.
Btw, it doesn't matter whether the indexes are exact copies, nor does the
boolean where-clause matter, I just added that to display all useable
indexes are involved in the bitmapOr. If you'd leave out the boolean switch,
the plan only shows two indexes used.