PostgreSQL Tutorial: Performance optimization

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

Optimizer internals

• 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.

• Tune selectivity estimation with expression index – guide you on how to tune selectivity estimation with expression index.

Query plan

• 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.

• Sequential Scan – understand the internals of sequential scans.

• Index Scan Types: Bitmap, Index, and Index Only – understand the three different PostgreSQL scan types for an index: bitmap, index, and index only.

• Bitmap Scan – understand cost estimation for bitmap scan.

• Index Only Scan – understand cost estimation for index only scan.

• Leverage Index-Only Scans – guide you on how to leverage index only scans in PostgreSQL.

• 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.

Optimize SQL functions

• 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.

• Utilizing inlining of SQL functions – guide you on how to utilize inlining of SQL functions.

Other optimization tips

• 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.

• 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.

• Measure the volume of data returned by a query and its network transmission cost – guide you on how to measure the volume of data returned by a query and the elapsed time.

• Rewrite OR in a query – understand “good” and “bad” ORs and what you can do to avoid the latter.

• Fitting more tuples in a data page – guide you on how to fit more tuples in a data page.

• Negative Effects and Costs of Indexes – introduce you to the negative effects and the costs of indexes in PostgreSQL.

See more

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