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.
• Performance Tips for Bulk Data Loading – introduce some performance tips for bulk data loading in PostgreSQL.
• The Impact of Full Page Writes – introduce you to the impact of full-page writes in PostgreSQL.
• Tuning network latency for your application server – guide you on how to tune network latency between your application server and PostgreSQL.

See more

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