Display statistics #21

New issue

Open

opened 2018-11-10 12:47:57 +00:00 by eelke · 5 comments

eelke commented

2018-11-10 12:47:57 +00:00

(Migrated from gitlab.com)

There are several kinds of statistics

column statistics: which are about the values in the column pg_statistics or pg_stats(view)
table statistics
index statistics
sequence statistics

pg_stat_all/sys/user_tables: seq/idx scans, insert, updates and deletes
pg_stat_xact_all/sys/user_tables
pg_statio_all/sys/user_tables: block reads and hits
pg_stat_all/sys/user_indexes: scans reads and fetches
pg_statio_all/sys/user_indexes: reads and hits
pg_statio_all/sys/user_sequences: reads and hits
pg_stat_activity
pg_stat_replication
pg_stat_database: io, tuples etc
pg_stat_database_conflicts
pg_stat_user_functions
pg_stat_xact_user_functions
pg_stat_bgwriter
pg_stats
pg_statistic

Analytics

The raw numbers are nice but it would be nice if the program helped interpreting them.

calculate hit/miss ratio for cache
sequential scans vs index scans
try to weight importance of these values
- look at how much tuples are retrieved from the table compared to the database
- what is the size of the table compared to the database
- it is not a problem for a seldom read table to have a bad hit rate
- it is not a problem for a small table to have mostly sequential scans

What would a user want to know from the statistics

Most used tables (total or split out in select, insert, update and delete)
Use of indexes ie lookups and scans
Put index use into perspective with its table (end result should be that user knows if index is effective)

There are several kinds of statistics - column statistics: which are about the values in the column pg_statistics or pg_stats(view) - table statistics - index statistics - sequence statistics ## Statistics related views/tables: - pg_stat_all/sys/user_tables: seq/idx scans, insert, updates and deletes - pg_stat_xact_all/sys/user_tables - pg_statio_all/sys/user_tables: block reads and hits - pg_stat_all/sys/user_indexes: scans reads and fetches - pg_statio_all/sys/user_indexes: reads and hits - pg_statio_all/sys/user_sequences: reads and hits - pg_stat_activity - pg_stat_replication - pg_stat_database: io, tuples etc - pg_stat_database_conflicts - pg_stat_user_functions - pg_stat_xact_user_functions - pg_stat_bgwriter - pg_stats - pg_statistic ## Analytics The raw numbers are nice but it would be nice if the program helped interpreting them. - calculate hit/miss ratio for cache - sequential scans vs index scans - try to weight importance of these values - look at how much tuples are retrieved from the table compared to the database - what is the size of the table compared to the database - it is not a problem for a seldom read table to have a bad hit rate - it is not a problem for a small table to have mostly sequential scans ## What would a user want to know from the statistics - Most used tables (total or split out in select, insert, update and delete) - Use of indexes ie lookups and scans - Put index use into perspective with its table (end result should be that user knows if index is effective)

eelke commented

2018-11-10 13:07:34 +00:00

(Migrated from gitlab.com)

changed the description

eelke commented

2018-12-26 06:50:13 +00:00

(Migrated from gitlab.com)

changed milestone to %1

eelke commented

2018-12-28 08:21:34 +00:00

(Migrated from gitlab.com)

changed the description