pg_class handles information on views and indexes as well as tables.
pg_class always contains the latest information.
"reltuples", a column in pg_class, stores the estimated number of rows in the table.
pg_stats is a table that displays column statistics.
Because some of the information accessible through pg_stats includes actual data, it cannot be accessed by regular users.
Only one row of operational statistics for the entire database cluster is stored.
"blks_hit" stores the number of blocks retrieved from the buffer cache (cache hits).
"blks_read" is the sum of the number of blocks read from disk and the number of blocks read from the buffer cache.
By default, the "track_counts" parameter is "off", so no activity statistics are collected.
"tup_fetched" contains the number of rows fetched using index scans.
From pg_stat_all_tables, the number of disk blocks read from the TOAST table can be obtained.
From pg_stat_activity, the start time of the current query execution can be obtained.
From pg_stat_database, the number of errors that have occurred in the target database can be obtained.
From pg_statio_all_tables, the number of buffer hits for the target table can be obtained.
From pg_stat_all_tables, the number of disk blocks read from the TOAST table can be obtained.
From pg_stat_activity, the start time of the current query execution can be obtained.
From pg_stat_database, the number of errors that have occurred in the target database can be obtained.
From pg_statio_all_tables, the number of buffer hits for the target table can be obtained.
DELETE
DROP TABLE
REINDEX
PREPARE
EXECUTE
JSON
HTML
CSV
YAML
XML
Database
Relation
Tuple
Column
Parameter
SELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_database;
SELECT * FROM pg_stat_bgwriter;
SELECT * FROM pg_stat_all_tables;
SELECT * FROM pg_stat_all_indexes;
The relpages column stores the number of rows in the table.
pg_class stores only information about tables.
The latest information is always stored in pg_class.
The value in the relpages column is an estimate.
Statistics stored in pg_class are updated by certain DDL commands.
It records execution time spent for SQL statements that exceed a specified threshold.
It identifies transactions and target tables currently waiting for locks.
It records execution statistics for all executed SQL statements.
It displays overall database statistics, with one row per database.
It displays table access statistics, with one row per table in the current database.
postgres=# EXPLAIN SELECT relname,nspname FROM pg_class left join pg_namespace ON (pg_class.relnamespace = pg_namespace.oid);
QUERY PLAN
-------------------------------------------------------------------------
Hash Left Join (cost=1.14..15.97 rows=288 width=128)
Hash Cond: (pg_class.relnamespace = pg_namespace.oid)
-> Seq Scan on pg_class (cost=0.00..10.88 rows=288 width=68)
-> Hash (cost=1.06..1.06 rows=6 width=68)
-> Seq Scan on pg_namespace (cost=0.00..1.06 rows=6 width=68)
(5 rows)
Executing this EXPLAIN command actually executes the SQL statement specified in the argument.
The descriptions "Hash Left Join", "Seq Scan on pg_class", and "Seq Scan on pg_namespace" in the plan nodes represent the optimal data processing algorithms chosen automatically by the query planner.
The "cost=..." portion represents the actual time required for processing.
The "rows=..." portion represents the estimated number of rows that each plan node will process.
The "width=..." portion represents the estimated average width (in bytes) of a row based on statistics.
© EDUCO (General Incorporated Association Educo) all rights reserved.