Advanced SQL for Query Tuning and Performance Optimization
Offered By: LinkedIn Learning
Course Description
Overview
Learn how to analyze query execution plans and use data modeling strategies to boost query performance.
Syllabus
Introduction
- Reduce query response time with query tuning
- What you should know
- 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
- 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
- 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
- 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
- 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
- Materialized views
- Creating materialized views
- Refreshing materialized views
- Challenge: Creating a materialized view
- Solution: Creating a materialized view
- 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
- 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
- Next steps
Taught by
Dan Sullivan
Related Courses
Introduction to DatabasesMeta via Coursera Web Development
Udacity Introduction to Data Science
University of Washington via Coursera Datenmanagement mit SQL
openHPI Sabermetrics 101: Introduction to Baseball Analytics
Boston University via edX