PGCon2014 - Final Release

PGCon 2014
The PostgreSQL Conference

Markus Winand
Day Tutorials - Day 2 - Wed May 21 - 2014-05-21
Room Montpetit 201
Start time 09:00
Duration 03:00
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.