1.最后分析或真空或创建表或等...
Select * from pg_stat_operations where schemaname='SCHEMA NAME '
and actionname in ('ANALYZE','VACUUM') order by statime;
2.长时间查询空闲：
Select * from pg_stat_activity order by query_start,backend_start;
gpdb=# Select * from pg_stat_activity order by query_start,backend_start;
datid | datname | procpid | sess_id | usesysid | usename |
current_query | waiting | query_start
| backend_start | client_addr | client_port | application_name
| xact_start | waiting_reason | rsgid | rsgname | rsgqueueduratio
n
-------+-----------+---------+---------+----------+---------+-----------------------
---------------------------------------------+---------+----------------------------
---+-------------------------------+---------------+-------------+------------------
+-------------------------------+----------------+-------+---------+----------------
--
16385 | gpperfmon | 31604 | 2822 | 16558 | gpmon | <IDLE>
| f | 2019-03-20 21:19:40.079557-
04 | 2019-03-19 05:45:25.082823-04 | 192.168.0.221 | 62596 | gpcc
| | | 0 | unknown |
16385 | gpperfmon | 7652 | 207 | 16558 | gpmon | <IDLE>
| f | 2019-03-20 21:19:45.883945-
04 | 2019-03-19 01:52:55.080215-04 | 192.168.0.221 | 45824 | gpcc
| | | 0 | unknown |
16384 | gpdb | 6961 | 27890 | 10 | gpadmin | Select * from pg_stat_
activity order by query_start,backend_start; | f | 2019-03-20 21:19:47.667488-
04 | 2019-03-20 21:19:20.171974-04 | | -1 | psql
| 2019-03-20 21:19:47.667488-04 | | 0 | unknown |
(3 rows)
View Code
3.如何在数据库中找到最大的表？
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
gpdb=# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
relname | relpages
----------------------------------------------------------------+----------
test_1 | 1672
test_index_1 | 1672
test99 | 1000
gp_disk_free | 1000
__gp_log_segment_ext | 1000
t1 | 1000
__gp_localid | 1000
__gp_masterid | 1000
__gp_log_master_ext | 1000
test_index_1_idx | 56
pg_proc | 20
pg_rewrite | 19
pg_attribute | 14
pg_depend | 14
pg_depend_reference_index | 13
pg_depend_depender_index | 13
pg_proc_proname_args_nsp_index | 9
test66 | 8
pg_statistic | 6
gp_persistent_relation_node | 6
pg_description | 6
pg_attribute_relid_attnam_index | 6
pg_attribute_relid_attnum_index | 5
pg_description_o_c_o_index | 5
test1 | 5
pg_proc_oid_index | 5
test2 | 4
test110 | 4
pg_operator | 4
gpcrondump_history | 3
pg_type | 3
View Code
4.数据库中的前5个最大表
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 5;
gpdb=# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 5;
relname | relpages
---------------------+----------
test_index_1 | 1672
test_1 | 1672
__gp_localid | 1000
__gp_masterid | 1000
__gp_log_master_ext | 1000
(5 rows)
View Code
relname– 关系/表的名称.
relpages - 关系页面（页数，默认情况下页面为8kb）
pg_class– 系统表，维护关系的细节
limit 5 – 限制输出只显示5行。
5.如何计算磁盘中的数据库大小？
SELECT pg_database_size('Database Name' );
SELECT pg_size_pretty(pg_database_size( 'Database Name' ));
gpdb=# SELECT pg_database_size('gpdb' );
pg_database_size
------------------
215354446
(1 row)
gpdb=# SELECT pg_database_size('postgres' );
pg_database_size
------------------
214830158
(1 row)
gpdb=# SELECT pg_size_pretty(pg_database_size( 'gpdb' ));
pg_size_pretty
----------------
205 MB
(1 row)
gpdb=# SELECT pg_size_pretty(pg_database_size( 'postgres' ));
pg_size_pretty
----------------
205 MB
(1 row)View Code
6.如何计算磁盘中的表大小？
SELECT pg_size_pretty(pg_total_relation_size('public.test1'));
gpdb=# SELECT pg_size_pretty(pg_total_relation_size('public.test1'));
pg_size_pretty
----------------
160 kB
(1 row)View Code
7.如何查找表的大小（不包括索引）？
SELECT pg_size_pretty(pg_relation_size('public.test1'));
gpdb=# SELECT pg_size_pretty(pg_relation_size('public.test1'));
pg_size_pretty
----------------
160 kB
(1 row)View Code
8.如何生成一系列数字并将其插入表格中？
INSERT INTO test2 (id) VALUES ( generate_series(1,1000));
gpdb=# INSERT INTO test3 (id) VALUES ( generate_series(1,1000));
INSERT 0 1000
gpdb=# SELECT * from test3 limit 3;
id | name
----+------
1 |
9 |
17 |
(3 rows)
View Code
9.如何计算表中的总行数？
select count(*) from test1;
gpdb=# select count(*) from test2;
count
-------
1006
(1 row)
View Code
10.具有特定列值的行总数不为空。
select count(id) from public.test1;
gpdb=# select count(id) from public.test1;
count
-------
6
(1 row)
View Code
11.如何获取表中列的最大值？ - 第一秒
select max( col_name) from table ;
SELECT MIN(col name) from table_namewhere num > (select MIN(col
name) from table_name);
12.如何获得表中列的第二个最小值？ - 第一个和第二个
select min(col_name) from table;
SELECT MIN(col name) from table_namewhere num > (select MIN(col
name) from table_name);
13.如何查看GP中的基本可用数据类型。
SELECT typname,typlen from pg_type where typtype='b';
typname – 数据类型的名称
typlen – 数据类型的长度
gpdb=# SELECT typname,typlen from pg_type where typtype='b';
typname | typlen
---------------------+--------
bool | 1
bytea | -1
char | 1
name | 64
int8 | 8
int2 | 2
int2vector | -1
int4 | 4
regproc | 4
text | -1
oid | 4
tid | 6
xid | 4
cid | 4
oidvector | -1
xml | -1
_xml | -1
json | -1
_json | -1
complex | 16
_complex | -1
smgr | 2
point | 16
lseg | 32
path | -1
box | 32
polygon | -1
line | 32
_line | -1
float4 | 4
float8 | 8
abstime | 4
reltime | 4
View Code
14.显示已关闭的segments。
Select * from gp_segment_configuration where status='d';
15.查找当前用户：
SELECT SESSION_USER, CURRENT_USER;
gpdb=# SELECT SESSION_USER, CURRENT_USER;
session_user | current_user
--------------+--------------
gpadmin | gpadmin
(1 row)
View Code
16.检查活动会话（工作负载）：
SELECT * FROM pg_stat_activity;
gpdb=# SELECT * FROM pg_stat_activity;
datid | datname | procpid | sess_id | usesysid | usename | current_query
| waiting | query_start | backend_start
| client_addr | client_port | application_name | xact_start | w
aiting_reason | rsgid | rsgname | rsgqueueduration
-------+-----------+---------+---------+----------+---------+-----------------------
----------+---------+-------------------------------+-------------------------------
+---------------+-------------+------------------+-------------------------------+--
--------------+-------+---------+------------------
16385 | gpperfmon | 7652 | 207 | 16558 | gpmon | <IDLE>
| f | 2019-03-20 21:28:10.079528-04 | 2019-03-19 01:52:55.080215-04
| 192.168.0.221 | 45824 | gpcc | |
| 0 | unknown |
16385 | gpperfmon | 31604 | 2822 | 16558 | gpmon | <IDLE>
| f | 2019-03-20 21:28:25.079502-04 | 2019-03-19 05:45:25.082823-04
| 192.168.0.221 | 62596 | gpcc | |
| 0 | unknown |
16384 | gpdb | 6961 | 27890 | 10 | gpadmin | SELECT * FROM pg_stat_
activity; | f | 2019-03-20 21:28:29.916239-04 | 2019-03-20 21:19:20.171974-04
| | -1 | psql | 2019-03-20 21:28:29.916239-04 |
| 0 | unknown |
(3 rows)
View Code
17.正在队列中等待的查询
SELECT * FROM gp_tookit.gp_resqueue_status;
18.查看数据库列表
SELECT datname from pg_database;
gpdb=# SELECT datname from pg_database;
datname
-----------
template1
template0
postgres
gpdb
gpperfmon
test2
(6 rows)
View Code
