pt-index-usage

Summary

Displays the indexes usage.

Usage

pt-index-usage [option...]

Options

-h, --host=HOSTNAME
-p, --port=PORT
-U, --username=USERNAME
-d, --dbname=DBNAME
-o, --owner=STRING
-n, --schema=STRING
-t, --table=STRING
-i, --index=STRING
-u, --unused
--help

-h, --host Specifies the connecting the PostgreSQL database server name or its IP address. If not specified, to set the value of PGHOST. localhost will be used if no other value has been defined in PGHOST.

-p, --port Specifies the port number of the connecting PostgreSQL database. If not specified, to set the value of PGPORT. 5432 will be used if no value has been defined in PGPORT.

-U, --username Specifies the user name of the PostgreSQL database. If not specified, to set the value of PGUSER. The value of USER will be used If no other value has been defined in PGUSER.

-d, --dbname Specifies the connecting database name. If not specified, to set the value of PGDATABASE. Database name as database user name will be used if no other value has been defined in PGDATABASE.

-o, --owner The index information is only displayed which matches to a string specified where is a name of the owner.

-n, --schema The index information is only displayed which matches to a string specified where is in the schema.

-t, --table The index information is only displayed which matches to a string specified where is in the table.

-i, --index The index information is only displayed which matches to a string specified.

-u, --unused The index information is displayed which not use the index.

-d (or --dbname ), -o (or --owner ), -n (or --schema ), -t (or --table ), -i (or --index ), -u (or --unused ), if specified the options at the same time, the index is displayed which matches to it.

Output Items

OID Object ID of index
OWNER Owner name of index
SCHEMA Schema name on being indexed
TABLE Table name on being indexed
INDEX Index name
BLKS Block (every 8kb) of index
SCAN Index scan number of executions
T_READ Number of index entries by index scan
T_FTCH Number of tuple on table by index scan
B_READ Number of index blocks read from disk
B_HIT Number of index pages read from shared buffer
STATUS Index status. It reads from pg_index system table.
TABLESPACE The tablespace name that has index

Examples

For the index of table in the public schema, displays the usage situation.

$ pt-index-usage -n public -d postgres
+-------+-------+--------+------------------+-----------------------+------+------+--------+--------+--------+-------+--------+------------+
|  OID  | OWNER | SCHEMA |      TABLE       |         INDEX         | BLKS | SCAN | T_READ | T_FTCH | B_READ | B_HIT | STATUS | TABLESPACE |
+-------+-------+--------+------------------+-----------------------+------+------+--------+--------+--------+-------+--------+------------+
| 26793 | snaga | public | pgbench_accounts | pgbench_accounts_pkey |  276 |    1 |      1 |      1 |      4 |     0 |        | spc1       |
| 26789 | snaga | public | pgbench_branches | pgbench_branches_pkey |    2 |    1 |      1 |      0 |      2 |     0 |        | pg_default |
| 26791 | snaga | public | pgbench_tellers  | pgbench_tellers_pkey  |    2 |    0 |      0 |      0 |      0 |     0 |        | pg_default |
+-------+-------+--------+------------------+-----------------------+------+------+--------+--------+--------+-------+--------+------------+
$

For the index of pgbench_accounts table in the public schema, displays the usage situation.

$ pt-index-usage -n public -d postgres -t pgbench_accounts
+-------+-------+--------+------------------+-----------------------+------+------+--------+--------+--------+-------+--------+------------+
|  OID  | OWNER | SCHEMA |      TABLE       |         INDEX         | BLKS | SCAN | T_READ | T_FTCH | B_READ | B_HIT | STATUS | TABLESPACE |
+-------+-------+--------+------------------+-----------------------+------+------+--------+--------+--------+-------+--------+------------+
| 26793 | snaga | public | pgbench_accounts | pgbench_accounts_pkey |  276 |    1 |      1 |      1 |      4 |     0 |        | spc1       |
+-------+-------+--------+------------------+-----------------------+------+------+--------+--------+--------+-------+--------+------------+
$

For the index not used even once, in the public schema, displays the usage situation.

$ pt-index-usage -d postgres -n public -u
+-------+-------+--------+-----------------+----------------------+------+------+--------+--------+--------+-------+--------+------------+
|  OID  | OWNER | SCHEMA |      TABLE      |        INDEX         | BLKS | SCAN | T_READ | T_FTCH | B_READ | B_HIT | STATUS | TABLESPACE |
+-------+-------+--------+-----------------+----------------------+------+------+--------+--------+--------+-------+--------+------------+
| 26791 | snaga | public | pgbench_tellers | pgbench_tellers_pkey |    2 |    0 |      0 |      0 |      0 |     0 |        | pg_default |
+-------+-------+--------+-----------------+----------------------+------+------+--------+--------+--------+-------+--------+------------+
$