Advanced SQL for Query Tuning and Performance Optimization

Introduction

• Reduce query reponse 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

2. PostgreSQL Tools for Tuning

• Installing PostgreSQL
• Overview of pgAdmin
• Explain and analyze
• Example plan: Selecting with a WHERE clause
• Indexes

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
• PostgreSQL-specific indexes

4. Tuning Joins

• What affects joins performance?
• Nested loops
• Nested loop example plan
• Hash joins
• Hash join example plan
• Merge joins
• Merge join example
• Subqueries vs. joins

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

6. Materialized Views

• Materialized views
• Creating materialized views
• Refreshing materialized views

7. Other Optimization Techniques

• Collect statistics about data in tables
• Hints to the query optimizer
• Parallel query execution
• Miscellaneous tips

Conclusion

• Next steps