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;
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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)
3.如何在数据库中找到最大的表? SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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
4.数据库中的前5个最大表 SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 5;
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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)
relname– 关系/表的名称. relpages - 关系页面(页数,默认情况下页面为8kb) pg_class– 系统表,维护关系的细节 limit 5 – 限制输出只显示5行。 5.如何计算磁盘中的数据库大小? SELECT pg_database_size('Database Name' ); SELECT pg_size_pretty(pg_database_size( 'Database Name' ));
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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)
6.如何计算磁盘中的表大小? SELECT pg_size_pretty(pg_total_relation_size('public.test1'));
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
gpdb=# SELECT pg_size_pretty(pg_total_relation_size('public.test1')); pg_size_pretty ---------------- 160 kB (1 row)
7.如何查找表的大小(不包括索引)? SELECT pg_size_pretty(pg_relation_size('public.test1'));
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
gpdb=# SELECT pg_size_pretty(pg_relation_size('public.test1')); pg_size_pretty ---------------- 160 kB (1 row)
8.如何生成一系列数字并将其插入表格中? INSERT INTO test2 (id) VALUES ( generate_series(1,1000));
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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)
9.如何计算表中的总行数? select count(*) from test1;
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
gpdb=# select count(*) from test2; count ------- 1006 (1 row)
10.具有特定列值的行总数不为空。 select count(id) from public.test1;
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
gpdb=# select count(id) from public.test1; count ------- 6 (1 row)
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 – 数据类型的长度
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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
14.显示已关闭的segments。 Select * from gp_segment_configuration where status='d'; 15.查找当前用户: SELECT SESSION_USER, CURRENT_USER;
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
gpdb=# SELECT SESSION_USER, CURRENT_USER; session_user | current_user --------------+-------------- gpadmin | gpadmin (1 row)
16.检查活动会话(工作负载): SELECT * FROM pg_stat_activity;
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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)
17.正在队列中等待的查询 SELECT * FROM gp_tookit.gp_resqueue_status;
18.查看数据库列表 SELECT datname from pg_database;
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
gpdb=# SELECT datname from pg_database; datname ----------- template1 template0 postgres gpdb gpperfmon test2 (6 rows)
select
[səˈlekt]
动词
选择; 选; 挑选; 点; 择; 筛选; 选拔; 遴选; 挑; 选派; 擢; 挑拣; 拔; 遴; 铨; 抡; 芼; 诹; 摘; 挑定; 提选; 甄拔
形容词
上等的; 选出的; 优良的
定义
动词
carefully choose as being the best or most suitable.
形容词
(of a group of people or things) carefully chosen from a larger number as being the best or most valuable.
同义词
动词
choose; pick (out); single out; sort out; take; opt for; decide on; settle on; determine; nominate; appoint; elect
形容词
choice; hand-picked; prime; first-rate; first-class; superior; finest; best; top-class; blue-ribbon; supreme; superb; excellent; A1; top-notch
示例
Punching in the code known only to a very
select few, the doors opened with small hiss.