English | MP4 | AVC 1280×720 | AAC 48KHz 2ch | 1h 44m | 214 MB
SQL queries can be fast and highly efficient, but they can also be slow and demand excessive CPU and memory resources. For many SQL programmers, occasional bouts with long-running queries and poor performance are simply par for the course. But by gaining a better understanding of how databases translate SQL queries into execution plans, you can take steps to avoid these issues. In this course, Dan Sullivan shows developers how to analyze query execution plans and use data modeling strategies to boost query performance. Dan describes how SQL queries are executed; highlights different types of indexes and how they factor in query tuning; covers several methods for performing joins; and discusses how to use partitioning and materialized views to improve performance.
Topics include:
- How SQL executes queries
- Working with PostgreSQL tools for tuning
- Bitmap and hash indexes
- Using different types of indexes to improve performance
- Challenges with joining tables
- When to use partitioning to improve performance
- Collecting statistics about data in tables
Table of Contents
1 Reduce query reponse time with query tuning
2 What you should know
3 From declarative SQL to a procedural execution plan
4 Scanning tables and indexes
5 Joining tables
6 Partitioning data
7 Installing PostgreSQL
8 Overview of pgAdmin
9 Explain and analyze
10 Example plan Selecting with a WHERE clause
11 Indexes
12 Indexing
13 B-tree indexes
14 B-tree index example plan
15 Bitmap indexes
16 Bitmap index example plan
17 Hash indexes
18 Hash index example plan
19 PostgreSQL-specific indexes
20 What affects joins performance
21 Nested loops
22 Nested loop example plan
23 Hash joins
24 Hash join example plan
25 Merge joins
26 Merge join example
27 Subqueries vs. joins
28 Horizontal vs. vertical partitioning
29 Partition by range
30 Partition by range example
31 Partition by list
32 Partition by list example
33 Partition by hash
34 Partition by hash example
35 Materialized views
36 Creating materialized views
37 Refreshing materialized views
38 Collect statistics about data in tables
39 Hints to the query optimizer
40 Parallel query execution
41 Miscellaneous tips
42 Next steps
Resolve the captcha to access the links!