postgresql 查看数据库,表,索引,表空间以及大小

postgresql 查看数据库,表,索引,表空间以及大小
张映 发表于 2013-05-31
分类目录： pgsql
标签：pgsql, postgresql, 大小, 数据库, 查看, 索引, 表, 表空间
客户要求用pgsql，所在服务器装了一下pgsql，我出了一个pgsql的分类，看这篇文章前，把这个分类下的文章都可以看一下，这是我熟悉pgsql的一套流程。以前搞过一次pgsql，很早了。
1,查看数据库
查看复制打印?
playboy=> \l //\加上字母l,相当于mysql的，mysql> show databases;
List of databases
Name | Owner | Encoding
———–+———-+———-
playboy | postgres | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8

playboy=> select pg_database_size(‘playboy’); //查看playboy数据库的大小

pg_database_size

      3637896  

(1 row)

playboy=> select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database; //查看所有数据库的大小
datname | size
———–+———
postgres | 3621512
playboy | 3637896
template1 | 3563524
template0 | 3563524
(4 rows)

playboy=> select pg_size_pretty(pg_database_size(‘playboy’)); //以KB，MB，GB的方式来查看数据库大小

pg_size_pretty

3553 kB
(1 row)
2，查看多表
查看复制打印?
playboy=> \dt //相当于mysql的，mysql> show tables;
List of relations
Schema | Name | Type | Owner
——–+——+——-+———
public | test | table | playboy
(1 row)
3，查看单表
查看复制打印?
playboy=> \d test; //相当于mysql的，mysql> desc test;
Table “public.test”
Column | Type | Modifiers
——–+———————–+———–
id | integer | not null
name | character varying(32) |
Indexes: “playboy_id_pk” PRIMARY KEY, btree (id)

playboy=> select pg_relation_size(‘test’); //查看表大小

pg_relation_size

            0  

(1 row)

playboy=> select pg_size_pretty(pg_relation_size(‘test’)); //以KB，MB，GB的方式来查看表大小

pg_size_pretty

0 bytes
(1 row)

playboy=> select pg_size_pretty(pg_total_relation_size(‘test’)); //查看表的总大小，包括索引大小

pg_size_pretty

8192 bytes
(1 row)
4，查看索引
查看复制打印?
playboy=> \di //相当于mysql的，mysql> show index from test;
List of relations
Schema | Name | Type | Owner | Table
——–+—————+——-+———+——-
public | playboy_id_pk | index | playboy | test
(1 row)

playboy=> select pg_size_pretty(pg_relation_size(‘playboy_id_pk’)); //查看索大小

pg_size_pretty

8192 bytes
(1 row)
5，查看表空间，以及大小
查看复制打印?
playboy=> select spcname from pg_tablespace; //查看所有表空间

spcname

pg_default
pg_global
(2 rows)

playboy=> select pg_size_pretty(pg_tablespace_size(‘pg_default’)); //查看表空间大小

pg_size_pretty

14 MB
(1 row)
61

转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/pgsql/1525.html

PostgreSQL主键索引膨胀的重建方法
普通的索引膨胀处理比较简单，主键的索引膨胀也不复杂，只是在新旧索引交替时有一些小处理。本试验在primary key上通过CONCURRENTLY建立第二索引来解决索引膨胀问题，适用9.3、9.4，其他版本使用前请实际测试。

创建测试表

                                Table "swrd.mytbl"

Column | Type | Modifiers
——–+—————————–+—————————————————-
id | integer | not null default nextval(‘mytbl_id_seq’::regclass)
val | timestamp without time zone | default now()
Indexes:
“mytbl_pkey” PRIMARY KEY, btree (id)
生成测试数据

生成测试数据步骤略，这里为了清楚看到测试的情况，生成10000000条。

swrd=# SELECT COUNT(*) FROM mytbl;

count

10000000
(1 row)
创建第二索引

在id上创建第二索引，记得使用CONCURRENTLY参数

swrd=# CREATE UNIQUE INDEX CONCURRENTLY ON mytbl USING btree(id);
CREATE INDEX
可以看到id字段上同时有两个索引mytbl_pkey和mytbl_id_idx

swrd=# SELECT schemaname,relname,indexrelname,pg_relation_size(indexrelid) AS index_size,idx_scan,idx_tup_read,idx_tup_fetch FROM pg_stat_user_indexes WHERE indexrelname IN (SELECT indexname FROM pg_indexes WHERE schemaname = ‘swrd’ AND tablename = ‘mytbl’);
schemaname | relname | indexrelname | index_size | idx_scan | idx_tup_read | idx_tup_fetch
————+———+————–+————+———-+————–+—————
swrd | mytbl | mytbl_pkey | 224632832 | 0 | 0 | 0
swrd | mytbl | mytbl_id_idx | 224641024 | 0 | 0 | 0
(2 rows)
替换索引

开启事务删除主键索引同时将第二索引更新为主键的约束

swrd=# begin;
BEGIN
swrd=# ALTER TABLE mytbl DROP CONSTRAINT mytbl_pkey;
ALTER TABLE
swrd=# ALTER TABLE mytbl ADD CONSTRAINT mytbl_id_idx PRIMARY KEY USING INDEX mytbl_id_idx;
ALTER TABLE
swrd=# END;
COMMIT
检查测试表的索引，可见现在只有第二索引了

swrd=# SELECT schemaname,relname,indexrelname,pg_relation_size(indexrelid) AS index_size,idx_scan,idx_tup_read,idx_tup_fetch FROM pg_stat_user_indexes WHERE indexrelname IN (SELECT indexname FROM pg_indexes WHERE schemaname = ‘swrd’ AND tablename = ‘mytbl’);
schemaname | relname | indexrelname | index_size | idx_scan | idx_tup_read | idx_tup_fetch
————+———+————–+————+———-+————–+—————
swrd | mytbl | mytbl_id_idx | 224641024 | 0 | 0 | 0
(1 row)
检查表的定义

检查表定义，可以看到与最初建表时是一样的

swrd=# \d+ mytbl
Table “swrd.mytbl”
Column | Type | Modifiers | Storage | Stats target | Description
——–+—————————–+—————————————————-+———+————–+————-
id | integer | not null default nextval(‘mytbl_id_seq’::regclass) | plain | |
val | timestamp without time zone | default now() | plain | |
Indexes:
“mytbl_id_idx” PRIMARY KEY, btree (id)