Understanding Explain — Postgres — 1

Scan Types

Sequential scan

• It scans rows one by one.
• Good for the small table.
• When lots of rows are returning by query then query planner will pick Sequential scan. As random page reads always worse than CPU.

Index Scan

• It scan index then it goes to the table and picks that row.
• It’s good when less number of rows getting returned by the query. As random page reads increase at the time of index scan. Lesser random page reads better than CPU.

Index only scan

• It scans the index and if you are selecting only that value then it will return without looking at table row.

Bitmap scan

• What is BitMap?

Let's say your query is select * from table where row1 = 2; Then we will travel all index and find out if row1 is 2 or not. We will put 1 where condition success and put 0 where condition fails. Now all rows whose value is 1 is my answer.

• It will scan bitmap index. find out rows.
• Will do bitmap heap scan for available rows, It will recheck condition and filter out.
• It’s good for the medium number of rows.
• As it’s scanning all page available data in a shot so it has lesser random reads.

Join

Nested loop

• For each row — Pick one row and match from other table row.
• It’s good for smaller table.

Hash Join

• Pick less row table. Hash all available value.
• Now traverse bigger table row. And as per hash it and get that join value.

Merge Join

• Sort both inner and outer table.
• Outer row will traverse till inner row has lesser value than outer.
• Its good for big join.

Limit

• When you apply limit then query planner takes decision on it.
• Sometime it’s better, sometime it become worse.

Jsonb

• Bigger @> smaller — if bigger contains smaller value.
• Smaller <@ Bigger — if smaller contained by bigger value.
• Gin index. It always get picked when you are doing Bigger @> smaller query.