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

From: "Arjen van der Meijden" <acmmailing(at)tweakers(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2441: All useable indexes for an IN are used, not just one
Date: 2006-05-17 10:31:35
Message-ID: 200605171031.k4HAVZKx099846@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 2441
Logged by: Arjen van der Meijden
Email address: acmmailing(at)tweakers(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Carmen 2006-05-17 11:37:55 BUG #2442: Problemas accessing DB in a multithread pross
Previous Message Alvaro Herrera 2006-05-16 19:49:50 Re: [BUGS] BUG #2429: Explain does not report object's schema