PostgreSQL pg_stat_all_indexes pg_stat_sys_indexes 和 pg_stat_user_indexes 统计索引相关信息
PostgreSQL About 2,295 words区别
pg_stat_all_indexes：统计数据库中所有索引的信息。
pg_stat_sys_indexes：统计数据库中系统索引的信息。
pg_stat_user_indexes：统计数据库中用户自定义索引的信息。
含义
| 字段名称 | 类型 | 描述 |
|---|---|---|
| relid | oid | 索引所属表的对象ID。 |
| indexrelid | oid | 索引的对象ID。 |
| schemaname | name | 索引所属表的模式名。 |
| relname | name | 索引所属表的名称。 |
| indexrelname | name | 索引的名称。 |
| idx_scan | bigint | 索引的扫描次数。 |
| idx_tup_read | bigint | 从索引中读取的元组数。 |
| idx_tup_fetch | bigint | 使用索引扫描返回的元组数。 |
示例
select * from pg_stat_user_indexes
输出
postgres=# select * from pg_stat_user_indexes;
relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+---------------------+--------------------------+----------+--------------+---------------
30348 | 30396 | public | pgbench_accounts_2 | pgbench_accounts_2_pkey | 38373 | 245008 | 237701
30351 | 30398 | public | pgbench_accounts_3 | pgbench_accounts_3_pkey | 38679 | 245326 | 238251
30354 | 30400 | public | pgbench_accounts_4 | pgbench_accounts_4_pkey | 39223 | 245858 | 238551
30357 | 30402 | public | pgbench_accounts_5 | pgbench_accounts_5_pkey | 39001 | 245654 | 238695
30342 | 30388 | public | pgbench_branches | pgbench_branches_pkey | 117614 | 117912 | 117614
30336 | 30390 | public | pgbench_tellers | pgbench_tellers_pkey | 188792 | 189034 | 188792
30345 | 30394 | public | pgbench_accounts_1 | pgbench_accounts_1_pkey | 38739 | 245382 | 238189
30360 | 30404 | public | pgbench_accounts_6 | pgbench_accounts_6_pkey | 38973 | 245624 | 238606
30363 | 30406 | public | pgbench_accounts_7 | pgbench_accounts_7_pkey | 38831 | 245476 | 238218
30366 | 30408 | public | pgbench_accounts_8 | pgbench_accounts_8_pkey | 39065 | 245706 | 238576
30369 | 30410 | public | pgbench_accounts_9 | pgbench_accounts_9_pkey | 38929 | 245576 | 238440
30372 | 30412 | public | pgbench_accounts_10 | pgbench_accounts_10_pkey | 39535 | 246182 | 238985
(12 rows)
官方文档
https://www.postgresql.org/docs/17/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW
Views: 77 · Posted: 2025-02-12
———— END ————
Give me a Star, Thanks:)
https://github.com/fendoudebb/LiteNote扫描下方二维码关注公众号和小程序↓↓↓
Loading...