Types of scans in database

Here is a micro blog on types of scans in database, this is something which stays under the hood for most of us but it's a good idea to know what happens.

So whenever we fetch something from our db there are three ways for db engine to search the data based on few conditions.

Let's consider an example,

CREATE TABLE Employees(
id int primary key not null,
name text not null,
department text,
age int not null
);


I have a employees table and there are lets say there are 1 million rows and I decided to get those employees whose age is between 28 to 41.

select * from employees where age>=28 and age<=41;

As in this case db has to go through each row one by one and check if the row satisfies the condition.

TOO SLOWW

What if I have an index on age.

CREATE INDEX age_idx on Employees(age);

Now I have a structure apart from actual db data where I can lookup from and then retrieve those data which satisfies the condition. This is index scan.

Easy and fast? What if 90% of my data satisfies this condition or maybe I want those employees who belong to IT department.💀

select * from employees where age>=28 and age<=41 and department='IT';`

It'll be very slow to retrieve from heap(actual data) one by one.

Here comes bitmap scan, in this case db engine creates a bitmap, it's an array where indices refers to row and the values can be 0 or 1. 1 means this row needs to be fetched and 0 means nope.

The bitmap gets created first and then all data gets fetched at once. It's fast!

So whats the difference between bitmap and index scan.

Index scan is very efficient when the filtered data is very small and specific so we can quickly fetch from index one by one.

Bitmap scan can be helpful when we have more conditions and there are too many rows which needs to be fetched.
A great example here is let's say we have index on department and age column.

create index age_idx on Employees(age);
create index dept_idx on Employees(department);

Now we need to fetch those rows where age is between 28 and 41 and also the department is IT.

Here we can create bitmaps on the both indexes and run an AND operation over both bitmaps to get those rows which satisfies both of the conditions.

Thats it!

Sorry for such long post, wanted to keep it a micro blog but I failed :P