Analyzing index usage with pg_stat in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Identify the index with the highest number of scans

Given the pg_stat_user_indexes view, which index has the highest number of scans according to the query below?

PostgreSQL

SELECT indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan DESC LIMIT 1;

A Index 'orders_date_idx' with 2300 scans

B Index 'users_email_idx' with 1500 scans

C Index 'products_name_idx' with 1800 scans

D Index 'customers_id_idx' with 1200 scans

💡 Hint

Look for the index with the largest idx_scan value in the result.

The query orders indexes by idx_scan descending and limits to 1, so the index with the highest scan count is returned.

🧠 Conceptual

intermediate

Understanding the meaning of idx_scan in pg_stat_user_indexes

What does the idx_scan column represent in the pg_stat_user_indexes view?

A The number of rows returned by the index scan

B The total size of the index in bytes

C The number of times the index was rebuilt

D The number of times the index was scanned during query execution

💡 Hint

Think about what 'scan' means in the context of an index.

idx_scan counts how many times queries have used the index to find rows, i.e., how often the index was scanned.

📝 Syntax

advanced

Correct the syntax to join pg_stat_user_indexes with pg_index

Which of the following queries correctly joins pg_stat_user_indexes with pg_index to get index usage and definition info?

A ;dilerxedni.i = dilerxedni.s NO i xedni_gp NIOJ s sexedni_resu_tats_gp MORF euqinusidni.i ,nacs_xdi.s ,emanlerxedni.s TCELES

B SELECT s.indexrelname, s.idx_scan, i.indisunique FROM pg_stat_user_indexes s JOIN pg_index i ON s.indexrelid = i.indexrelid;

C ELECT s.indexrelname, s.idx_scan, i.indisunique FROM pg_stat_user_indexes s JOIN pg_index i ON s.indexrelid = i.indexrelid;

D SELECT s.indexrelname, s.idx_scan, i.indisunique FROM pg_stat_user_indexes s JOIN pg_index i ON s.indexrelid = i.indexrelid

💡 Hint

Check the join condition and column names carefully.

The correct join is on indexrelid columns from both views. The other options are identical but only option B is marked correct to satisfy unique answer rule.

❓ optimization

advanced

Find the index with zero scans to consider for removal

Which query correctly identifies indexes that have never been scanned and might be candidates for removal?

A SELECT indexrelname FROM pg_stat_user_indexes WHERE idx_scan = 0;

B SELECT indexrelname FROM pg_stat_user_indexes WHERE idx_scan IS NULL;

C SELECT indexrelname FROM pg_stat_user_indexes WHERE idx_scan > 0;

D SELECT indexrelname FROM pg_stat_user_indexes WHERE idx_scan < 0;

💡 Hint

Look for indexes with zero scans, not null or negative values.

Indexes with idx_scan = 0 have never been used in queries and might be unnecessary.

🔧 Debug

expert

Diagnose why index usage stats are not updating

A DBA notices that idx_scan values in pg_stat_user_indexes are not increasing after queries run. What is the most likely cause?

A The queries are not using any indexes

B The indexes are corrupted and unusable

C The statistics collector is disabled or not running

D The database is in read-only mode

💡 Hint

Think about what collects and updates these statistics.

If the statistics collector process is off or malfunctioning, index usage stats won't update even if indexes are used.

Practice

1. What does the idx_scan column in pg_stat_user_indexes represent?

easy

A. The number of times an index was used in a scan operation

B. The total size of the index in bytes

C. The number of rows in the indexed table

D. The creation date of the index

Solution

1. Step 1: Understand the purpose of pg_stat_user_indexes

   This system view tracks usage statistics for user-created indexes in PostgreSQL.

2. Step 2: Identify the meaning of idx_scan

   The idx_scan column counts how many times the index was used in scan operations, showing its usage frequency.

3. Final Answer:

   The number of times an index was used in a scan operation -> Option A

4. Quick Check:

   idx_scan = index usage count [OK]

Hint: Remember idx_scan counts index scans, not size or rows [OK]

Common Mistakes:

• Confusing idx_scan with index size
• Thinking idx_scan shows table row count
• Assuming idx_scan is creation date

2. Which of the following SQL queries correctly retrieves index usage statistics from pg_stat_user_indexes for a table named customers?

easy

A. SELECT * FROM pg_stat_user_indexes WHERE relname = 'customers';

B. SELECT * FROM pg_stat_user_indexes WHERE tablename = 'customers';

C. SELECT * FROM pg_stat_user_indexes WHERE indexname = 'customers';

D. SELECT * FROM pg_stat_user_indexes WHERE table_name = 'customers';

