Thread: BUG #2441: All useable indexes for an IN are used, not just one

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.

"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