Overview
-
Learn how to analyze query execution plans and use data modeling strategies to boost query performance.
Syllabus
-
Introduction
- Reduce query reponse time with query tuning
- What you should know
- From declarative SQL to a procedural execution plan
- Scanning tables and indexes
- Joining tables
- Partitioning data
- Installing PostgreSQL
- Overview of pgAdmin
- Explain and analyze
- Example plan: Selecting with a WHERE clause
- 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
- What affects joins performance?
- Nested loops
- Nested loop example plan
- Hash joins
- Hash join example plan
- Merge joins
- Merge join example
- Subqueries vs. joins
- 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
- Materialized views
- Creating materialized views
- Refreshing materialized views
- Collect statistics about data in tables
- Hints to the query optimizer
- Parallel query execution
- Miscellaneous tips
- Next steps
