Database Management · Advanced level

Advanced level · Module 2 of 6 · 24 guided hours

Performance Tuning

Read query plans, design indexes, rewrite slow queries and tune configuration based on measurements.

Learning objectives

  • Read EXPLAIN and EXPLAIN ANALYZE output
  • Design effective indexes for real workloads
  • Rewrite queries and tune configuration with measured results

Topics

  • the query planner
  • EXPLAIN and EXPLAIN ANALYZE
  • B-tree, composite, partial and covering indexes
  • query rewriting and N+1
  • statistics
  • memory settings and connection pooling

Lessons

  1. Lesson 1 Reading Query Plans

    Understand how the planner chooses a plan and read EXPLAIN and EXPLAIN ANALYZE output.

    query plannerEXPLAINEXPLAIN ANALYZEestimated vs actual rows

  2. Lesson 2 Index Design

    Choose index types and column order for real queries, and avoid indexes that cost more than they help.

    composite index and column orderpartial indexcovering index (INCLUDE)index selectivity

  3. Lesson 3 Rewriting Slow Queries

    Improve queries by changing how they are written: avoiding N+1 patterns, functions on indexed columns and unnecessary work.

    sargable conditionsN+1 queriespagination with keysavoiding SELECT *

  4. Lesson 4 Configuration and Connection Management

    Tune memory settings and manage connections with pooling, based on measurements.

    shared_buffers and work_memconnection limitsconnection pooling (PgBouncer)measure, change, measure

Practical activity

Take the five slowest queries of a provided workload, diagnose each with EXPLAIN ANALYZE, fix them and report before-and-after timings.

Expected outcomes

  • Evidence-based tuning
  • Indexes that match real queries

Assessment

Tuning report marked on correct diagnosis, measured improvements and justified trade-offs.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.