Speed Up Postgres Queries with Indexes
The more data you add, the longer it will take to answer new questions you pose to your database. That’s because so far, Postgres has been scanning through the table looking at every row to see if it meets the constraint. We can speed this process up substantially by adding indexes.
[00:00] We've got a table that's pretty small by table standards, honestly, called movies, but we've given it about three million rows and there's instructions on how to set this up, you have the data. We want to perform some queries over the movies where the title equals America. Maybe there's a movie called America. It's 51.
[00:25] You saw a little bit of a lag there. That's probably the first time that you saw a lag like that. It's still running in less than a second, but that is a really long time on a database. Especially on production databases, where you have a lot of people contending for resources, you don't want something like this, because it will take down your entire system.
[00:46] The reason that this is running so slowly is because we don't have an index. We can find that out with an explain statement. We can see here that this is doing a sequential scan on the movies, which means it's going through each movie in order and looking at the title America and saying, "Is that your title?"
[01:07] That's really, really slow. We can perform this a few more times, see that it does take in fact quite a long time. What we want to do is add an index here. We're going to create index. We're going to do this concurrently. This is really, really important. Postgres gives us the ability to add in an index without taking out any locks on the database.
[01:31] If you can do this and you're on a production machine, you should do it. You absolutely should do it. You can do it in Postgres. Let's do that. We'll index the movies on title. That's the name of the index. Movies, title. Now, it has to go through and build that index, which is going to take a little bit of time.
[01:52] That took about a minute and a half. Now if we do the same thing, OK, cool. That finished in a much shorter period of time. Let's explain that. Actually, we didn't perform the exact same query. It still takes about the same amount of time.
[02:18] Here, we can see what this looks like. We see that now it's actually using the index condition, which is going to be a much, much faster way to look this up.