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))

Advanced statistics with pg_stat_statements

While the PostgreSQL statistics collector is rich and mature, having to monitor connection activity can be a little tricky since the pg_stat_activity catalog does not provide historical information. (…)

The pg_stat_statements extension solves this problem by providing a single view that gives you a full history of executed statements, timing, and other little details that can come in very handy when doing introspection. Moreover, pg_stat_statements provides a count of how many times the same statement has been executed, resulting in important information that queries might need to pay attention to for optimization purposes. (Ferrari and Pirozzi 2023, 600)

Since the extension is already shipped, the only thing an administrator needs to do is to enable it in a database cluster. You can do it by editing the postgresql.conf file and adding the following line: 

  shared_preload_libraries = 'pg_stat_statements'

or add it like this (it requires a restart of the database cluster). 

  ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

then verify if it works and enable the extension in your database. 

  -- Check if pg_stat_statements shows up
  SHOW shared_preload_libraries;

  -- Then enable it
  CREATE extension pg_stat_statements;









Usage






  SELECT
    auth.rolname,
    query,
    db.datname,
    calls,
    min_exec_time,
    max_exec_time
  FROM pg_stat_statements
  JOIN pg_authid auth ON auth.oid = userid
  JOIN pg_database db ON db.oid = dbid
  ORDER BY calls DESC;

















Column Name
Description






query
Text of a representative statement in a parametrized form





query_id
Hashcode to identity identical queries





calls
Amount of times a statement has been called





total_exec_time
Total execution time of all statements, in ms





mean_exec_time
Mean execution time in ms





total_plan_time
Total time spent planning the statement, in milliseconds





mean_plan_time
Mean planning time, in ms





shared_blks_hit
 





shared_blks_read
 





dbid
Useful in environments with multiple replicas














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.