PostgreSQL Query Optimization: The Ultimate Guide to Building Efficient Queries

• Tongue: English
• Leaves: 360
• Edition: 2
• Category: Library

✦ Table of Contents

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