postgres of sql

1.query how large space the table takes up in one schema.

SELECT
        tablename,
        pg_size_pretty(pg_relation_size(schemaname || '.' || tablename))
FROM
        pg_tables
WHERE
        schemaname = 'public';

 tablename | pg_size_pretty
-----------+----------------
 t         | 64 kB
 person    | 8192 bytes
 t1        | 228 MB
 emp       | 8192 bytes
(4 rows)

learn come from:http://postgresql.1045698.n5.nabble.com/pg-relation-size-relation-does-not-exist-td1920236.html

2.query how large space the table takes up and the index takes up and so on  in one schema

SELECT
schemaname,
tablename,
pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS size_p,
pg_total_relation_size(schemaname || '.' || tablename) AS siz,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size_p,
pg_total_relation_size(schemaname || '.' || tablename) - pg_relation_size(schemaname || '.' || tablename) AS index_size,
(100*(pg_total_relation_size(schemaname || '.' || tablename) - pg_relation_size(schemaname || '.' || tablename)))/CASE WHEN pg_total_relation_size(schemaname || '.' || tablename) = 0 THEN 1 ELSE pg_total_relation_size(schemaname || '.' || tablename) END || '%' AS index_pct
FROM pg_tables where schemaname = 'public'
ORDER BY siz DESC LIMIT 50;

 schemaname | tablename |   size_p   |    siz    | total_size_p | index_size | index_pct
------------+-----------+------------+-----------+--------------+------------+-----------
 public     | t1        | 228 MB     | 239083520 | 228 MB       |      98304 | 0%
 public     | t         | 64 kB      |   4325376 | 4224 kB      |    4259840 | 98%
 public     | emp       | 8192 bytes |     49152 | 48 kB        |      40960 | 83%
 public     | person    | 8192 bytes |      8192 | 8192 bytes   |          0 | 0%
(4 rows)

learn come from:http://blog.chinaunix.net/uid-24774106-id-3757916.html

 

3.SELECT pg_relation_size(oid) FROM pg_class where relname='t1';

 postgres=# SELECT pg_relation_size(oid) FROM pg_class where relname='t1';
 pg_relation_size
------------------
        238985216
(1 row)

 notic： 238985216 =  239083520 -  98304

4.  In PostgreSQL terminology, an LSN (Log Sequence Number) is a 64-bit integer used to determine a position in WAL (Write ahead log), used to preserve data integrity. Internally in code, it is managed as XLogRecPtr, a simple 64-bit integer. An LSN is represented with two hexadecimal numbers of 8 digits each separated with "/". For example, when looking on server what is the current position of WAL, you can do something like that

select  pg_curent_xlog_location();

(learn from http://michael.otacoo.com/)

5. 查看缓存命中率

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;

我们可以这个在 dataclip上显示 Heroku Postgres的缓存命中率为99.99%。如果你发现比例低于99%，那么你可能想要考虑增加数据库的缓存可用性了，你可以在Heroku Postgres上使用 快速提升数据库性能 或者在像EC2之类的上使用dump/restore组成一个更大的实例来提升性能。

（来自：http://www.2cto.com/os/201306/223460.html）

6、查看表对应的文件位置

postgres=# select pg_relation_filepath('test');
 pg_relation_filepath 
----------------------
 base/12878/24577
(1 row)

7、查看数据库对应的目录

select oid, datname from pg_database;
postgres=# select oid, datname from pg_database;
  oid  |  datname  
-------+-----------
     1 | template1
 12873 | template0
 12878 | postgres
(3 rows)

8、查看一个表的详细信息，如有多少dead_tuples

postgres=# create table test(id int);
CREATE TABLE
postgres=# insert into test select generate_series(1,10);
INSERT 0 10
postgres=# begin;
BEGIN
postgres=# insert into test select generate_series(11,20);
INSERT 0 10
postgres=# rollback;
ROLLBACK
postgres=# commit;
WARNING:  there is no transaction in progress
COMMIT

postgres=# select * from pg_stat_all_tables where relname = 'test'; 
-[ RECORD 1 ]-----+------------------------------
relid             | 24579
schemaname        | public
relname           | test
seq_scan          | 2
seq_tup_read      | 20
idx_scan          | 
idx_tup_fetch     | 
n_tup_ins         | 20
n_tup_upd         | 0
n_tup_del         | 0
n_tup_hot_upd     | 0
n_live_tup        | 10
n_dead_tup        | 0
last_vacuum       | 2014-06-07 23:40:42.876369+08
last_autovacuum   | 
last_analyze      | 
last_autoanalyze  | 
vacuum_count      | 1
autovacuum_count  | 0
analyze_count     | 0
autoanalyze_count | 0

postgres=# VACUUM VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": removed 10 row versions in 1 pages
INFO:  "test": found 10 removable, 10 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
VACUUM

9、get infromation about a table belongs to which database

postgres=# SELECT * FROM information_schema.tables WHERE table_name='test';
-[ RECORD 1 ]----------------+-----------
table_catalog                | <strong>postgres</strong>
table_schema                 | public
table_name                   | test
table_type                   | BASE TABLE
self_referencing_column_name | 
reference_generation         | 
user_defined_type_catalog    | 
user_defined_type_schema     | 
user_defined_type_name       | 
is_insertable_into           | YES
is_typed                     | NO
commit_action                |