索引使用情况监控
学习使用系统视图监控索引的有效性，并识别未使用或性能不佳的索引。
索引使用情况监控 是 CoddyKit 上的免费 Advanced PostgreSQL: Indexing, Partitioning, Replication 课时。 这是第 2 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 Advanced PostgreSQL: Indexing, Partitioning, Replication 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程共包含 4 节课。
本课时的部分内容尚未翻译，以英文显示。
Why Monitor Index Usage?
Indexes are powerful tools for speeding up queries, but they aren't free. They consume disk space and add overhead to data modifications (INSERT, UPDATE, DELETE).
Monitoring index usage helps us understand if our indexes are actually working for us or just taking up space.
Introducing `pg_stat_user_indexes`
PostgreSQL provides several system views to monitor database activity. For index usage, the pg_stat_user_indexes view is your best friend.
This view tracks statistics for indexes on user-defined tables, giving you insights into how often each index is being scanned.
Key Index Usage Metrics
When you query pg_stat_user_indexes, look out for these columns:
idx_scan: The number of times the index has been scanned.idx_tup_read: The number of index entries returned by scans.idx_tup_fetch: The number of live table rows fetched through the index.
These tell you how frequently and effectively an index is being used.
Finding Unused Indexes
The easiest win in index optimization is identifying indexes that are never used. An index with idx_scan = 0 is a strong candidate for removal.
Removing unused indexes can reduce disk space, speed up writes, and simplify database maintenance.
Demo: Querying Unused Indexes
Let's run a query to find all indexes that have never been scanned since the last statistics reset. Try running this example:
SELECT
relname AS table_name,
indexrelname AS index_name,
idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY table_name, index_name;Beyond Unused: Underperforming Indexes
An index might be used (idx_scan > 0) but still be 'underperforming' if it's not chosen by the query planner when it should be, or if it's leading to many sequential scans on the table itself.
To spot these, we need to compare index usage with overall table access patterns.
Table Scan Insights with `pg_stat_user_tables`
The pg_stat_user_tables view provides statistics at the table level. Key columns here are:
seq_scan: Number of sequential scans initiated on this table.idx_scan: Number of index scans initiated on this table.
A high seq_scan count on a large table often indicates a missing or ineffective index.
Comparing Sequential vs. Index Scans
By comparing seq_scan and idx_scan from pg_stat_user_tables, we can identify tables that are frequently being scanned sequentially, even if indexes exist.
A high ratio of sequential scans to index scans on a table suggests potential indexing issues or queries that aren't utilizing available indexes.
Demo: Scan Ratio Query
This query calculates the percentage of sequential scans for each table. Tables with a high percentage of seq_scan might need attention.
SELECT
relname AS table_name,
seq_scan,
idx_scan,
(seq_scan * 100.0) / (CASE WHEN seq_scan + idx_scan = 0 THEN 1 ELSE seq_scan + idx_scan END) AS seq_scan_percent
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_scan_percent DESC;Quick Check
You're trying to find indexes that are consuming disk space but are never being used by any query. Which PostgreSQL system view would you primarily consult for this information?
Recap & Next Steps
Great job! In this lesson, you learned how to monitor index effectiveness using PostgreSQL's system views.
pg_stat_user_indexeshelps find unused indexes (idx_scan = 0).pg_stat_user_tablesreveals the balance between sequential and index scans on tables.- By combining these, you can identify indexes that are candidates for removal or further investigation.
In the next lesson, we'll dive into reindexing and maintaining index health!
用 AI 导师学习 Advanced PostgreSQL: Indexing, Partitioning, Replication — 免费
在浏览器中编写并运行真实代码，获得全天候 AI 导师的即时帮助，并在网页或应用中继续学习。
- 课程
- 11
- 课程
- 44
常见问题解答
「索引使用情况监控」课时是免费的吗？
是的 — 「索引使用情况监控」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程的其余内容，请升级到 CoddyKit PRO。 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程共包含 4 节课。
「索引使用情况监控」这节课中我会学到什么？
学习使用系统视图监控索引的有效性，并识别未使用或性能不佳的索引。 你通过在浏览器中直接运行的动手代码来练习 Advanced PostgreSQL: Indexing, Partitioning, Replication，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 Advanced PostgreSQL: Indexing, Partitioning, Replication 需要有经验吗？
无需任何先前经验。CoddyKit 上的 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 2 节课，共 4 节。
「索引使用情况监控」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 Advanced PostgreSQL: Indexing, Partitioning, Replication 课中编写并运行代码吗？
能。每节 Advanced PostgreSQL: Indexing, Partitioning, Replication 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。