Re: BUG #2075: Strange choice of bitmap-index-scan - Mailing list pgsql-bugs

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@tweakers.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
>