PostgreSQL Tutorial: Performance optimization

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

Optimizer internals

PostgreSQL optimizer choose the query plan with the lowest execution cost based on statistics and the cost based model, to achieve efficient SQL query. This section focuses on the cost estimation in the optimizer.

• 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

This section focuses on the cost estimation and internals of various query plans, and some optimization practices.

• 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.
• Understand cost estimation for in-memory sorting – understand cost estimation for in-memory sorting.
• 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

This section introduces the methods related to optimizing SQL functions.

• Understand PL/pgSQL Function Volatility – understand PL/pgSQL function volatility.
• 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.

SQL optimization

This section introduces the methods related to optimizing SQL queries.

• EXPLAIN statement – guide you on how to use the EXPLAIN statement to return the execution plan of a query.
• EXPLAIN parameterized query – guide you on how to use the EXPLAIN statement to display the execution plan of a parameterized statement.
• 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.
• ‘Rows Removed By Filter’ in EXPLAIN output – understand what’s ‘Rows Removed By Filter’ in EXPLAIN output all about.
• Tuning index scans – guide you on how to tune index scans in PostgreSQL.
• Tuning subqueries – guide you on how to tune subqueries in PostgreSQL.
• Rewrite OR in a query – understand “good” and “bad” ORs and what you can do to avoid the latter.
• Improve joins in SELECT queries – guide you on how to optimize SELECT SQL queries by improving joins.
• Improve filtering in SELECT queries – guide you on how to optimize SELECT SQL queries by improving filtering.
• Tuning LIKE filters using indexes – guide you on how to tune LIKE filters using indexes.
• Speed up COUNT(DISTINCT) using estimates – guide you on how to speed up COUNT(DISTINCT) using database estimates.
• Speed up COUNT(DISTINCT) using aggregations – guide you on how to speed up COUNT(DISTINCT) using aggregations.
• Utilizing inlining of views – guide you on how to utilize inlining of views.
• Just-in-Time Compilation (JIT) – guide you on how to tune SQL queries using just-in-time compilation (JIT).

Tuning parameters

This section introduces the optimization practices about configuration parameters in PostgreSQL.

• 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.
• Tuning commit_delay setting – guide you on how to tune commit_delay setting in PostgreSQL.
• Tuning Linux Page Cache – guide you on how to tune the page cache in Linux.
• 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.
• Improving VACUUM processing speed – guide you on how to improve VACUUM processing speed in PostgreSQL.
• Tuning checkpoints – guide you on how to tune checkpoints in PostgreSQL.
• Tune GUC parameters – guide you on how to tune GUC parameters in PostgreSQL.
• Tuning Parameters vs. Tuning Queries – discuss that tuning parameters vs. tuning queries in PostgreSQL.

Indexing

This section introduces how to use indexes efficiently to improve query performance.

• Tuning Multicolumn Indexes – guide you on how to tune multicolumn indexes in PostgreSQL.
• Multiple Indexes vs. Multicolumn Indexes – discuss that multiple indexes vs. multicolumn indexes in PostgreSQL.
• Leverage expression indexes – guide you on how to leverage expression indexes in PostgreSQL.
• Negative Effects and Costs of Indexes – introduce you to the negative effects and the costs of indexes in PostgreSQL.

Other optimization tips

• 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.
• 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.
• 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.
• 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.
• Measure the network impact on performance – guide you on how to measure the network impact on PostgreSQL performance.
• Fitting more tuples in a data page – guide you on how to fit more tuples in a data page.
• 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.
• Parallel VACUUM – introduce how parallel VACUUM works in PostgreSQL.

See more

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