Advanced SQL for Query Tuning and Performance Optimization

Introduction

• Reduce query response time with query tuning
• What you should know

1. How SQL Executes a Query

• From declarative SQL to a procedural execution plan
• Scanning tables and indexes
• Joining tables
• Partitioning data
• Challenge: Choosing how to partition a table
• Solution: Choosing how to partition a table

2. PostgreSQL Tools for Tuning

• Using PostgreSQL in Codespaces
• Explain and analyze
• Example plan: Selecting with a WHERE clause
• Indexes
• Challenge: Generating a query execution plan
• Solution: Generating a query execution plan

3. Types of Indexes

• Indexing
• B-tree indexes
• B-tree index example plan
• Bitmap indexes
• Bitmap index example plan
• Hash indexes
• Hash index example plan
• Bloom filter indexes
• PostgreSQL-specific indexes
• Challenge: Choosing an index
• Solution: Choosing an index

4. Tuning Joins

• Types of joins
• Nested loops
• Nested loop example plan
• Hash joins
• Hash join example plan
• Merge joins
• Merge join example
• Subqueries vs. joins
• Challenge: Designing a join
• Solution: Designing a join

5. Partitioning Data

• Horizontal vs. vertical partitioning
• Partition by range
• Partition by range example
• Partition by list
• Partition by list example
• Partition by hash
• Partition by hash example
• Challenge: Partitioning a table
• Solution: Partitioning a table

6. Materialized Views

• Materialized views
• Creating materialized views
• Refreshing materialized views
• Challenge: Creating a materialized view
• Solution: Creating a materialized view

7. Database Statistics and Identifying Slow Queries

• Collect statistics about data in tables
• Analyzing execution statistics with pg_stat_statements
• Reviewing execution plans with the auto_explain module
• Additional analysis with other pg_stats data
• Challenge: Analyze schema statistics
• Solution: Analyze schema statistics

8. Other Optimization Techniques

• Using common table expressions to avoid repetitive computation
• Hints to the Query Optimizer
• Parallel query execution
• Improving cache utilization
• Miscellaneous tips
• Challenge: Design a common table expression
• Solution: Design a common table expression

Conclusion

• Next steps