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

BUG #2075: Strange choice of bitmap-index-scan

From: "Arjen" <acmmailing(at)tweakers(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2075: Strange choice of bitmap-index-scan
Date: 2005-11-29 14:20:32
Message-ID: 20051129142032.5FD1EF0B3F@svr2.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      2075
Logged by:          Arjen
Email address:      acmmailing(at)tweakers(dot)net
PostgreSQL version: 8.1.0
Operating system:   Gentoo linux (2.6.11 kernel)
Description:        Strange choice of bitmap-index-scan
Details: 

I haven't tried a minimal test-case, but this table:

tweakers=# \d pwprodukten
                                     Table "public.pwprodukten"
     Column     |          Type          |                        Modifiers
----------------+------------------------+----------------------------------
------------------------
 id             | integer                | not null default
nextval('pwprodukten_id_seq'::regclass)
 upid           | character varying(40)  | not null default ''::character
varying
 naam           | character varying(110) | not null
 cat2           | smallint               | not null default 0::smallint
 grafiek        | character(1)           | default 'N'::bpchar
 trend          | smallint               | default 0::smallint
 image          | smallint               | default 0::smallint
 meuk           | smallint               | default 0::smallint
 views          | smallint               | default 0::smallint
 popuindex      | smallint               | default 0::smallint
 url            | character varying(255) |
 infoid         | smallint               | default 0::smallint
 zichtbaar      | boolean                | not null default false
 havereview     | character(1)           | default 'N'::bpchar
 prerelease     | character(1)           | not null default 'N'::bpchar
 havebenchmarks | character(1)           | default 'N'::bpchar
Indexes:
    "pwprodukten_pkey" PRIMARY KEY, btree (id)
    "pwprodukten_cat2_popuindex" btree (cat2, popuindex)
    "pwprodukten_cat2_zichtbaar" btree (cat2, zichtbaar)
    "pwprodukten_infoid" btree (infoid)
    "pwprodukten_upid2" btree (upid)
    "pwprodukten_zichtbaar" btree (zichtbaar)

With this query:
SELECT
*
FROM pwprodukten pr
WHERE pr.Cat2 = 51
AND pr.Zichtbaar = 'true';

yields this plan:
                                           QUERY PLAN
----------------------------------------------------------------------------
---------------------
 Bitmap Heap Scan on pwprodukten pr  (cost=5.62..9.63 rows=144 width=134)
   Recheck Cond: (cat2 = 51)
   Filter: zichtbaar
   ->  BitmapAnd  (cost=5.62..5.62 rows=1 width=0)
         ->  Bitmap Index Scan on pwprodukten_cat2_popuindex 
(cost=0.00..2.50 rows=144 width=0)
               Index Cond: (cat2 = 51)
         ->  Bitmap Index Scan on pwprodukten_cat2_zichtbaar 
(cost=0.00..2.86 rows=144 width=0)
               Index Cond: ((cat2 = 51) AND (zichtbaar = true))

So, it uses the correct index, but somehow decides to also use the other
cat2_... index, which it doesn't need of course.

Responses

pgsql-bugs by date

Next:From: Farid ZDate: 2005-11-29 14:57:31
Subject: BUG #2076: 8.01.01.00 driver transaction error
Previous:From: Kari LavikkaDate: 2005-11-29 14:05:44
Subject: postgres 8.1 crashing

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