Performance Optimization (PostgreSQL)

The EXPLAIN Statement

There are a few important things to know before using EXPLAIN:

  • It will only show the best plan, which is the one with the lowest cost among all the evaluated plans.
  • It will not execute the statement you are asking the plan for, at least unless you explicitly ask for its execution. Therefore, the EXPLAIN execution is fast and pretty much constant each time.
  • It will present you with all the execution nodes that the executor will use to provide you with the dataset.

(Ferrari and Pirozzi 2023, 471)

Scan Types

Scan Type Description
Sequential  
Bitmap Heap Scan  
Bitmap Index Scan  
Index Scan  
Index-Only Scan A covering index (see Index (PostgreSQL))

References:

Ferrari, Luca, and Enrico Pirozzi. 2023. Learn Postgresql: Use, Manage, and Build Secure and Scalable Databases with Postgresql 16. 2nd ed. Packt Publishing Ltd.