Introduction to databases — Tutorial 5

Indexing

In this tutorial, we learn how to create indexes in a relational database and study their impact on query performance.

1 Environment Preparation

Download the movie database from Edunao.
Open pgAdmin on your computer and connect to the PostgreSQL server that you used in the previous tutorials.
Create a new database. You can call it movies, or choose another name.
Right-click the database name and select Restore.
Format: select Plain ; Filename: select the file that you downloaded before.
Click on Restore and wait between 20 and 30 seconds.

After the import is complete, right-click the database name again and select Query Tool. A new window should appear where you can type queries against the selected database.

2 Query Performance Without Indexes

We first evaluate the performance of queries without indexes, using the customer table.

Exercise

Exercise 2.1 How many rows does the table customer contain?

One way to measure the performance of a query is to use the EXPLAIN (ANALYZE, BUFFERS) command followed by the query. For example:

Exercise

Exercise 2.2

Write a query to search for a family name that does not exist in the customer table (e.g., xxx).
Execute the query with EXPLAIN (ANALYZE, BUFFERS) and discuss the result.

Exercise

Exercise 2.3 Repeat the same query 5 times to get an average of the running time.

3 Impact of an Index

The following SQL command creates an index on selected columns of a table:

CREATE INDEX name_of_index
ON table_name (column1, column2, ...)

Exercise

Exercise 3.1 Create an index on the customer_last_name column of the customer table. How long does it take to create the index?

Exercise

Exercise 3.2 Now analyze the query looking for all customers whose last name is Howard. How does the query plan change? What is the execution time now?

4 About the table size

We now evaluate the impact that the size of a table has on the use of an index.

Create a small table with 100 actors using the following query:

CREATE TABLE actor_100 AS
SELECT *
FROM actor
LIMIT 100

This table will have two columns, actor_id and actor_name.

Create an index on actor_name.

Exercise

Exercise 4.1 Write a query to search for an actor name that does not exist in the table (e.g., xx). Analyze the query. Is the index used?

Exercise

Exercise 4.2 Create another table with 1000 actors and repeat the experiment. Is the index used?

5 Composite Indexes

Let’s look at an example of a composite index.

Exercise

Exercise 5.1 Drop the index created earlier on customer_last_name. Use the DROP INDEX command followed by the index name.

Exercise

Exercise 5.2 Analyze the query that retrieves all customers whose first name is Ludovic and last name is Smith. What is the execution time? Run the query five times to get a proper estimate.

Exercise

Exercise 5.3 Create an index on both customer_first_name and customer_last_name, in this order. How long does it take to create this index? Does it take longer than creating an index on a single column?

Exercise

Exercise 5.4 Analyze the query that retrieves all customers whose first name is Ludovic and last name is Smith. Is the index used? What is the execution time? Execute 5 times to get a proper estimate.

Exercise

Exercise 5.5 Rewrite the same query, swapping the order of the first name and family name conditions in the WHERE clause. Is the index used?

Exercise

Exercise 5.6 Rewrite the previous query to search for all customers whose last name is Smith. Is the index used? What is the execution time?

Let’s verify whether the observations made in the previous exercise are correct.

Create a table that only contains distinct first names.

CREATE TABLE customer_high_card AS
SELECT DISTINCT ON (customer_first_name)
       customer_first_name,
       customer_last_name
FROM customer

Exercise

Exercise 5.7 Create an index on the table customer_high_card on columns customer_first_name and customer_last_name, in this order. Search for all customers whose last name is Smith. Is the index used?