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.