[Postgresql] 세팅 값 조회

• tuple 또는 item 은 row와 같은 뜻
• relation은 table과 같은 뜻
• filenode는 테이블 또는 인덱스를 나타내는 id
• block 또는 page는 같은 뜻으로 테이블에 저장되는 8KB 세그먼트 정보를 나타낸다.
• heap 또는 heap file은 정렬되지 않은 많은 사이즈의 레코드를 의미한다.
• CTID는 테이블 내의 특정 row의 물리적 위치를 나타낸다. 
• OID는 오브젝트 식별자로서 역할

PostgreSQL database location

select oid, datname from pg_database;

 

PostgreSQL table location 

1GB보다 더 커지게 되면 기가 세그먼트 단위로 쪼개지게 된다. 첫 번째는 기존과 같은 위치에 존재하고, 부속물들은 뒤에 suffix가 붙이게 된다.

select pg_relation_filepath('table_name');

PostgreSQL row location

모든 테이블은 고정된 크기(8kb)의 페이지의 배열로 저장한다. 모든 페이지는 논리적으로 동일하며, 어느 한 특정 row는 어느 페이지에서도 저장이 가능하다. 

테이블에 저장되는 구조는 힙 파일로 되어 있다. 

select ctid, * from <table_name>;

PostgreSQL Total Table

\dt

 

PostgreSQL tablespace

select * from pg_tablespace;

PostgreSQL segment size

select current_setting('segement_size');

 

PostgreSQL block size

select current_setting('block_size');

 

PostgreSQL Cache hit

select round(sum(blks_hit)*100/sum(blks_hit + blks_read),2) as "Buffer Cache Hit Ratio" from pg_stat_database;

 

PostgreSQL query history

\s

 

PostgreSQL TABLE SIZE

select table_schema,
       table_name,
       pg_size_pretty(pg_total_relation_size (format('%I.%I', table_schema, table_name)))
from information_schema.tables
where table_schema not in ('information_schema', 'pg_catalog')
order by pg_total_relation_size(format('%I.%I', table_schema, table_name)) desc;

PostgreSQL ROW Count

SELECT schemaname,relname,n_live_tup 
  FROM pg_stat_user_tables 
  ORDER BY n_live_tup DESC;

 

 

PostgreSQL Index 조회

\di

 

PostgreSQL Index Usage

SELECT 
  relname, 
  100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, 
  n_live_tup rows_in_table
FROM 
  pg_stat_user_tables
WHERE 
    seq_scan + idx_scan > 0 
ORDER BY 
  n_live_tup DESC;

 

PostgreSQL Index Cache Hit

 

SELECT 
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM 
  pg_statio_user_tables;

 

 

튜플 정보

SELECT 
c.relname,
pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,
pg_stat_get_live_tuples(c.oid) AS n_live_tup,
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size, pg_size_pretty(pg_relation_size(c.oid)) as rel_size 
FROM pg_class as c 
WHERE pg_stat_get_live_tuples(c.oid) > 0 
AND c.relname NOT LIKE 'pg_%' ORDER BY n_dead_tup DESC;

참고: https://www.slideshare.net/ssuser31f121/postgresql-vaccum?qid=6d6e9f7a-73a3-429c-9fc7-ecdc16444762&v=&b=&from_search=1