自学内容网 自学内容网

【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)!