Types of Scans used by Query Optimizer.

Hi Guys,

In the last article, We have discussed how to interpret the execution plan. In this blog, I'm going to explain the types of scans using by Optimizer.

Scan Methods : 

Sequential Scan

Sequential Scan in PostgreSQL seems at the complete table to take the wanted rows. This is the insufficient effective scanning procedure because it scans through the complete table as saved on disk. The Planner may like to conduct a sequential scan if the state column in the query does not have an index or if the planner considers that the state will be met by most of the rows in the table.
 
Examine the following example:

Index Scan

The Index Scan crosses the B-tree of an index and scans for matching records in the B-tree leaf nodes. It then recovers the data from the table. The Index Scan technique is considered for use when there are a proper index and the optimizer forecasts to return approximately some rows of the table. If there is an index on column age, the resulting the production plan may seem like this:

The distinct parts of data here are the name of the index utilized (iuser_age) and the index status (age = 50).

Index Only Scan

The Index Only Scan algorithm has been included in PostgreSQL 9.2. The benefit of this approach is that it bypasses the costly table access when the database can get the columns in the index itself. Consider the subsequent example with an index on the two columns name and age:

In this example, the index iusers_multiple is formed on all of the chosen columns so the associated data can be fetched direct. This proposal might be attractive, 
but please retain in mind that you require to create a higher index. You should regularly check whether the performance gain is deserving using the technique.

Bitmap Index Scan + Recheck + Bitmap Heap Scan

This is an optimization of a normal Index Scan. In a general Index Scan, the row is obtained quickly after it is found in an index. In a Bitmap Index Scan, the important rows are first stored in a bitmap. The bitmap is ordered by the actual location of a row after the completion of the scan. The rows are then obtained in the order of their physical location. The concept is that individual disk page is fetched at most once. After fetching a disk page, the rows are rechecked for the state in the query. The rechecking is needed because on the moment – e.g., when the query returns a huge part of the index – the Bitmap Index Scan drops to filter rows when browsing the index. Let's look at the example with an index on the lastletter column:

First, the isentence_lletter index is used to build a bitmap then produces a small list of disk pages. The pages are obtained and the scan takes each applicable row in every one of them. The operation is recognized by the Recheck Condition requirement in the execution plan.

Thanks 

Sanjay Saini