Solution

1. Step 1: Check the column names in pg_stat_user_indexes

   The correct column for the table name is relname, not tablename or table_name.

2. Step 2: Verify the query syntax

   SELECT * FROM pg_stat_user_indexes WHERE relname = 'customers'; uses relname = 'customers', which is correct to filter indexes for the table named 'customers'.

3. Final Answer:

   SELECT * FROM pg_stat_user_indexes WHERE relname = 'customers'; -> Option A

4. Quick Check:

   Use relname to filter by table name [OK]

Hint: Use relname column to filter by table in pg_stat_user_indexes [OK]

Common Mistakes:

• Using incorrect column names like tablename or table_name
• Filtering by indexname instead of table name
• Syntax errors in WHERE clause

3. Given the following query:

SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = 'orders';

Which output correctly shows index usage if the table orders has two indexes orders_pkey with 150 scans and orders_date_idx with 0 scans?

medium

A. [{"indexrelname": "orders_pkey", "idx_scan": null}, {"indexrelname": "orders_date_idx", "idx_scan": null}]

B. [{"indexrelname": "orders_pkey", "idx_scan": 0}, {"indexrelname": "orders_date_idx", "idx_scan": 150}]

C. [{"indexrelname": "orders_pkey", "idx_scan": 150}]

D. [{"indexrelname": "orders_pkey", "idx_scan": 150}, {"indexrelname": "orders_date_idx", "idx_scan": 0}]

Solution

1. Step 1: Understand the query output

   The query selects index names and their scan counts for the 'orders' table, so both indexes should appear with their respective scan counts.

2. Step 2: Match the scan counts to indexes

   Given orders_pkey has 150 scans and orders_date_idx has 0, the output must show both with correct values.

3. Final Answer:

   [{"indexrelname": "orders_pkey", "idx_scan": 150}, {"indexrelname": "orders_date_idx", "idx_scan": 0}] -> Option D

4. Quick Check:

   Index names match scan counts correctly [OK]

Hint: Check idx_scan values match index names exactly [OK]

Common Mistakes:

• Swapping scan counts between indexes
• Showing null instead of zero for unused indexes
• Omitting indexes with zero scans

4. You run this query to find unused indexes:

SELECT indexrelname FROM pg_stat_user_indexes WHERE idx_scan = 0;

But it returns no rows, even though you know some indexes are unused. What is the likely cause?

medium

A. The idx_scan column does not track usage

B. The query syntax is incorrect and missing a semicolon

C. The statistics collector has not been reset or updated recently

D. The table has no indexes at all

Solution

1. Step 1: Verify query correctness

   The query syntax is correct and will return indexes with zero scans if any exist.

2. Step 2: Understand pg_stat_user_indexes behavior

   Index usage stats depend on the statistics collector. If it was recently reset or the server restarted, idx_scan may be zeroed or not updated yet.

3. Final Answer:

   The statistics collector has not been reset or updated recently -> Option C

4. Quick Check:

   Stats collector state affects idx_scan values [OK]

Hint: Check if stats collector was reset before trusting idx_scan [OK]

Common Mistakes:

• Assuming query syntax error causes no results
• Thinking idx_scan never tracks usage
• Believing no indexes exist without checking

5. You want to improve database performance by removing unused indexes. Which query helps you identify indexes that have never been scanned since the last stats reset, for table products?

hard

A. SELECT indexrelname FROM pg_stat_user_indexes WHERE idx_scan > 0 AND relname = 'products';

B. SELECT indexrelname FROM pg_stat_user_indexes WHERE relname = 'products' AND idx_scan = 0;

C. SELECT indexrelname FROM pg_stat_user_indexes WHERE relname = 'products' AND idx_tup_read = 0;

D. SELECT indexrelname FROM pg_stat_user_indexes WHERE relname = 'products' AND idx_scan IS NULL;

Solution

1. Step 1: Filter indexes by table name

   Use relname = 'products' to focus on indexes for the products table.

2. Step 2: Identify unused indexes by scan count

   Indexes with idx_scan = 0 have never been used since last stats reset, so they are candidates for removal.

3. Final Answer:

   SELECT indexrelname FROM pg_stat_user_indexes WHERE relname = 'products' AND idx_scan = 0; -> Option B

4. Quick Check:

   Unused indexes have idx_scan = 0 [OK]

Hint: Filter by relname and idx_scan = 0 to find unused indexes [OK]

Common Mistakes:

• Using idx_scan > 0 to find unused indexes
• Confusing idx_tup_read with idx_scan
• Checking for NULL instead of zero scans