The following bug has been logged online:
Bug reference: 2075
Logged by: Arjen
Email address: [EMAIL PROTECTED]
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.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
