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.
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 |