Postgres Training

Cheatsheet

Explain

1. Sequential Scan: This is the simplest way of fetching data from a table: it scans through every page of data sequentially (important fields: Filter, Rows Removed by Filter
2. Index Scan: An index scan uses an index to find either a specific row, or all rows matching a predicate. (important fields: Filter, Index Cond, Rows Removed by Filter, Rows Removed by Index Recheck, Scan Direction)
3. Bitmap Heap Scan: A bitmap heap scan takes a row location bitmap generated by a Bitmap Index Scan (either directly, or through a series of bitmap set operations via BitmapAnd and BitmapOr nodes) and looks up the relevant data. (important fields: Exact Heap Blocks, Filter, Lossy Heap Blocks, Recheck Cond, Rows Removed by Filter, Rows Removed by Index Recheck)
4. Bitmap Index Scan: a bitmap index scan as a middle ground between a sequential scan and an index scan. Like an index scan, it scans an index to determine exactly what data it needs to fetch, but like a sequential scan, it takes advantage of data being easier to read in bulk. (important fields: Index Cond)

For more details, read explain

Contents

1. Prepara Data
2. EXPLAIN
3. Example 01: Reduce seq scan + rows removed by filter by adding index
4. Example 02: Multicolumn index + row-wise comparison
5. Example 03: Reduce seq scan by adding index

References

1. Distinguishing Access and Filter-Predicates
2. Postgres choosing a filter instead of index cond when OR is involved
3. Row-wise comparison
4. pganalyze
5. multicolumn index
6. Using EXPLAIN
7. PostgreSQL and UUID as primary key