From the course: Advanced SQL for Data Scientists
Unlock the full course today
Join today to access over 22,400 courses taught by industry experts or purchase this course individually.
Analyzing a query with a join
From the course: Advanced SQL for Data Scientists
Analyzing a query with a join
- [Instructor] Now Explain Plan is also useful for understanding how Postgres implements joins. So let's take a look at that. Now, the first step of course is we need another table to join to our sensor management table. So let's just create a simple little reference table for our sensor IDs. Let's make a table that gives a name for each ID and we'll keep it simple. We'll just call our sensors, we use the word sensor and then append the sensor ID and that'll be the name. So for that, I'm going to use the generate series again. So we'll do a select, select i from generate_series and we have 100 sensors so we'll go from one to 100 and we'll alias that as i. Okay, so that gives us our list of 100. And we're going to use this as part of a common table expression. So I'll say with, let's call the sensor IDs as, and we'll say select as i, and we will select i as ID and then we'll create Sensor and we'll actually use…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Contents
-
-
-
-
-
EXPLAIN and ANALYZE commands7m 41s
-
Generating data with generate_sequence4m 16s
-
Generating time series data6m 35s
-
Analyzing a query with WHERE clauses and indexes2m 46s
-
Analyzing a query with a join5m 57s
-
Challenge: Optimize a query using an explain plan33s
-
Solution: Optimize a query using an explain plan1m 39s
-
-
-
-