Analyzing index usage with pg_stat in PostgreSQL - Mini Project: Build & Apply

Analyzing index usage with pg_stat

📖 Scenario: You are a database administrator for an online bookstore. You want to understand how often the indexes on your books table are used to improve query performance and optimize your database.

🎯 Goal: Build a query that retrieves index usage statistics from the pg_stat_user_indexes system view for the books table, so you can analyze which indexes are being used and how often.

📋 What You'll Learn

Create a query that selects the index name, number of index scans, and table name from pg_stat_user_indexes.

Filter the results to only include indexes on the books table.

Order the results by the number of index scans in descending order.

💡 Why This Matters

🌍 Real World

Database administrators often need to monitor index usage to improve query speed and reduce unnecessary index overhead.

💼 Career

Understanding how to query PostgreSQL system views for index statistics is a valuable skill for roles like DBA, backend developer, and data engineer.

1

DATA SETUP: Identify the table name

Create a variable called table_name and set it to the string 'books' to specify the table you want to analyze.

PostgreSQL

table_name = 'books'
# Your code here

Need a hint?

Use single quotes around the table name string.

2

CONFIGURATION: Prepare the base query string

Create a variable called base_query and set it to the SQL string that selects indexrelname, idx_scan, and relname from pg_stat_user_indexes.

PostgreSQL

table_name = 'books'
base_query = "SELECT indexrelname, idx_scan, relname FROM pg_stat_user_indexes"
# Your code here

Need a hint?

Write the exact SELECT statement as a string.

3

CORE LOGIC: Add filtering for the table name

Create a variable called filtered_query that adds a WHERE clause to base_query to filter rows where relname equals the table_name variable.

PostgreSQL

table_name = 'books'
base_query = "SELECT indexrelname, idx_scan, relname FROM pg_stat_user_indexes"
filtered_query = base_query + f" WHERE relname = '{table_name}'"
# Your code here

Need a hint?

Use an f-string to insert the table_name variable inside the WHERE clause.

4

COMPLETION: Add ordering by index scans

Create a variable called final_query that adds an ORDER BY clause to filtered_query to sort results by idx_scan in descending order.

PostgreSQL

table_name = 'books'
base_query = "SELECT indexrelname, idx_scan, relname FROM pg_stat_user_indexes"
filtered_query = base_query + f" WHERE relname = '{table_name}'"
final_query = filtered_query + " ORDER BY idx_scan DESC"
# Your code here

Need a hint?

Append the ORDER BY clause as a string to the filtered query.