Checklist to tune PostgreSQL Query performance

Tuning server performance or query speed requires a lot of data collection and implementation of best practices followed by applying solutions specific to issues.

below is the checklist that can be referred by beginners.

1. EXPLAIN:  (Query execution plan)

• The EXPLAIN command shows the execution plan of a statement and is based on the statistics about the table. 
• Always remember the right plan is very critical for good performance. 
• The EXPLAIN command will break down how PostgreSQL will execute the SQL query, but it is more of an estimation. 
• We can use the ANALYZE keyword in the explain plan, which actually executes the query, and then displays the true row counts and true run time accumulated within each plan node.

2. VACUUM:

• The VACUUM command basically removes the tuples that are deleted or made obsolete by an update but were not physically removed from their table. 
• This is very helpful to avoid bloating. 
• It is good practice to analyze a keyword with VACUUM. 

Reference: https://www.postgresql.org/docs/12/sql-vacuum.html.



3. ANALYZE:

• ANALYZE collects statistics about the contents of tables in the database, which helps the query planner to find the most efficient execution plans for queries.

Reference: https://www.postgresql.org/docs/12/sql-analyze.html.



4. Database Indexes:

• It is always recommended to create indexes on a table. 
• PostgreSQL will execute a full table scan in the absence of indexes, which makes executing a query very slow.   
• The types of indexes available in PostgreSQL are 
• B-tree (the default index)
• hash,
• GiST,
• SP-GiST
• GIN
• PostgreSQL creates implicit indexes when the table has a primary/unique key. 
• we should try to avoid creating unused or unnecessary indexes on a table, as these could also affect performance.
• We can also use the REINDEX command to fix unusable indexes or when an index gets bloated.

Reference: https://www.postgresql.org/docs/12/sql-createindex.html.



5. auto_explain module:

• The auto_explain module is used for logging execution plans of slow statements automatically. 

Reference: https://www.postgresql.org/docs/12/auto-explain.html.



6. pg_stat_statements:

• The pg_stat_statements module is used for tracking the execution statistics of SQL statements to identify the queries that are slow. 

Reference: https://www.postgresql.org/docs/12/pgstatstatements.html.

7. Logging:

• log_min_duration_statement (integer) can be helpful in tracking down unoptimized queries.

8. A third-party tool like pgBadger:

• pgBadger is an open-source tool that generates a detailed report of activity on the database server, including temp files, slow queries, etc. 
• It can be downloaded from here: https://pgbadger.darold.net

9. PostgreSQL configuration parameters:

• Applying best practices in PostgreSQL configuration parameters can get better performance. 
• To do this we need to edit the postgresql.conf file, which resides under $data/ directory of your installation. 
• The Parameters we can configure are 
• max_connections,
• checkpoint_segments
• work_mem
• random_page_cost