PGCon2014 - Final Release
PGCon 2014
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Markus Winand |
| Schedule | |
|---|---|
| Day | Tutorials - Day 2 - Wed May 21 - 2014-05-21 |
| Room | Montpetit 201 |
| Start time | 09:00 |
| Duration | 03:00 |
| Info | |
| ID | 649 |
| Event type | Lecture |
| Track | Tutorial |
| Language used for presentation | English |
The Art of Indexing
How to get most out of B-tree indexes
Proper indexing is not a trivial topic and, frankly speaking, understood quite poorly. This training teaches you everything you need to know about proper indexing. It does not assume any previous knowledge on this topic or about database performance in general — all you need to know is SQL.
The training starts by introducing the anatomy of B-tree indexes and uses this foundation to explain the most common causes of poor SQL performance: the index/query mismatch. It also explains many performance anti-patterns and the corresponding performance patterns. You'll also see that indexing is not just about the WHERE-clause. We'll also cover how to index for JOIN, ORDER BY and GROUP BY as well as the recently introduced index-only scan (since 9.2).
Although the focus on B-tree indexes might seem very limiting, you'll see that the B-tree index is still the performance workhorse that solves most performance issues.
After this training… You will know how B-tree indexes really work. You will understand the causes of slow index lookups and prevent them. You will know why indexing is a development task. You will love execution plans. You will exploit multi-column indexes to their full extend. You will apply SQL functions correctly. You will understand the importance and effects of bind parameters. You will recognize common anti-patterns and use the more efficient alternatives. You will understand that an index on the join predicates is not always the right choice. You will understand the performance impact of ORM tools and know what to do about it. You will use indexes to optimize order by clauses. You will not be confused by those countless SQL performance myths. You will write faster SQL.