PostgreSQL Query Optimization: The Ultimate Guide to Building Efficient Queries - Second Edition

Write optimized queries. This book helps you write queries that perform fast and deliver results on time. You will learn that query optimization is not a dark art practiced by a small, secretive cabal of sorcerers. Any motivated professional can learn to write efficient queries from the get-go and capably optimize existing queries. You will learn to look at the process of writing a query from the database engine’s point of view, and know how to think like the database optimizer. The book begins with a discussion of what a performant system is and progresses to measuring performance and setting performance goals. It introduces different classes of queries and optimization techniques suitable to each, such as the use of indexes and specific join algorithms. You will learn to read and understand query execution plans along with techniques for influencing those plans for better performance. The book also covers advanced topics such as the use of functions and procedures, dynamic SQL, and generated queries. All of these techniques are then used together to produce performant applications, avoiding the pitfalls of object-relational mappers. This second edition includes new examples using Postgres 15 and the newest version of the PostgresAir database. It includes additional details and clarifications about advanced topics, and covers configuration parameters in greater depth. Finally, it makes use of advancements in NORM, using automatically generated functions. What You Will Learn• Identify optimization goals in OLTP and OLAP systems• Read and understand PostgreSQL execution plans• Distinguish between short queries and long queries• Choose the right optimization technique for each query type• Identify indexes that will improve query performance• Optimize full table scans• Avoid the pitfalls of object-relational mapping systems• Optimize the entire application rather than just database queries Table of Contents About the Authors About the Technical Reviewer Acknowledgments Introduction Chapter 1: Why Optimize? What Do We Mean by Optimization? Why It Is Difficult: Imperative and Declarative Optimization Goals Optimizing Processes Optimizing OLTP and OLAP Database Design and Performance Application Development and Performance Other Stages of the Lifecycle PostgreSQL Specifics Summary Chapter 2: Theory: Yes, We Need It! Query Processing Overview Compilation Optimization and Execution Relational, Logical, and Physical Operations Relational Operations Logical Operations Queries as Expressions: Thinking in Sets Operations and Algorithms Summary Chapter 3: Even More Theory: Algorithms Algorithm Cost Models Data Access Algorithms Storage Structures Full Scan Index-Based Table Access Index-Only Scan Comparing Data Access Algorithms Index Structures What Is an Index? B-Tree Indexes Why Are B-Trees Used So Often? Other Kinds of Indexes Combining Relations Nested Loops Hash-Based Algorithms Sort-Merge Algorithm Comparing Algorithms Summary Chapter 4: Understanding Execution Plans Putting Everything Together: How an Optimizer Builds an Execution Plan Reading Execution Plans Understanding Execution Plans What Is Going On During Optimization? Why Are There So Many Execution Plans to Choose From? How Are Execution Costs Calculated? How Can the Optimizer Be Led Astray? Summary Chapter 5: Short Queries and Indexes What Makes a Query “Short”? Choosing Selection Criteria Index Selectivity Unique Indexes and Constraints Indexes and Non-equal Conditions Indexes and Column Transformations Indexes and the like Operator Using Multiple Indexes Compound Indexes How Do Compound Indexes Work? Lower Selectivity Using Indexes for Data Retrieval Covering Indexes Excessive Selection Criteria Partial Indexes Indexes and Join Order When Are Indexes Not Used Avoiding Index Usage Why Does PostgreSQL Ignore My Index? Let PostgreSQL Do Its Job! How to Build the Right Index(es) To Build or Not to Build Which Indexes Are Needed? Which Indexes Are Not Needed? Indexes and Short Query Scalability Summary Chapter 6: Long Queries and Full Scans Which Queries Are Considered Long? Long Queries and Full Scans Long Queries and Hash Joins Long Queries and the Order of Joins What Is a Semi-join? Semi-joins and Join Order More on Join Order What Is an Anti-join? Semi- and Anti-joins Using the JOIN Operator When Is It Necessary to Specify Join Order? Grouping: Filter First, Group Last Grouping: Group First, Select Last Using SET Operations Avoiding Multiple Scans Conclusion Chapter 7: Long Queries: Additional Techniques Structuring Queries Temporary Tables and CTEs Temporary Tables Common Table Expressions (CTEs) Views: To Use or Not to Use Why Use Views? Materialized Views Creating and Using Materialized Views Refreshing Materialized Views Should I Create a Materialized View? Do Materialized Views Need to Be Optimized? Dependencies Partitioning Does Partitioning Improve Performance? Why Create a Partitioned Table? Parallelism Summary Chapter 8: Optimizing Data Modification What Is DML? Two Ways to Optimize Data Modification How Does DML Work? Low-Level Input/Output The Impact of Concurrency Control Data Modification and Indexes DML and Vacuum Mass UPDATE/DELETE Frequent Updates Referential Integrity and Triggers Summary Chapter 9: Design Matters Design Matters Why Use a Relational Model? Types of Databases Entity-Attribute-Value Model Key-Value Model Hierarchical Model Combining the Best of Different Worlds Flexibility vs. Efficiency and Correctness Must We Normalize? Use and Misuse of Surrogate Keys Summary Chapter 10: What About Configuration Parameters? PostgreSQL Configuration Parameters Overview Memory Allocation Connections and Sessions Tuning Parameters for Better Performance Are There Better Ways? Other Limitations of Parameter Tuning Conclusion Chapter 11: Application Development and Performance Response Time Matters World Wide Wait Performance Metrics Impedance Mismatch A Road Paved with Good Intentions Application Development Patterns “Shopping List Problem” Interfaces Welcome to the World of ORM In Search of a Better Solution Summary Chapter 12: Functions Function Creation Internal Functions User-Defined Functions Introducing Procedural Language Dollar Quoting Function Parameters and Function Output: Void Functions Function Overloading Function Execution Function Execution Internals Functions and Performance How Using Functions Can Worsen Performance Any Chance Functions Can Improve Performance? Functions and User-Defined Types User-Defined Data Types Functions Returning Composite Types Using Composite Types with Nested Structure Functions and Type Dependencies Data Manipulation with Functions Functions and Security What About Business Logic? Functions in OLAP Systems Parameterizing No Explicit Dependency on Tables and Views Ability to Execute Dynamic SQL Stored Procedures Functions with No Results Functions and Stored Procedures Transaction Management Exception Processing Summary Chapter 13: Dynamic SQL What Is Dynamic SQL Why Dynamic SQL Works Better in Postgres What About SQL Injection? How to Use Dynamic SQL for an Optimal Execution Plan How to Use Dynamic SQL in OLAP Systems Using Dynamic SQL for Flexibility Using Dynamic SQL to Aid the Optimizer FDWs and Dynamic SQL Summary Chapter 14: Avoiding the Pitfalls of Object-Relational Mapping Why Application Developers Like NORM ORM vs. NORM NORM Explained NORM in the Application Perspective NORM from a Database Perspective Mapping JSON to the Database Generating Database Code Getting Data from the Database Modifying Data in the Database Why Not Store JSON?! Performance Gains Working Together with Application Developers Summary Chapter 15: More Complex Filtering and Search Full Text Search Multidimensional and Spatial Search Generalized Index Types in PostgreSQL GIST Indexes Indexes for Full Text Search Indexing Very Large Tables Indexing JSON and JSONB Summary Chapter 16: Ultimate Optimization Algorithm Major Steps Step-by-Step Guide Step 1: Short or Long? Step 2: Short Step 2.1: The Most Restrictive Criteria Step 2.2: Check the Indexes Step 2.3: Add an Excessive Selection Criterion, If Applicable Step 2.4: Building (or Rebuilding) the Query Step 3: Long Step 4: Incremental Updates Step 5: Non-incremental Long Query But Wait—There Is More! Summary Chapter 17: Conclusion Index "Write optimized queries. This book helps you write queries that perform fast and deliver results on time. You will learn that query optimization is not a dark art practiced by a small, secretive cabal of sorcerers. Any motivated professional can learn to write efficient queries from the get-go and capably optimize existing queries. You will learn to look at the process of writing a query from the database engine's point of view, and know how to think like the database optimizer. The book begins with a discussion of what a performant system is and progresses to measuring performance and setting performance goals. It introduces different classes of queries and optimization techniques suitable to each, such as the use of indexes and specific join algorithms. You will learn to read and understand query execution plans along with techniques for influencing those plans for better performance. The book also covers advanced topics such as the use of functions and procedures, dynamic SQL, and generated queries. All of these techniques are then used together to produce performant applications, avoiding the pitfalls of object-relational mappers. This second edition includes new examples using Postgres 15 and the newest version of the PostgresAir database. It includes additional details and clarifications about advanced topics, and covers configuration parameters in greater depth. Finally, it makes use of advancements in NORM, using automatically generated functions. What You Will Learn Identify optimization goals in OLTP and OLAP systems Read and understand PostgreSQL execution plans Distinguish between short queries and long queries Choose the right optimization technique for each query type Identify indexes that will improve query performance Optimize full table scans Avoid the pitfalls of object-relational mapping systems Optimize the entire application rather than just database queries Who This Book Is For IT professionals working in PostgreSQL who want to develop performant and scalable applications, anyone whose job title contains the words "database developer" or "database administrator"" or who is a backend developer charged with programming database calls, and system architects involved in the overall design of application systems running against a PostgreSQL database"