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.
You will:
• 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
Author(s): Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova
Edition: 1
Publisher: Apress
Year: 2021
Language: English
Commentary: Vector PDF
Pages: 344
City: New York, NY
Tags: SQL; Relational Databases; PostgreSQL; Query Performance Optimization; SQL Stored Procedures; Relational Algebra; SQL Execution Plan
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?
Bitmaps
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
Which Queries Are Considered 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
Create a Materialized View or Not?
Do Materialized Views Need to Be Optimized?
Dependencies
Partitioning
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
Mass Updates and 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: Application Development and Performance
Response Time Matters
World Wide Wait
Performance Metrics
Impedance Mismatch
The 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 11: 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 12: Dynamic SQL
What Is Dynamic SQL
Why It Works Better in Postgres
What About SQL Injection?
How to Use Dynamic SQL in OLTP Systems
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 13: Avoiding the Pitfalls of Object-Relational Mapping
Why Application Developers Like NORM
ORM vs. NORM
NORM Explained
Implementation Details
Complex Searches
Updates
Insert
Update
Delete
Why Not Store JSON?!
Performance Gains
Working Together with Application Developers
Summary
Chapter 14: 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 15: 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: Constructing the Query
Step 3: Long
Step 4: Incremental Updates
Step 5: Non-incremental Long Query
But Wait, There Is More!
Summary
Chapter 16: Conclusion
Index
