Useful vs Useless Bitmaps

It’s Hot Out There


Paul white (b|t) did what I think he does best: make a casual, off-hand remark about something mystifying with such absolute certainty that it makes your brain halt. At least that’s what happens to me.

It all started with:

“The Bitmap is hopeless.”

Earth Shattering Kaboom


I’d never considered Bitmaps in excruciating detail.

Sorta Bloom Filter-y. They show up in some parallel Hash and Merge Join plans (simplifying a bit because they’re hidden in serial Hash Join plans).

They seem nice. Early row reductions. Never thought of one as “hopeless”.

But then!

Hope Bloats


Let’s take a look at a plan with a hopeless Bitmap.

Despair

In the outer (top) branch of the plan, a Bitmap is created. It gets applied at the Scan of the Users table.

Louder than Bits

What makes it hopeless?

  • The Users table has 2,465,710 rows in it
  • Despite the Bitmap, we read 2,465,593 rows and
  • We pass 2,465,590 of those rows along to the Repartition Streams

In other words, the Bitmap barely filtered out any rows whatsoever. Did it hurt performance? Am I mad at Bitmaps? No and no.

At least not here.

Beware Bitmap Placement


In some query plans, the Bitmap may not make it all the way down to the Scan operator.

If there’s a Partial Aggregate after the Scan, you may find the Bitmap applied at the Repartition Streams.

Better late than never, I suppose.

Participation Ribbon

Helpful Bitmaps


In a helpful Bitmap plan, the details look much different.

Bully

Visually, this plan looks much different than the Hopeless Bitmap plan.

The number of rows (39,789) read from the scan is much lower than the table cardinality (2,465,710).

The details of the scan are also interesting.

Where else can I go?
  • We did not have to read all 2,465,710 rows
  • We only had to read¬† 83,144 of them
  • We were able to Bitmap out ~50% of them, down to 39,789

That’s a Bangin’ Bitmap.

Thanks for reading!



Leave a Reply

Your email address will not be published. Required fields are marked *