PostgreSQL Tutorial: Performance optimization

This chapter explains how to optimize PostgreSQL performance and provides examples.

• EXPLAIN statement – guide you on how to use the EXPLAIN statement to return the execution plan of a query.
• Setting up Slow Query Logging – guide you on how to setup slow query logging in PostgreSQL.
• Configuring pg_stat_statements as an extension – guide you on how to configure pg_stat_statements as an extension.
• Query analysis using pg_stat_statements – guide you on how to use the pg_stat_statements extension, to find slow queries that need to be optimized.
• Tune optimizer settings – guide you on how to tune optimizer settings in PostgreSQL.
• Tuning work_mem setting – guide you on how to tune work_mem setting in PostgreSQL.
• Avoid redundant function calls in queries – guide you on how to avoid redundant function calls in queries.
• Tune the estimated execution cost of a function – guide you on how to tune the estimated execution cost of a function.
• Tune the estimated number of returned rows for a function – guide you on how to tune the estimated number of returned rows for a function.
• Tune parallel safety of a function – guide you on how to tune parallel safety of a function.
• Understand query planning – understand query planning in PostgreSQL.
• Basic relation level statistics – understand the basic relation-level statistics in the system catalog.
• Tune estimated number of NULL values – guide you on how to tune estimated number of NULL values.
• Tune estimated number of distinct values – guide you on how to tune estimated number of distinct values.
• Tune selectivity estimation of equalities – guide you on how to tune selectivity estimation of equalities.
• Tune selectivity estimation of range matching – guide you on how to tune selectivity estimation of range matching.
• Tune selectivity estimation of expressions – guide you on how to tune selectivity estimation of expressions.
• Tune selectivity estimation of multivariate matching – guide you on how to tune selectivity estimation of multivariate matching.
• Understand cost estimation for sequential scan – understand cost estimation for sequential scan.
• Understand cost estimation for parallel query – understand cost estimation for parallel query.
• Understand cost estimation for index scan – understand cost estimation for plain index scan.
• Partition Pruning During Execution – understand partition pruning during query execution.
• Bitmap Scan – understand cost estimation for bitmap scan.
• Index Only Scan – understand cost estimation for index only scan.
• Tune parallel query – guide you on how to tune parallel query in PostgreSQL.
• Cache query plans – guide you on how to cache query plans in PostgreSQL.
• Utilizing inlining of SQL functions – guide you on how to utilize inlining of SQL functions.
• Speed up applications with Redis caching – guide you on how to build caching with Redis into a simple PostgreSQL application.
• Tuning fillfactor setting for a table – guide you on how to tune the fill factor for a table in PostgreSQL.
• Tune Stats Collector – guide you on how to tune stats collector in PostgreSQL.
• Tuning Linux Page Cache – guide you on how to tune the page cache in Linux.
• Logging lock waits and temp files – guide you on how to log lock waits and temp files in PostgreSQL.
• Logging kernel resource usage of queries – guide you on how to log kernel resource usage of queries in PostgreSQL.
• Tuning autovacuum for dead tuple cleanup – guide you on how to tune autovacuum for dead tuple cleanup in PostgreSQL.
• Tuning autovacuum to avoid transaction wraparound problems – guide you on how to tune autovacuum to avoid transaction wraparound problems in PostgreSQL.
• Splitting wide tables – guide you on how to split wide tables in PostgreSQL.
• LZ4 compression for TOAST tables – introduces the LZ4 TOAST compression feature which is available in PostgreSQL version 14, and demonstrates its usage.
• Tuning subqueries – guide you on how to tune subqueries in PostgreSQL.
• Improving VACUUM processing speed – guide you on how to improve VACUUM processing speed in PostgreSQL.
• EXPLAIN parameterized query – guide you on how to use the EXPLAIN statement to display the execution plan of a parameterized statement.
• Tuning checkpoints – guide you on how to tune checkpoints in PostgreSQL.

See more

• PostgreSQL Indexes
• PostgreSQL Documentation: Performance Tips
• PostgreSQL Documentation: Parallel Query
• PostgreSQL Documentation: How the Planner Uses Statistics