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.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"Arjen van der Meijden" <acmmailing@tweakers.net> writes:
> 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[]))

Thanks for the report. I've applied patches to improve this.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq