From the course: Advanced SQL for Query Tuning and Performance Optimization

Unlock the full course today

Join today to access over 24,800 courses taught by industry experts.

Hash index example plan

Hash index example plan - SQL Tutorial

From the course: Advanced SQL for Query Tuning and Performance Optimization

Start my 1-month free trial Buy for my team

Hash index example plan

“

- [Instructor] Let's create a hash index on the email column of the staff table. The email column is a good candidate for hash indexing, since the email should be unique to each person and it's likely we want to be able to, say, look up a person's data using their email. Also, it's not likely that we'd want to perform a range scan query using email address. So to specify a hash index, we start with create index, and of course, we give it a name, so let's call this idx_staff_email, and we're going to create this on the staff table. Now, instead of immediately specifying the column, at this point, we introduce a new clause called using hash, and that just tells the Postgres that we want to use a hash index on the column email. And so let's run that, and so now we have created an index. Let's take a look at what an explain plan will look like when we try and select star from staff where email is equal to bphillips5@time.com.…

Contents