Real-World Example – Web Development


Okay, so time for a demo. I am currently logged into my development machine for Hipmunk. We use Postgres as our database, and I’m going to log into that database right now. Psql is the Postgres command. And we have a table for our hotels–for our hotel search product. So we can say select count *– count is a SQL command for counting the rows in a table– from hotels. So you can see we have 302,000 rows in this table. Okay, so let’s run a quick SQL statement against this thing. We’re going to say select name from hotels where id=51492. This is a hotel id that I happen to know exists. Okay, it’s called the Hotel Karlsteiner Stuben. So one of the things we can do in Postgres is I’m going to run the same command again–the same SQL statement again– with explain analyze. What this is going to do is this is going to run the query and then explain to me what it did. What this says is that it did a sequential scan on hotels–that’s our table name, hotels– sequential scan on hotels, filtering for id 51492, and it took 142 milliseconds to scan that table of 300,000 hotels and find the one with the correct id. Now, if I were to create an index on that field–on the id field– things should get faster. So let’s go ahead and create the index– create index hotel id on hotels id. This is the SQL command for creating an index. I’m not going to quiz you on this, but this is what it looks like. Create index–this is the name of our index, hotel id on which table. So in this case, it’s the hotels table. That’s the table we’re using. And then it takes in parentheses which field we’re going to use. And in this case, we’re going to give it the id. I submit that. Postgres creates our index. And now let’s run this explain analyze command again. So now instead of doing a sequential scan, we did an index scan, using hotel id–that was the name of our index here– on the table. And in this case, we can see the total run time went from 142 milliseconds to 0.163 milliseconds. That is a substantial improvement. And let’s go ahead and drop that index– drop index hotel– and we’ll rerun this command one more time to see it slow again. Ta-da! We went from 0.163 milliseconds to 141 milliseconds. So that’s just a quick real-world demo of what an index can do. And it’s pretty handy that the Postgres database will explain to us what exactly is going on. All right, let’s move on.

Add a Comment

Your email address will not be published. Required fields are marked *