Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL pg_stat_all_indexes and pg_stat_all_tables View


  • The pg_stat_all_indexes view will contain one row for each index in the current database, showing statistics about accesses to that specific index. The pg_stat_user_indexes and pg_stat_sys_indexes views contain the same information, but filtered to only show user and system indexes respectively.
  • Indexes can be used via either simple index scans or "bitmap" index scans. In a bitmap scan the output of several indexes can be combined via AND or OR rules, so it is difficult to associate individual heap row fetches with specific indexes when a bitmap scan is used. Therefore, a bitmap scan increments the pg_stat_all_indexes.idx_tup_read count(s) for the index(es) it uses, and it increments the pg_stat_all_tables.idx_tup_fetch count for the table, but it does not affect pg_stat_all_indexes.idx_tup_fetch.
  • Note: The idx_tup_read and idx_tup_fetch counts can be different even without any use of bitmap scans, because idx_tup_read counts index entries retrieved from the index while idx_tup_fetch counts live rows fetched from the table. The latter will be less if any dead or not-yet-committed rows are fetched using the index, or if any heap fetches are avoided by means of an index-only scan.
Column
Type
Description
relid
oid
OID of the table for this index
indexrelid
oid
OID of this index
schemaname
name
Name of the schema this index is in
relname
name
Name of the table for this index
indexrelname
name
Name of this index
idx_scan
bigint
Number of index scans initiated on this index
idx_tup_read
bigint
Number of index entries returned by scans on this index
idx_tup_fetch
bigint
Number of live table rows fetched by simple index scans using this index
The pg_stat_all_tables view will contain one row for each table in the current database (including TOAST tables), showing statistics about accesses to that specific table. The pg_stat_user_tables and pg_stat_sys_tables views contain the same information, but filtered to only show user and system tables respectively.
Column
Type
Description
relid
oid
OID of a table
schemaname
name
Name of the schema that this table is in
relname
name
Name of this table
seq_scan
bigint
Number of sequential scans initiated on this table
seq_tup_read
bigint
Number of live rows fetched by sequential scans
idx_scan
bigint
Number of index scans initiated on this table
idx_tup_fetch
bigint
Number of live rows fetched by index scans
n_tup_ins
bigint
Number of rows inserted
n_tup_upd
bigint
Number of rows updated
n_tup_del
bigint
Number of rows deleted
n_tup_hot_upd
bigint
Number of rows HOT updated (i.e., with no separate index update required)
n_live_tup
bigint
Estimated number of live rows
n_dead_tup
bigint
Estimated number of dead rows
last_vacuum
timestamp with time zone
Last time at which this table was manually vacuumed (not counting VACUUM FULL)
last_autovacuum
timestamp with time zone
Last time at which this table was vacuumed by the autovacuum daemon
last_analyze
timestamp with time zone
Last time at which this table was manually analyzed
last_autoanalyze
timestamp with time zone
Last time at which this table was analyzed by the autovacuum daemon
vacuum_count
bigint
Number of times this table has been manually vacuumed (not counting VACUUM FULL)
autovacuum_count
bigint
Number of times this table has been vacuumed by the autovacuum daemon
analyze_count
bigint
Number of times this table has been manually analyzed
autoanalyze_count
bigint
Number of times this table has been analyzed by the autovacuum daemon

TO check that table whether having any index or not  and check the index status
--to checking table index:
postgres=# select relname,indexrelname,indexrelid from pg_stat_all_indexes where indexrelid=24700;
 relname | indexrelname | indexrelid 
---------+--------------+------------
 k       | production   |      24700 
--check the index is valid or not and check the index is alive or not
postgres=# select indexrelid,indislive,indisvalid from pg_index where indexrelid=24700;
 indexrelid | indislive | indisvalid 
------------+-----------+------------
      24700 | t         | t
(1 row)                                       
--Checking the tables dead tubles and analyze 
postgres=# select relname,last_vacuum,n_dead_tup,last_analyze 
from pg_stat_all_tables where relname='k';

 relname | last_vacuum | n_dead_tup | last_analyze 
---------+-------------+------------+--------------
 k       |             |       8192 |    

Comments

Popular posts from this blog

PostgreSQL pgBadger

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Database startup / shutdown /restart