In this video, see the hash join used in an example plan.
- [Instructor] We're back in pgAdmin now, to see what an explain plan with a hash join looks like. We'll start with the commands that will force the use of the hash join. We'll set enable_nestloop to false and set enable_hashjoin to true, and we'll set enable_mergejoin to false. Now, we'll add the same query we looked at in the nested loop example, and that is SELECT, and from the staff table, we're going to pull the ID, the last name, the job title, and then from the company regions, we're going to get the country. Then it'll be FROM staff. Excuse me, I'll pull that down a little bit. From the staff table, which we'll alias with an s, and we'll INNER JOIN on company_regions, which we'll alias with a cr, and of course, this will be ON region_id in both tables. Okay, let's just make sure I put that in correctly by executing. Okay, we got the data we were expecting, so let's look at the explain plan. Move that up a little bit. Notice, this query uses a hash join as expected. Row 4 in the explain plan indicates the hash table is built on the company regions table. Row 3 indicates we're scanning the staff table and calculating a hash value, which is compared in Row 2. The total cost of this join is just under 24 computational units.
Author
Released
4/1/2019- How SQL executes queries
- Working with PostgreSQL tools for tuning
- Bitmap and hash indexes
- Using different types of indexes to improve performance
- Challenges with joining tables
- When to use partitioning to improve performance
- Collecting statistics about data in tables
Skill Level Advanced
Duration
Views
Related Courses
-
Advanced SQL for Data Scientists
with Dan Sullivan1h 24m Advanced -
Introduction to Spark SQL and DataFrames
with Dan Sullivan1h 53m Intermediate
-
Introduction
-
1. How SQL Executes a Query
-
Scanning tables and indexes3m 12s
-
Joining tables2m 24s
-
Partitioning data2m 23s
-
2. PostgreSQL Tools for Tuning
-
Installing PostgreSQL2m 41s
-
Overview of pgAdmin3m 21s
-
Explain and analyze3m 37s
-
Indexes3m 7s
-
-
3. Types of Indexes
-
Indexing3m 1s
-
B-tree indexes2m 2s
-
B-tree index example plan2m 18s
-
Bitmap indexes1m 46s
-
Bitmap index example plan2m 39s
-
Hash indexes1m 19s
-
Hash index example plan1m 32s
-
PostgreSQL-specific indexes1m 17s
-
-
4. Tuning Joins
-
Nested loops3m 19s
-
Nested loop example plan3m 55s
-
Hash joins1m 25s
-
Hash join example plan1m 57s
-
Merge joins2m 17s
-
Merge join example3m 16s
-
Subqueries vs. joins1m 11s
-
5. Partitioning Data
-
Partition by range2m 6s
-
Partition by list1m 37s
-
Partition by list example4m 55s
-
Partition by hash2m 12s
-
Partition by hash example4m 51s
-
6. Materialized Views
-
Materialized views1m 41s
-
-
7. Other Optimization Techniques
-
Hints to the query optimizer2m 13s
-
Parallel query execution2m 3s
-
Miscellaneous tips1m 47s
-
Conclusion
-
Next steps41s
-
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Hash join example plan