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

Hi Tom,

I found another example, in case you're interested:

This query:
SELECT * FROM meuktracker m
JOIN pwproduktrel p ON tabel = 'm' AND tabelid = m.id
WHERE m.id = (select min(id) from meuktracker where id > 7810);

It works ok if the subquery is replaced by the actual result, but this
one yields:

Nested Loop (cost=5.08..13.06 rows=9 width=1153)
InitPlan
-> Result (cost=0.73..0.74 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..0.73 rows=1 width=4)
-> Index Scan using meuktracker_pkey on meuktracker
(cost=0.00..2168.16 rows=2981 width=4)
Index Cond: (id > 7810)
Filter: (id IS NOT NULL)
-> Index Scan using meuktracker_pkey on meuktracker m
(cost=0.00..3.88 rows=1 width=1140)
Index Cond: (id = $1)
-> Bitmap Heap Scan on pwproduktrel p (cost=4.34..8.36 rows=9
width=13)
Recheck Cond: (($1 = tabelid) AND (tabel = 'm'::bpchar))
-> BitmapAnd (cost=4.34..4.34 rows=1 width=0)
-> Bitmap Index Scan on pwproduktrel_tabelid_meuk
(cost=0.00..2.04 rows=9 width=0)
Index Cond: ($1 = tabelid)
-> Bitmap Index Scan on pwproduktrel_pkey
(cost=0.00..2.06 rows=9 width=0)
Index Cond: ((tabel = 'm'::bpchar) AND ($1 = tabelid))

With table structures:
Table "public.meuktracker"
Column | Type | Modifiers
-------------+------------------------+----------------------------------------------------------
id | integer | not null default
nextval('meuktracker_id_seq'::regclass)
header | character varying(40) |
message | text |
quote | text |
versie | character varying(30) |
bron | character varying(40) |
link | character varying(255) |
submitter | integer | not null
filegrootte | integer | not null
licentieid | smallint | not null
cat | smallint | not null
authorid | smallint |
time | bigint | not null
linksite | character varying(255) | not null
poll | smallint |
embargo | boolean |
Indexes:
"meuktracker_pkey" PRIMARY KEY, btree (id)
"meuktracker_cat" btree (cat)
"meuktracker_time" btree ("time")

and

Table "public.pwproduktrel"
Column | Type | Modifiers
-----------+--------------+-----------------------------
tabel | character(1) | not null
tabelid | integer | not null
produktid | integer | not null
Indexes:
"pwproduktrel_pkey" PRIMARY KEY, btree (tabel, tabelid, produktid)
"pwproduktrel_produktid" btree (produktid)
"pwproduktrel_produktid_meuk" btree (produktid) WHERE tabel =
'm'::bpchar
"pwproduktrel_produktid_news" btree (produktid) WHERE tabel =
'n'::bpchar
"pwproduktrel_tabel_image" btree (tabelid) WHERE tabel = 'i'::bpchar
"pwproduktrel_tabel_produktid" btree (tabel, produktid)
"pwproduktrel_tabelid" btree (tabelid)
"pwproduktrel_tabelid_meuk" btree (tabelid) WHERE tabel = 'm'::bpchar
"pwproduktrel_tabelid_news" btree (tabelid) WHERE tabel = 'n'::bpchar

(yes, I'm trying to figure out the best index combinations here ;-) )

Either of the two selected indexes is useable, but the
pwproduktrel_tabelid_meuk is obviously (much?) smaller in size and
therefore faster to look in, isn't it?

There are 10575 records in meuktracker and 146757 in pwproduktrel of
which 128513 are with tabel = 'm'.

Best regards,

Arjen

Tom Lane wrote:
> "Arjen" <acmmailing(at)tweakers(dot)net> writes:
>> So, it uses the correct index, but somehow decides to also use the other
>> cat2_... index, which it doesn't need of course.
>
> I've tweaked the heuristics in choose_bitmap_and to (hopefully) work a
> bit better in scenarios like this. Thanks for the example.
>
> regards, tom lane
>