Improving Query Performance in PostgreSQL

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

Start Course for Free
4 Hours15 Videos53 Exercises4,235 Learners
4300 XP

Create Your Free Account

GoogleLinkedInFacebook

or

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA. You confirm you are at least 16 years old (13 if you are an authorized Classrooms user).

Loved by learners at thousands of companies

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.

  1. 1

    Bringing Together the Data

    Free

    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.

    Play Chapter Now
    All about joins
    50 xp
    Where are all the athletes from
    100 xp
    Using different joins to explore athletes' regions
    100 xp
    What about the weather
    100 xp
    Subqueries and common table expressions (cte)
    50 xp
    Filtering to freezing with a subquery
    100 xp
    Where winter is white
    100 xp
    Countries with subqueries or CTEs
    100 xp
    Working with temporary tables
    50 xp
    Canadians temp table
    100 xp
    Analyze that temp table
    100 xp
  2. 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.

    Play Chapter Now
    What you write is not what SQL sees
    50 xp
    Order of operations impact on query structure
    50 xp
    Group by and aggregations
    100 xp
    Count and count distinct
    100 xp
    Filtering in the WHERE clause
    50 xp
    OR versus IN with athletes
    100 xp
    Data type filters
    100 xp
    EXPLAIN the filter query plan step
    100 xp
    Filtering while joining
    50 xp
    Where to place a region filter
    100 xp
    Filtering in the join, where, and select
    100 xp
    Aggregating with different data granularities
    50 xp
    Aggregate before joining tables
    100 xp
    South African trends
    100 xp
  3. 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.

    Play Chapter Now
    Queries and tables and views, oh my
    50 xp
    Data loading and storage
    50 xp
    Finding the table type
    100 xp
    Row-oriented storage and partitions
    50 xp
    Row-oriented storage properties
    50 xp
    Previewing a row-oriented table
    100 xp
    Partitioning on location
    100 xp
    Using and creating indexes
    50 xp
    Finding the database indexes
    100 xp
    Creating and using an index
    100 xp
    Compare runtimes
    100 xp
    Using column-oriented storage
    50 xp
    Column-oriented storage properties
    50 xp
    Using the information schema
    100 xp
  4. 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.

    Play Chapter Now
    Query lifecycle and the planner
    50 xp
    Exploring pg_tables
    100 xp
    Basic EXPLAIN
    100 xp
    Index scans
    100 xp
    EXPLAIN the WHERE
    100 xp
    A deeper dive into EXPLAIN
    50 xp
    EXPLAIN parameters
    100 xp
    Aggregating and sorting populations
    100 xp
    Joining in the query plan
    100 xp
    Query structure and query execution
    50 xp
    Subqueries vs. CTEs
    100 xp
    Why the difference?
    50 xp
    Filtering impacts
    100 xp
    Congratulations
    50 xp

In the following tracks

SQL for Database Administrators

Datasets

GDPOlympic AthletesOlympic RegionsAQI

Collaborators

Becca RobinsMona Khalil

Prerequisites

Intermediate SQL
Amy McCarty Headshot

Amy McCarty

Data Analyst

Amy is a data analyst, policy wonk, and all around data nerd. She has a master's degree in policy analytics from Carnegie Mellon and has worked as an analyst in both the nonprofit and for-profit sectors. She loves to learn and shares that passion as an adjunct analytics professor. When not at the computer, Amy stays busy broadening her multicultural culinary prowess and learning to hear all of those silent consonants in the French language.
See More

What do other learners have to say?

“I've used other sites—Coursera, Udacity, things like that—but DataCamp's been the one that I've stuck with.”

Devon Edwards Joseph
Lloyds Banking Group

“DataCamp is the top resource I recommend for learning data science.”

Louis Maiden
Harvard Business School

“DataCamp is by far my favorite website to learn from.”

Ronald Bowers
Decision Science Analytics, USAA