【PostgreSQL的指标采集工具--pgmetrics】
pgmetrics是用go语言写的一款PostgreSQL的健康监控指标采集软件。可以连接到数据库，获取当前数据库的相关信息，如果连接的是本地数据库，则同时会采集服务器的状态信息。可以把采集的结果以json或者text或者csv的形式存储。
1.下载pgmetrics的软件
https://github.com/rapidloop/pgmetrics/releases
postgres@ubuntu-linux-22-04-desktop:~$ ll pgmetrics_1.16.0_linux_arm64.tar.gz
-rw-r--r-- 1 postgres postgres 4361437 Mar 19 15:37 pgmetrics_1.16.0_linux_arm64.tar.gz
postgres@ubuntu-linux-22-04-desktop:~$ tar -xf pgmetrics_1.16.0_linux_arm64.tar.gz
postgres@ubuntu-linux-22-04-desktop:~$ cd pgmetrics_1.16.0_linux_arm64/
postgres@ubuntu-linux-22-04-desktop:~/pgmetrics_1.16.0_linux_arm64$ ll
total 14872
drwxrwxr-x 2 postgres postgres 4096 Mar 19 15:37 ./
drwxr-x--- 21 postgres postgres 4096 Mar 19 15:37 ../
-rw-r--r-- 1 postgres postgres 11357 Feb 3 2023 LICENSE
-rw-r--r-- 1 postgres postgres 377 Feb 3 2023 README.md
-rwxr-xr-x 1 postgres postgres 15204352 Oct 4 12:13 pgmetrics*
2.pgmetrics的选项
postgres@ubuntu-linux-22-04-desktop:~/pgmetrics_1.16.0_linux_arm64$ ./pgmetrics --help
pgmetrics collects PostgreSQL information and metrics.
Usage:
pgmetrics [OPTION]... [DBNAME]
General options:
-t, --timeout=SECS individual query timeout in seconds (default: 5)
--lock-timeout=MILLIS lock timeout in milliseconds (default: 50)
-i, --input=FILE don't connect to db, instead read and display
this previously saved JSON file
-V, --version output version information, then exit
-?, --help[=options] show this help, then exit
--help=variables list environment variables, then exit
Collection options:
-S, --no-sizes don't collect tablespace and relation sizes
-c, --schema=REGEXP collect only from schema(s) matching POSIX regexp
-C, --exclude-schema=REGEXP do NOT collect from schema(s) matching POSIX regexp
-a, --table=REGEXP collect only from table(s) matching POSIX regexp
-A, --exclude-table=REGEXP do NOT collect from table(s) matching POSIX regexp
--omit=WHAT do NOT collect the items specified as a comma-separated
list of: "tables", "indexes", "sequences",
"functions", "extensions", "triggers",
"statements", "log", "citus", "indexdefs",
"bloat"
--sql-length=LIMIT collect only first LIMIT characters of all SQL
queries (default: 500)
--statements-limit=LIMIT collect only utmost LIMIT number of row from
pg_stat_statements (default: 100)
--only-listed collect info only from the databases listed as
command-line args (use with Heroku)
--all-dbs collect info from all user databases
--log-file location of PostgreSQL log file
--log-dir read all the PostgreSQL log files in this directory
--log-span=MINS examine the last MINS minutes of logs (default: 5)
--aws-rds-dbid AWS RDS/Aurora database instance identifier
--az-resource Azure resource ID
--pgpool collect only Pgpool metrics
Output options:
-f, --format=FORMAT output format; "human", "json" or "csv" (default: "human")
-l, --toolong=SECS for human output, transactions running longer than
this are considered too long (default: 60)
-o, --output=FILE write output to the specified file
--no-pager do not invoke the pager for tty output
Connection options:
-h, --host=HOSTNAME database server host or socket directory
(default: "/var/run/postgresql")
-p, --port=PORT database server port (default: 5432)
-U, --username=USERNAME database user name (default: "postgres")
-w, --no-password never prompt for password
--role=ROLE do SET ROLE before collection
For more information, visit <https://pgmetrics.io>.
3.使用案例
设置超时时间为3s，避免阻塞业务。
postgres@ubuntu-linux-22-04-desktop:~/pgmetrics_1.16.0_linux_arm64$ ./pgmetrics -t 3 --no-password --no-pager -h /tmp -o ./PostgreSQL_Checklog_`date +"%Y-
%m-%d-%H%M%S"`.log postgres test_upgrade
pgmetrics: warning: failed to guess log file location/access denied, specify explicitly with --log-file or --log-dir
postgres@ubuntu-linux-22-04-desktop:~/pgmetrics_1.16.0_linux_arm64$ ll
total 14912
drwxrwxr-x 2 postgres postgres 4096 Mar 19 15:46 ./
drwxr-x--- 21 postgres postgres 4096 Mar 19 15:42 ../
-rw-r--r-- 1 postgres postgres 11357 Feb 3 2023 LICENSE
-rw-rw-r-- 1 postgres postgres 37143 Mar 19 15:46 PostgreSQL_Checklog_2024-03-19-154620.log
-rw-r--r-- 1 postgres postgres 377 Feb 3 2023 README.md
-rwxr-xr-x 1 postgres postgres 15204352 Oct 4 12:13 pgmetrics*
报告的内容如下
pgmetrics run at: 19 Mar 2024 3:46:20 PM (now)
PostgreSQL Cluster:
Name:
Server Version: 16.1
Server Started: 19 Mar 2024 3:22:34 PM (23 minutes ago)
System Identifier: 7316572678719819371
Timeline: 1
Last Checkpoint: 19 Mar 2024 3:22:34 PM (23 minutes ago)
REDO LSN: 0/3D1DA560
Checkpoint LSN: 0/3D1DA560 (0 B since REDO)
Transaction IDs: oldest = 726, next = 2189, range = 1463
Notification Queue: 0.0% used
Active Backends: 1 (max 100)
Recovery Mode? no
System Information:
Hostname: ubuntu-linux-22-04-desktop
CPU Cores: 0 x
Load Average: 0.28
Memory: used=707 MiB, free=218 MiB, buff=251 MiB, cache=564 MiB
Swap: used=167 MiB, free=1.8 GiB
+---------------------------------+-----------------+
| Setting | Value |
+---------------------------------+-----------------+
| shared_buffers | 16384 (128 MiB) |
| work_mem | 4096 (4.0 MiB) |
| maintenance_work_mem | 65536 (64 MiB) |
| temp_buffers | 1024 (8.0 MiB) |
| autovacuum_work_mem | -1 |
| temp_file_limit | -1 |
| max_worker_processes | 8 |
| autovacuum_max_workers | 3 |
| max_parallel_workers_per_gather | 2 |
| effective_io_concurrency | 1 |
+---------------------------------+-----------------+
WAL Files:
WAL Archiving? no
WAL Files: 3
+--------------------+----------------+
| Setting | Value |
+--------------------+----------------+
| wal_level | replica |
| archive_timeout | 0 |
| wal_compression | off |
| max_wal_size | 1024 (1.0 GiB) |
| min_wal_size | 80 (80 MiB) |
| checkpoint_timeout | 300 |
| full_page_writes | on |
| wal_keep_size | 0 |
+--------------------+----------------+
BG Writer:
Checkpoint Rate: 0.21 per min
Average Write: 0 B per checkpoint
Total Checkpoints: 4 sched (80.0%) + 1 req (20.0%) = 5
Total Write: 0 B, @ 0 B per sec
Buffers Allocated: 842 (6.6 MiB)
Buffers Written: 0 chkpt (0.0%) + 0 bgw (0.0%) + 0 be (0.0%)
Clean Scan Stops: 0
BE fsyncs: 0
Counts Since: 19 Mar 2024 3:22:34 PM (23 minutes ago)
+------------------------------+--------------+
| Setting | Value |
+------------------------------+--------------+
| bgwriter_delay | 200 msec |
| bgwriter_flush_after | 64 (512 KiB) |
| bgwriter_lru_maxpages | 100 |
| bgwriter_lru_multiplier | 2 |
| block_size | 8192 |
| checkpoint_timeout | 300 sec |
| checkpoint_completion_target | 0.9 |
+------------------------------+--------------+
Backends:
Total Backends: 1 (1.0% of max 100)
Problematic: 0 waiting on locks, 0 waiting on other, 0 xact too long, 0 idle in xact
Locks:
+------------+-------------+-------+
| Lock Type | Not Granted | Total |
+------------+-------------+-------+
| relation | 0 | 4 |
| virtualxid | 0 | 1 |
+------------+-------------+-------+
| | 0 | 5 |
+------------+-------------+-------+
Vacuum Progress:
No manual or auto vacuum jobs in progress.
+------------------------------+----------------+
| Setting | Value |
+------------------------------+----------------+
| maintenance_work_mem | 65536 (64 MiB) |
| autovacuum | on |
| autovacuum_analyze_threshold | 50 |
| autovacuum_vacuum_threshold | 50 |
| autovacuum_freeze_max_age | 200000000 |
| autovacuum_max_workers | 3 |
| autovacuum_naptime | 60 sec |
| vacuum_freeze_min_age | 50000000 |
| vacuum_freeze_table_age | 150000000 |
+------------------------------+----------------+
Roles:
+-----------------------------+-------+------+-------+-----------+----------+------------+---------+-------------------------+-------------------------
-------------------------------------+
| Name | Login | Repl | Super | Creat Rol | Creat DB | Bypass RLS | Inherit | Expires |
Member Of |
+-----------------------------+-------+------+-------+-----------+----------+------------+---------+-------------------------+-------------------------
-------------------------------------+
| postgres | yes | yes | yes | yes | yes | yes | yes | |
|
| pg_monitor | | | | | | | yes | | pg_read_all_settings, pg
_read_all_stats, pg_stat_scan_tables |
| pg_read_all_settings | | | | | | | yes | |
|
| pg_read_all_stats | | | | | | | yes | |
|
| pg_stat_scan_tables | | | | | | | yes | |
|
| pg_signal_backend | | | | | | | yes | |
|
| pg_checkpoint | | | | | | | yes | |
|
| pg_use_reserved_connections | | | | | | | yes | |
|
| pg_read_server_files | | | | | | | yes | |
|
| pg_write_server_files | | | | | | | yes | |
|
| pg_execute_server_program | | | | | | | yes | |
|
| pg_database_owner | | | | | | | yes | |
|
| pg_read_all_data | | | | | | | yes | |
|
| pg_write_all_data | | | | | | | yes | |
|
| pg_create_subscription | | | | | | | yes | |
|
| repl | yes | yes | | | | | yes | |
|
| ysla | yes | | | | | | yes | |
|
| test10 | yes | | | | | | yes | 17 Aug 2024 12:40:09 PM |
|
| test11 | yes | | | | | | yes | 23 Feb 2024 1:57:19 PM |
|
+-----------------------------+-------+------+-------+-----------+----------+------------+---------+-------------------------+-------------------------
-------------------------------------+
Tablespaces:
+------------+----------+----------------------------------+---------+--------------------------+---------------------------+
| Name | Owner | Location | Size | Disk Used | Inode Used |
+------------+----------+----------------------------------+---------+--------------------------+---------------------------+
| pg_default | postgres | $PGDATA = /home/postgres/data-16 | 33 MiB | 27 GiB (43.7%) of 62 GiB | 419716 (10.2%) of 4128768 |
| pg_global | postgres | $PGDATA = /home/postgres/data-16 | 588 KiB | 27 GiB (43.7%) of 62 GiB | 419716 (10.2%) of 4128768 |
+------------+----------+----------------------------------+---------+--------------------------+---------------------------+
Database #1:
Name: postgres
Owner: postgres
Tablespace: pg_default
Connections: 1 (no max limit)
Frozen Xid Age: 1463
Transactions: 71 (100.0%) commits, 0 (0.0%) rollbacks
Cache Hits: 96.0%
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
Total Temp: 0 B in 0 files
Problems: 0 deadlocks, 0 conflicts
Totals Since:
Size: 12 MiB
Sequences:
+------------------------------+------------+
| Sequence | Cache Hits |
+------------------------------+------------+
| hints_id_seq | |
| student_student_id_seq | |
| test_vaccum_index_on_id_seq | |
| test_vaccum_index_off_id_seq | |
+------------------------------+------------+
Installed Extensions:
+--------------------+---------+------------------------------------------------------------------------+
| Name | Version | Comment |
+--------------------+---------+------------------------------------------------------------------------+
| pg_bigm | 1.2 | text similarity measurement and index searching based on bigrams |
| pg_hint_plan | 1.4.2 | |
| pg_stat_statements | 1.9 | track planning and execution statistics of all SQL statements executed |
| pg_trgm | 1.6 | text similarity measurement and index searching based on trigrams |
| pgcrypto | 1.3 | cryptographic functions |
| plpgsql | 1.0 | PL/pgSQL procedural language |
+--------------------+---------+------------------------------------------------------------------------+
Slow Queries:
+-------+----------+------------+-----------+----------------------------------------------------+
| Calls | Avg Time | Total Time | Rows/Call | Query |
+-------+----------+------------+-----------+----------------------------------------------------+
| 4 | 4ms | 17ms | 1 | SELECT pg_database_size($1) |
| 4 | 3ms | 13ms | 1 | SELECT pg_tablespace_size($1) |
| 1 | 9ms | 9ms | 9 | SELECT current_database() AS db, schemaname, tab |
| 2 | 2ms | 5ms | 378 | SELECT name, setting, COALESCE(boot_val,$1), sourc |
| 2 | 2ms | 4ms | 19 | SELECT S.relid, S.schemaname, S.relname, current_d |
| 2 | 1ms | 3ms | 0 | SELECT funcid, schemaname, funcname, current_datab |
| 2 | 1ms | 2ms | 6 | SELECT name, current_database(), COALESCE(default_ |
| 2 | 0s | 1ms | 1 | SELECT EXTRACT($1 FROM pg_postmaster_start_time()) |
| 2 | 0s | 0s | 1 | SELECT COALESCE(datname, $2), COALESCE(usename, $3 |
| 1 | 0s | 0s | 4 | SELECT d.datname as "Name", pg_catalog.pg_get_ |
| 2 | 0s | 0s | 5 | SELECT COALESCE(D.datname, $1), L.locktype, L.mode |
| 2 | 0s | 0s | 12 | SELECT S.relid, S.indexrelid, S.schemaname, S.reln |
| 2 | 0s | 0s | 12 | SELECT indexrelid, pg_get_indexdef(indexrelid) FRO |
| 1 | 0s | 0s | 44 | SELECT userid, dbid, queryid, LEFT(COALESCE(query, |
| 2 | 0s | 0s | 3 | SELECT name FROM pg_ls_waldir() WHERE name ~ $1 |
| 2 | 0s | 0s | 1 | SELECT checkpoints_timed, checkpoints_req, checkpo |
| 2 | 0s | 0s | 19 | SELECT R.oid, R.rolname, R.rolsuper, R.rolinherit, |
| 2 | 0s | 0s | 2 | SELECT c.oid, i.inhparent::regclass FROM pg_cla |
| 2 | 0s | 0s | 0 | SELECT c.oid, inhparent::regclass, COALESCE(pg_get |
| 2 | 0s | 0s | 0 | SELECT COALESCE(usename, $1), application_name, |
| 2 | 0s | 0s | 1 | SELECT checkpoint_lsn, redo_lsn, timeline_id, n |
| 2 | 0s | 0s | 2 | SELECT oid, spcname, pg_get_userbyid(spcowner), |
| 2 | 0s | 0s | 2 | SELECT D.oid, D.datname, D.datdba, D.dattablespace |
| 2 | 0s | 0s | 1 | SELECT system_identifier FROM pg_control_system() |
| 2 | 0s | 0s | 1 | SELECT pg_is_in_recovery(), COALESCE(pg_last_wa |
| 2 | 0s | 0s | 1 | SELECT archived_count, COALESCE(last_archived_ |
| 2 | 0s | 0s | 4 | SELECT relid, schemaname, relname, current_databas |
| 2 | 0s | 0s | 1 | SELECT current_user |
| 2 | 0s | 0s | 6 | SELECT backend_type, count(*) FROM pg_stat_activit |
| 2 | 0s | 0s | 1 | SELECT COUNT(*) FROM pg_ls_archive_statusdir() WHE |
| 2 | 0s | 0s | 0 | SELECT pid, datname, relid::int, COALESCE(command, |
| 2 | 0s | 0s | 0 | WITH P AS (SELECT DISTINCT pid FROM pg_locks WHERE |
| 2 | 0s | 0s | 0 | SELECT status, receive_start_lsn, receive_start_tl |
| 2 | 0s | 0s | 1 | SELECT wal_records, wal_fpi, wal_bytes, wal_buffer |
| 2 | 0s | 0s | 0 | SELECT slot_name, COALESCE(plugin, $1), slot_type, |
| 2 | 0s | 0s | 1 | SELECT COALESCE(pg_current_logfile($1),$2) |
| 2 | 0s | 0s | 1 | SELECT pg_notification_queue_usage() |
| 2 | 0s | 0s | 0 | SELECT pid, datname, COALESCE(relid, $1), COALESCE |
| 2 | 0s | 0s | 1 | SELECT COALESCE(inet_client_addr() = inet_server_a |
| 2 | 0s | 0s | 0 | SELECT pid, datname, relid::int, COALESCE(command, |
| 2 | 0s | 0s | 0 | SELECT pid, datname, COALESCE(relid::int, $1::int) |
| 2 | 0s | 0s | 0 | SELECT pid, datname, relid::int, index_relid::int, |
| 2 | 0s | 0s | 0 | SELECT pid, COALESCE(phase, $1), COALESCE(bac |
| 2 | 0s | 0s | 1 | SELECT pg_current_wal_flush_lsn(), pg_current_ |
| 2 | 0s | 0s | 1 | SELECT current_database() |
| 2 | 0s | 0s | 0 | SELECT T.oid, T.tgrelid, T.tgname, P.proname F |
| 1 | 0s | 0s | 0 | WITH pc AS (SELECT pubname, COUNT(*) AS c FROM pg_ |
| 1 | 0s | 0s | 0 | WITH sc AS (SELECT srsubid, COUNT(*) AS c FROM |
+-------+----------+------------+-----------+----------------------------------------------------+
Database #2:
Name: test_upgrade
Owner: postgres
Tablespace: pg_default
Connections: 0 (no max limit)
Frozen Xid Age: 1463
Transactions: 18 (100.0%) commits, 0 (0.0%) rollbacks
Cache Hits: 95.6%
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
Total Temp: 0 B in 0 files
Problems: 0 deadlocks, 0 conflicts
Totals Since:
Size: 7.3 MiB
Installed Extensions:
+---------+---------+-------------------------------------------------------------------+
| Name | Version | Comment |
+---------+---------+-------------------------------------------------------------------+
| pg_bigm | 1.2 | text similarity measurement and index searching based on bigrams |
| pg_trgm | 1.6 | text similarity measurement and index searching based on trigrams |
| plpgsql | 1.0 | PL/pgSQL procedural language |
+---------+---------+-------------------------------------------------------------------+
Slow Queries:
+-------+----------+------------+-----------+----------------------------------------------------+
| Calls | Avg Time | Total Time | Rows/Call | Query |
+-------+----------+------------+-----------+----------------------------------------------------+
| 1 | 7ms | 7ms | 7 | SELECT current_database() AS db, schemaname, tab |
| 1 | 3ms | 3ms | 0 | SELECT funcid, schemaname, funcname, current_datab |
| 1 | 0s | 0s | 2 | SELECT S.relid, S.schemaname, S.relname, current_d |
| 1 | 0s | 0s | 3 | SELECT name, current_database(), COALESCE(default_ |
| 1 | 0s | 0s | 0 | SELECT S.relid, S.indexrelid, S.schemaname, S.reln |
| 1 | 0s | 0s | 0 | SELECT indexrelid, pg_get_indexdef(indexrelid) FRO |
| 1 | 0s | 0s | 0 | SELECT relid, schemaname, relname, current_databas |
| 1 | 0s | 0s | 1 | SELECT current_database() |
| 1 | 0s | 0s | 0 | WITH pc AS (SELECT pubname, COUNT(*) AS c FROM pg_ |
| 1 | 0s | 0s | 0 | WITH sc AS (SELECT srsubid, COUNT(*) AS c FROM |
| 1 | 0s | 0s | 0 | SELECT T.oid, T.tgrelid, T.tgname, P.proname F |
| 1 | 0s | 0s | 0 | SELECT c.oid, inhparent::regclass, COALESCE(pg_get |
| 1 | 0s | 0s | 0 | SELECT c.oid, i.inhparent::regclass FROM pg_cla |
+-------+----------+------------+-----------+----------------------------------------------------+
Table #1 in "postgres":
Name: postgres.hint_plan.hints
Columns: 4
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 8.0 KiB
ACL:
+----------+---------------------------------------------------------------+------------+
| Role | Privileges | Granted By |
+----------+---------------------------------------------------------------+------------+
| postgres | INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER | postgres |
| PUBLIC | SELECT | postgres |
+----------+---------------------------------------------------------------+------------+
+--------------------+-------+---------+-------+------------+-------+----------------+-------------------+
| Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |
+--------------------+-------+---------+-------+------------+-------+----------------+-------------------+
| hints_pkey | btree | 8.0 KiB | | | 0 | 0.0 | 0.0 |
| hints_norm_and_app | btree | 8.0 KiB | | | 0 | 0.0 | 0.0 |
+--------------------+-------+---------+-------+------------+-------+----------------+-------------------+
Table #2 in "postgres":
Name: postgres.public.tab_test_1
Columns: 1
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 8.0 KiB
+-------+-------+--------+-------+------------+-------+----------------+-------------------+
| Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |
+-------+-------+--------+-------+------------+-------+----------------+-------------------+
| idx_1 | btree | 16 KiB | | | 0 | 0.0 | 0.0 |
+-------+-------+--------+-------+------------+-------+----------------+-------------------+
Table #3 in "postgres":
Name: postgres.public.tab_test_2
Columns: 1
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 8.0 KiB
Table #4 in "postgres":
Name: postgres.public.tab_testdump
Columns: 3
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 16 KiB
Table #5 in "postgres":
Name: postgres.public.tab_1
Columns: 3
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 16 KiB
+-------+-------+--------+-------+------------+-------+----------------+-------------------+
| Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |
+-------+-------+--------+-------+------------+-------+----------------+-------------------+
| idx1 | btree | 16 KiB | | | 0 | 0.0 | 0.0 |
| idx2 | btree | 16 KiB | | | 0 | 0.0 | 0.0 |
+-------+-------+--------+-------+------------+-------+----------------+-------------------+
Table #6 in "postgres":
Name: postgres.public.test_default_1
Columns: 2
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 8.0 KiB
Table #7 in "postgres":
Name: postgres.public.test_default_2
Columns: 2
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 8.0 KiB
Table #8 in "postgres":
Name: postgres.public.test_default_3
Columns: 2
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 16 KiB
Table #9 in "postgres":
Name: postgres.public.tab_xmaster_ysl
Columns: 2
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 8.0 KiB
Table #10 in "postgres":
Name: postgres.public.student
Columns: 3
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 0 B
Table #11 in "postgres":
Name: postgres.public.student_qualified
Inherits from: student
Columns: 3
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 0 B
Table #12 in "postgres":
Name: postgres.public.student_nqualified
Inherits from: student
Columns: 3
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 0 B
Table #13 in "postgres":
Name: postgres.public.test_vaccum_index_on
Columns: 3
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 48 KiB
+----------------------------+-------+--------+-------+------------+-------+----------------+-------------------+
| Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |
+----------------------------+-------+--------+-------+------------+-------+----------------+-------------------+
| test_vaccum_index_on_pkey | btree | 16 KiB | | | 0 | 0.0 | 0.0 |
| test_vaccum_index_on_x_key | btree | 16 KiB | | | 0 | 0.0 | 0.0 |
| z | btree | 16 KiB | | | 0 | 0.0 | 0.0 |
+----------------------------+-------+--------+-------+------------+-------+----------------+-------------------+
Table #14 in "postgres":
Name: postgres.public.test_vaccum_index_off
Columns: 3
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 48 KiB
+-----------------------------+-------+--------+-------+------------+-------+----------------+-------------------+
| Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |
+-----------------------------+-------+--------+-------+------------+-------+----------------+-------------------+
| test_vaccum_index_off_pkey | btree | 16 KiB | | | 0 | 0.0 | 0.0 |
| test_vaccum_index_off_x_key | btree | 16 KiB | | | 0 | 0.0 | 0.0 |
| z2 | btree | 16 KiB | | | 0 | 0.0 | 0.0 |
+-----------------------------+-------+--------+-------+------------+-------+----------------+-------------------+
Table #15 in "postgres":
Name: postgres.public.test
Columns: 1
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 1.8 MiB
Bloat: 888 KiB (49.1%)
+-----------+-------+---------+-----------------+------------+-------+----------------+-------------------+
| Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |
+-----------+-------+---------+-----------------+------------+-------+----------------+-------------------+
| test_pkey | btree | 2.2 MiB | 1.4 MiB (64.5%) | | 0 | 0.0 | 0.0 |
+-----------+-------+---------+-----------------+------------+-------+----------------+-------------------+
Table #16 in "postgres":
Name: postgres.public.t
Columns: 1
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 8.0 KiB
Table #17 in "postgres":
Name: postgres.public.test_data
Columns: 1
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 8.0 KiB
Table #18 in "postgres":
Name: postgres.public.t2
Columns: 1
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 16 KiB
Table #19 in "postgres":
Name: postgres.public.t3
Columns: 1
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 16 KiB
Table #1 in "test_upgrade":
Name: test_upgrade.public.test01
Columns: 2
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 8.0 KiB
Table #2 in "test_upgrade":
Name: test_upgrade.public.txa
Columns: 1
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 0.0% est. rows modified
Row Estimate: 0.0% live of total 0
Rows Changed: ins 0.0%, upd 0.0%, del 0.0%
HOT Updates: 0.0% of all updates
Seq Scans: 0, 0.0 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 0.0% (idx=0.0%)
Size: 0 B
原文地址：https://blog.csdn.net/weixin_47308871/article/details/136867720
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！