In this lab, students will learn how PostgreSQL indexes help retrieve data efficiently. They will create different types of indexes, examine query execution plans, compare query performance, and identify situations where indexes are useful.
Database: DVD Rental
DBMS: PostgreSQL
Level: Beginner to Intermediate
After completing this lab, students should be able to:
EXPLAIN and EXPLAIN ANALYZE.Consider a book containing hundreds of pages. If you want to find a particular topic, you can:
A database index works in a similar way. It helps PostgreSQL locate records without necessarily checking every row in a table.
Important points:
Use the following query to view indexes in the current database:
SELECT
tablename,
indexname,
indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;
Student Activity:
Create an index on the amount column of the payment table.
CREATE INDEX idx_payment_amount
ON payment (amount);
Retrieve payment records using the indexed column:
SELECT *
FROM payment
WHERE amount = 5.99;
Student Activity:
Create an index on the payment_date column.
CREATE INDEX idx_payment_date
ON payment (payment_date);
Retrieve payments made after a particular date:
SELECT *
FROM payment
WHERE payment_date >= TIMESTAMP '2007-02-01 00:00:00';
Student Activity:
Modify the query to retrieve payments made within a date range.
A composite index is created using two or more columns.
Create an index on customer_id and payment_date.
CREATE INDEX idx_payment_customer_date
ON payment (customer_id, payment_date);
Execute the following query:
SELECT *
FROM payment
WHERE customer_id = 1
AND payment_date >= TIMESTAMP '2007-02-01 00:00:00';
Student Activity:
customer_id.payment_date.A unique index prevents duplicate values in the indexed column or combination of columns.
Create a unique index on the email column of the customer table:
CREATE UNIQUE INDEX idx_customer_email_unique
ON customer (email);
Note: This command will work only if the existing data contains no duplicate email values.
Check for duplicate email addresses:
SELECT
email,
COUNT(*) AS total
FROM customer
GROUP BY email
HAVING COUNT(*) > 1;
Student Activity:
An expression index is created using an expression or function rather than a simple column reference.
Create an index on lowercase customer email addresses:
CREATE INDEX idx_customer_lower_email
ON customer (LOWER(email));
Execute the following query:
SELECT *
FROM customer
WHERE LOWER(email) = LOWER('MARY.SMITH@EXAMPLE.COM');
Student Activity:
LOWER() function.A partial index contains entries for only those rows that satisfy a specified condition.
Create an index for active customers:
CREATE INDEX idx_customer_active
ON customer (customer_id)
WHERE active = true;
Execute the following query:
SELECT *
FROM customer
WHERE active = true;
Student Activity:
PostgreSQL supports IF NOT EXISTS to avoid an error when an index with the same name already exists.
CREATE INDEX IF NOT EXISTS idx_payment_amount
ON payment (amount);
Student Activity:
Execute the command more than once and observe the result.
Use DROP INDEX to remove an index.
DROP INDEX idx_payment_amount;
To avoid an error if the index does not exist:
DROP INDEX IF EXISTS idx_payment_amount;
Student Activity:
The EXPLAIN command displays the execution plan PostgreSQL intends to use for a query.
Execute:
EXPLAIN
SELECT *
FROM payment
WHERE amount = 5.99;
Observe the execution plan.
Important terms:
| Term | Meaning |
|---|---|
| Seq Scan | PostgreSQL checks rows sequentially. |
| Index Scan | PostgreSQL retrieves rows using an index. |
| Bitmap Index Scan | PostgreSQL uses an index to identify matching rows for a bitmap operation. |
| Bitmap Heap Scan | PostgreSQL retrieves the matching table rows using the bitmap. |
| Cost | Estimated cost of executing a plan. |
| Rows | Estimated number of rows returned. |
EXPLAIN ANALYZE executes the query and displays its actual execution statistics.
EXPLAIN ANALYZE
SELECT *
FROM payment
WHERE amount = 5.99;
Observe:
Important: Unlike EXPLAIN, EXPLAIN ANALYZE actually executes the query. Use it carefully with INSERT, UPDATE, and DELETE statements.
First, examine the query plan:
EXPLAIN ANALYZE
SELECT *
FROM payment
WHERE amount = 5.99;
Create an index:
CREATE INDEX idx_payment_amount
ON payment (amount);
Execute the same query again:
EXPLAIN ANALYZE
SELECT *
FROM payment
WHERE amount = 5.99;
Compare the execution plans.
Student Activity:
Record the following observations:
| Observation | Before Index | After Index |
|---|---|---|
| Scan method | ||
| Estimated cost | ||
| Actual execution time | ||
| Actual rows |
Important: PostgreSQL may still choose a Sequential Scan when it estimates that scanning the table is cheaper. This is normal, especially for small tables or queries that return a large proportion of the rows. Creating an index does not guarantee that PostgreSQL will use it.
PostgreSQL uses table statistics to estimate the cost of different execution plans.
Use ANALYZE to update statistics:
ANALYZE payment;
You can also analyze a specific table column:
ANALYZE payment (amount);
Student Activity:
ANALYZE payment.EXPLAIN again.Complete the following exercises independently.
Create an index named idx_film_release_year on the release_year column of the film table.
Retrieve all films released in 2006.
SELECT *
FROM film
WHERE release_year = 2006;
Use EXPLAIN ANALYZE to inspect the execution plan.
Create an index on the rental_date column of the rental table.
Retrieve rentals made after a specified date.
Create a composite index on the following columns of the rental table:
customer_idrental_dateWrite a query to retrieve a customer’s rentals within a date range.
Check whether the customer table contains duplicate email addresses. If no duplicates exist, create a unique index on email.
Create an index on the uppercase version of the customer’s last name.
Write a query using the same expression.
Create a partial index for films having a rental rate greater than 2.00.
Write a query to retrieve films that satisfy the condition.
Display all indexes in the public schema.
Identify:
Select a suitable query from the payment table.
EXPLAIN ANALYZE.Drop the practice indexes created during this lab.
Use:
DROP INDEX IF EXISTS index_name;
Replace index_name with the actual index name.
Create an appropriate index to support searching payments by customer and payment date.
Write a query to retrieve payments for a particular customer within a specified date range.
Use EXPLAIN ANALYZE to examine the execution plan.
Create an appropriate index to support searching films by title.
Write a query to retrieve films with a specified title.
Compare the execution plan before and after creating the index.
Create an appropriate index to support retrieving rental records for a particular customer.
Write a query to retrieve the customer’s rental history.
Explain why the selected index may improve query performance.
Consider the following query:
SELECT *
FROM payment
WHERE customer_id = 1
AND amount > 5.00
ORDER BY payment_date DESC;
Determine which columns could be included in an index to support this query.
Create a suitable index and examine the execution plan.
Explain whether PostgreSQL uses the index.
Students must submit:
EXPLAIN ANALYZE.| Assessment Area | Marks |
|---|---|
| Creating and managing indexes | 20 |
| Single-column and composite indexes | 15 |
| Unique, partial, and expression indexes | 15 |
| Using EXPLAIN and EXPLAIN ANALYZE | 20 |
| Query plan comparison | 15 |
| Independent exercises and challenges | 10 |
| Documentation and observations | 5 |
| Total | 100 |
EXPLAIN?EXPLAIN and EXPLAIN ANALYZE?| Command | Purpose |
|---|---|
CREATE INDEX |
Creates an index. |
CREATE UNIQUE INDEX |
Creates an index that prevents duplicate indexed values. |
DROP INDEX |
Removes an index. |
EXPLAIN |
Displays the estimated execution plan. |
EXPLAIN ANALYZE |
Executes a query and displays actual execution statistics. |
ANALYZE |
Updates table statistics. |
pg_indexes |
Provides information about database indexes. |