pg_stat_user_indexes 是一个统计视图，显示当前数据库中每个用户表索引的访问统计信息。
pg_stat_user_indexes 在PostgreSQL 7.2 中被添加。
按 PostgreSQL 版本定义
pg_stat_user_indexes (PostgreSQL 19)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+---------
relid | oid | | |
indexrelid | oid | | |
schemaname | name | | |
relname | name | | |
indexrelname | name | | |
idx_scan | bigint | | |
last_idx_scan | timestamp with time zone | | |
idx_tup_read | bigint | | |
idx_tup_fetch | bigint | | |
pg_stat_user_indexes (PostgreSQL 18)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+---------
relid | oid | | |
indexrelid | oid | | |
schemaname | name | | |
relname | name | | |
indexrelname | name | | |
idx_scan | bigint | | |
last_idx_scan | timestamp with time zone | | |
idx_tup_read | bigint | | |
idx_tup_fetch | bigint | | |
pg_stat_user_indexes (PostgreSQL 17)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+---------
relid | oid | | |
indexrelid | oid | | |
schemaname | name | | |
relname | name | | |
indexrelname | name | | |
idx_scan | bigint | | |
last_idx_scan | timestamp with time zone | | |
idx_tup_read | bigint | | |
idx_tup_fetch | bigint | | |
pg_stat_user_indexes (PostgreSQL 16)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+---------
relid | oid | | |
indexrelid | oid | | |
schemaname | name | | |
relname | name | | |
indexrelname | name | | |
idx_scan | bigint | | |
last_idx_scan | timestamp with time zone | | |
idx_tup_read | bigint | | |
idx_tup_fetch | bigint | | |
pg_stat_user_indexes (PostgreSQL 15)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Collation | Nullable | Default
---------------+--------+-----------+----------+---------
relid | oid | | |
indexrelid | oid | | |
schemaname | name | | |
relname | name | | |
indexrelname | name | | |
idx_scan | bigint | | |
idx_tup_read | bigint | | |
idx_tup_fetch | bigint | | |
pg_stat_user_indexes (PostgreSQL 14)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Collation | Nullable | Default
---------------+--------+-----------+----------+---------
relid | oid | | |
indexrelid | oid | | |
schemaname | name | | |
relname | name | | |
indexrelname | name | | |
idx_scan | bigint | | |
idx_tup_read | bigint | | |
idx_tup_fetch | bigint | | |
pg_stat_user_indexes (PostgreSQL 13)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Collation | Nullable | Default
---------------+--------+-----------+----------+---------
relid | oid | | |
indexrelid | oid | | |
schemaname | name | | |
relname | name | | |
indexrelname | name | | |
idx_scan | bigint | | |
idx_tup_read | bigint | | |
idx_tup_fetch | bigint | | |
pg_stat_user_indexes (PostgreSQL 12)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Collation | Nullable | Default
---------------+--------+-----------+----------+---------
relid | oid | | |
indexrelid | oid | | |
schemaname | name | | |
relname | name | | |
indexrelname | name | | |
idx_scan | bigint | | |
idx_tup_read | bigint | | |
idx_tup_fetch | bigint | | |
pg_stat_user_indexes (PostgreSQL 11)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Collation | Nullable | Default
---------------+--------+-----------+----------+---------
relid | oid | | |
indexrelid | oid | | |
schemaname | name | | |
relname | name | | |
indexrelname | name | | |
idx_scan | bigint | | |
idx_tup_read | bigint | | |
idx_tup_fetch | bigint | | |
pg_stat_user_indexes (PostgreSQL 10)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Collation | Nullable | Default
---------------+--------+-----------+----------+---------
relid | oid | | |
indexrelid | oid | | |
schemaname | name | | |
relname | name | | |
indexrelname | name | | |
idx_scan | bigint | | |
idx_tup_read | bigint | | |
idx_tup_fetch | bigint | | |
pg_stat_user_indexes (PostgreSQL 9.6)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Modifiers
---------------+--------+-----------
relid | oid |
indexrelid | oid |
schemaname | name |
relname | name |
indexrelname | name |
idx_scan | bigint |
idx_tup_read | bigint |
idx_tup_fetch | bigint |
pg_stat_user_indexes (PostgreSQL 9.5)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Modifiers
---------------+--------+-----------
relid | oid |
indexrelid | oid |
schemaname | name |
relname | name |
indexrelname | name |
idx_scan | bigint |
idx_tup_read | bigint |
idx_tup_fetch | bigint |
pg_stat_user_indexes (PostgreSQL 9.4)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Modifiers
---------------+--------+-----------
relid | oid |
indexrelid | oid |
schemaname | name |
relname | name |
indexrelname | name |
idx_scan | bigint |
idx_tup_read | bigint |
idx_tup_fetch | bigint |
pg_stat_user_indexes (PostgreSQL 9.3)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Modifiers
---------------+--------+-----------
relid | oid |
indexrelid | oid |
schemaname | name |
relname | name |
indexrelname | name |
idx_scan | bigint |
idx_tup_read | bigint |
idx_tup_fetch | bigint |
pg_stat_user_indexes (PostgreSQL 9.2)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Modifiers
---------------+--------+-----------
relid | oid |
indexrelid | oid |
schemaname | name |
relname | name |
indexrelname | name |
idx_scan | bigint |
idx_tup_read | bigint |
idx_tup_fetch | bigint |
pg_stat_user_indexes (PostgreSQL 9.1)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Modifiers
---------------+--------+-----------
relid | oid |
indexrelid | oid |
schemaname | name |
relname | name |
indexrelname | name |
idx_scan | bigint |
idx_tup_read | bigint |
idx_tup_fetch | bigint |
pg_stat_user_indexes (PostgreSQL 9.0)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Modifiers
---------------+--------+-----------
relid | oid |
indexrelid | oid |
schemaname | name |
relname | name |
indexrelname | name |
idx_scan | bigint |
idx_tup_read | bigint |
idx_tup_fetch | bigint |
pg_stat_user_indexes (PostgreSQL 8.4)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Modifiers
---------------+--------+-----------
relid | oid |
indexrelid | oid |
schemaname | name |
relname | name |
indexrelname | name |
idx_scan | bigint |
idx_tup_read | bigint |
idx_tup_fetch | bigint |
View definition:
SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch
FROM pg_stat_all_indexes
WHERE (pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND pg_stat_all_indexes.schemaname !~ '^pg_toast'::text;
pg_stat_user_indexes (PostgreSQL 8.3)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Modifiers
---------------+--------+-----------
relid | oid |
indexrelid | oid |
schemaname | name |
relname | name |
indexrelname | name |
idx_scan | bigint |
idx_tup_read | bigint |
idx_tup_fetch | bigint |
View definition:
SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch
FROM pg_stat_all_indexes
WHERE (pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND pg_stat_all_indexes.schemaname !~ '^pg_toast'::text;
pg_stat_user_indexes (PostgreSQL 8.2)
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Modifiers
---------------+--------+-----------
relid | oid |
indexrelid | oid |
schemaname | name |
relname | name |
indexrelname | name |
idx_scan | bigint |
idx_tup_read | bigint |
idx_tup_fetch | bigint |
View definition:
SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch
FROM pg_stat_all_indexes
WHERE pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'pg_toast'::name, 'information_schema'::name]);
变更历史
- PostgreSQL 19
- 添加了列
stats_reset（提交 a5b54325）
- 添加了列
- PostgreSQL 7.2
- 添加 (提交 8d80b0d9)
示例
pg_stat_user_indexes 的示例内容
postgres=# SELECT * FROM pg_stat_user_indexes WHERE relname = 'object_version' ORDER BY indexrelname; relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch -------+------------+------------+----------------+------------------------------------+-----------+--------------+--------------- 17064 | 17475 | public | object_version | object_version_latitude_ix | 113536 | 84047443 | 41516 17064 | 17476 | public | object_version | object_version_longitude_ix | 66825 | 66184961 | 9601 17064 | 17477 | public | object_version | object_version_ml_id_ix | 0 | 0 | 0 17064 | 17478 | public | object_version | object_version_obj_id_ix | 54760411 | 55981560 | 40519109 17064 | 17389 | public | object_version | object_version_object_id_key | 500645319 | 516918133 | 496099738 17064 | 17479 | public | object_version | object_version_object_status_id_ix | 2101 | 232612 | 176774 17064 | 17480 | public | object_version | object_version_owner_id_ix | 60 | 105 | 105 17064 | 17481 | public | object_version | object_version_parent_id_ix | 7 | 0 | 0 17064 | 17391 | public | object_version | object_version_pkey | 40742809 | 40994921 | 34044235 17064 | 17482 | public | object_version | object_version_tag_ix | 5569371 | 10668570 | 3591442 17064 | 17483 | public | object_version | object_version_ts_vector_ix | 71526 | 28091329 | 0 17064 | 17488 | public | object_version | ov_created_date_ix | 27043 | 477188 | 323429 17064 | 17489 | public | object_version | ov_created_ix | 9921 | 81380326 | 80593178 17064 | 17490 | public | object_version | ov_public_date_ix | 218433 | 1010681710 | 0
参考资料
- PostgreSQL 文档: pg_stat_user_indexes
有用链接
- 深入解析 PostgreSQL 统计信息：pg_stat_user_indexes - Data Egret 博客文章，2017 年 5 月