Skip to main content

Course

Improving Query Performance in PostgreSQL

IntermediateSkill Level

4.8+

Updated 01/2025

Learn how to structure your PostgreSQL queries to run in a fraction of the time.

Start Course for Free

SQLProgramming

4 hr

15 videos

53 Exercises

4,300 XP

13,467

Statement of Accomplishment

Loved by learners at thousands of companies

Training a Team?

Try for Business

Course Description

Losing time on slow queries? Hesitant to share your queries with more seasoned coworkers? In this course, you will learn how to structure your PostgreSQL to run in a fraction of the time. Exploring intertwined data relating Olympic participation, country climate, and gross domestic product, you will experience firsthand how changes in filtering method and using subqueries impact query performance. You will learn the properties of a row oriented database while also seeing how Hawaii's volcanos impact air quality. Restructuring your queries with the query planner and the SQL order of operations, you will soon be dazzling your coworkers with your effortless efficiency.

Prerequisites

Data Manipulation in SQL

1

Bringing Together the Data

Bundle up as you dive into the Winter Olympics! You will learn how to join, subquery, and create temporary tables while finding which Olympic athletes brave sub-freezing temperatures to train. You will also learn about the query planner and how its functionality can guide your SQL structure to faster queries.

All about joins

Where are all the athletes from

Using different joins to explore athletes' regions

What about the weather

Subqueries and common table expressions (cte)

Filtering to freezing with a subquery

Where winter is white

Countries with subqueries or CTEs

Working with temporary tables

Canadians temp table

Analyze that temp table

2

Minimizing Results and Decreasing the Load

Dig up those past algebra memories while learning the SQL order of operations. Find which countries "should" have the most athletes by looking at population and gross domestic product (GDP) while learning the best way to filter. You will also learn when your query aggregates (sums, counts, etc.) and how you can structure your query to optimize this process.

What you write is not what SQL sees

Order of operations impact on query structure

Group by and aggregations

Count and count distinct

Filtering in the WHERE clause

OR versus IN with athletes

Data type filters

EXPLAIN the filter query plan step

Filtering while joining

Where to place a region filter

Filtering in the join, where, and select

Aggregating with different data granularities

Aggregate before joining tables

South African trends

3

Using Database Designed Properties

Zero in on the properties that improve database performance. Discover when your table is not a table but a view. Learn how your database's storage structure (row or column oriented) impacts your query structure. You will explore volcanic smog while using partitions and indexes to speed your queries.

Queries and tables and views, oh my

Data loading and storage

Finding the table type

Row-oriented storage and partitions

Row-oriented storage properties

Previewing a row-oriented table

Partitioning on location

Using and creating indexes

Finding the database indexes

Creating and using an index

Compare runtimes

Using column-oriented storage

Column-oriented storage properties

Using the information schema

4

Assessing Query Performance

Learn the lingo of the Query Lifecycle and dive into the query planner. Explore how the query planner creates and optimizes the query plan. Find your next vacation locale by looking for countries with recent population growth while also seeing how a join impacts the query steps. Fine tune your optimization techniques by seeing how different filters speed your query times.

Query lifecycle and the planner

Exploring pg_tables

Basic EXPLAIN

Index scans

EXPLAIN the WHERE

A deeper dive into EXPLAIN

EXPLAIN parameters

Aggregating and sorting populations

Joining in the query plan

Query structure and query execution

Subqueries vs. CTEs

Why the difference?

Filtering impacts

Congratulations

Improving Query Performance in PostgreSQL

Course
Complete

Earn Statement of Accomplishment

Add this credential to your LinkedIn profile, resume, or CV
Share it on social media and in your performance reviewEnroll Now

Don’t just take our word for it

*4.8

from 405 reviews

84%

14%

2%

0%

0%

Sort by

Pedro

5 days ago

Michał

6 days ago

chrys yannick

6 days ago

Pape Ibrahima

7 days ago

Manuel

last week

Komi Armel

last week

Michał

chrys yannick

Pape Ibrahima

FAQs

What datasets are used to practice query optimization in PostgreSQL?

You work with intertwined data on Olympic participation, country climate, gross domestic product, and Hawaii volcano air quality data to practice various optimization techniques.

What is the query planner and how does it help optimize queries?

The query planner is a PostgreSQL tool that creates and optimizes execution plans for your queries. You learn to read and interpret its output to guide structural improvements.

Does the course cover indexes and partitions?

Yes. Chapter 3 teaches you to use partitions and indexes to speed up queries, alongside understanding views and how row versus column oriented storage affects your query design.

Will I learn the SQL order of operations?

Yes. Chapter 2 covers the SQL order of operations and shows how understanding when aggregation occurs can help you structure queries for better filtering and performance.

How does this course differ from the SQL Server query performance course?

This course is specific to PostgreSQL, covering its query planner, storage structures, and optimization tools. The techniques are tailored to PostgreSQL rather than SQL Server.

Join over 19 million learners and start Improving Query Performance in PostgreSQL today!

Grow your data skills with DataCamp for Mobile

Make progress on the go with our mobile courses and daily 5-minute coding challenges.