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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Farid Z | 2005-11-29 14:57:31 | BUG #2076: 8.01.01.00 driver transaction error |
Previous Message | Kari Lavikka | 2005-11-29 14:05:44 | postgres 8.1 crashing |