Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group