postgresql数据库怎么查询所有的序列名

postgresql中一个序列对象通常用于为行或者表生成唯一的标识符。

查看序列：

psql 的 \d 命令输出一个数据库对象，包括 Sequence，表，视图和索引。你还可以使用 \ds 命令只查看当前数据库的所有序列。例如：

pigdb-# \ds

List of relations

Schema | Name | Type | Owner

--------+-----------------------+----------+--------

public | author_ids | sequence | ichexw

public | shipments_ship_id_seq | sequence | ichexw

(2 rows)

一．gin索引需要安装第三方插件

yum install postgresql96-contrib -- 安装插件

find / -name extension --可以看到btree_gincontrol存在

create extension btree_gin; -- 添加索引

二测试数据基本属性介绍

总共使用3个表，表结构和数据量完全一致。

表数据量：10522369

表字段：id ,basic_acc_no,id_card,name,sex,telephone,json_t

1）索引的配置情况：

basic_account_info_al -- btree

basic_account_info_al2 --gin

basic_account_info_al3 -- btree multi

basic_account_info_al 单列索引 id，basic_acc_no，name,json_t

basic_account_info_al2 gin索引 (id,basic_acc_no,id_card,name),(json_t)

basic_account_info_al3 复合索引 (id,basic_acc_no),(name,id)(json_t,id)

basic_account_info_al 表达式索引 (json_t->>id)

basic_account_info_al2表达式索引 ((json_t->>'id'))

三．测试结果

1唯一值属性：索引字段都是唯一 id，basic_acc_no

查询语句

explain analyse select from basic_account_info_al2 where id = 29699221 ;

explain analyse select from basic_account_info_al where id = 29699221 ;

explain analyse select from basic_account_info_al3 where id = 29699221 ;

explain analyse select from basic_account_info_al2 where basic_acc_no = 'XFK2990134' ;

explain analyse select from basic_account_info_al where basic_acc_no = 'XFK2990134' ;

explain analyse select from basic_account_info_al3 where basic_acc_no = 'XFK2990134' ;

explain analyse select from basic_account_info_al2 where basic_acc_no = 'XFK9780134' and id = 29699221;

explain analyse select from basic_account_info_al where basic_acc_no = 'XFK9780134' and id = 29699221;

explain analyse select from basic_account_info_al3 where basic_acc_no = 'XFK9780134' and id = 29699221;

explain analyse select from basic_account_info_al2 where id = 29699221 and basic_acc_no = 'XFK9780134' ;

explain analyse select from basic_account_info_al where id = 29699221 and basic_acc_no = 'XFK9780134' ;

explain analyse select from basic_account_info_al3 where id = 29699221 and basic_acc_no = 'XFK9780134' ;

2重复值属性： name是有重复值的。

explain analyse select from basic_account_info_al where name ='张燕洪';

explain analyse select from basic_account_info_al3 where name ='张燕洪';

explain analyse select from basic_account_info_al2 where name ='张燕洪';

explain analyse select from basic_account_info_al2 where id = 24426014 and name = '周杨' ;

explain analyse select from basic_account_info_al where id = 24426014 and name = '周杨' ;

explain analyse select from basic_account_info_al3 where id = 24426014 and name = '周杨' ;

explain analyse select from basic_account_info_al2 where name = '周杨' and id = 24426014 ;

explain analyse select from basic_account_info_al where name = '周杨' and id = 24426014 ;

explain analyse select from basic_account_info_al3 where name = '周杨' and id = 24426014 ;

3jsonb属性

create index inx_gin_json on basic_account_info_al2 using gin (json_t);

create index inx_btree_json on basic_account_info_al (json_t);

create index inx_btree_2_js on basic_account_info_al3 (json_t,id );

explain analyse select from basic_account_info_al where json_t ='{"id": 21782879, "sex": 0, "name": "刘乐典"}';

explain analyse select from basic_account_info_al2 where json_t ='{"id": 21782879, "sex": 0, "name": "刘乐典"}';

explain analyse select from basic_account_info_al3 where json_t ='{"id": 21782879, "sex": 0, "name": "刘乐典"}';

explain analyse select from basic_account_info_al WHERE json_t @> '{"id": 21782879}';

explain analyse select from basic_account_info_al2 WHERE json_t @> '{"id": 21782879}';

explain analyse select from basic_account_info_al3 WHERE json_t @> '{"id": 21782879}';

explain analyse select from basic_account_info_al where (json_t->>id)= '24426014' ;

explain analyse select from basic_account_info_al2 where (json_t->>id)= '24426014' ;

explain analyse select from basic_account_info_al3 where (json_t->>id)='24426014' ;

4jsonb表达式索引

查询条件 表名 查询时使用的索引名称 查询时间（5次平均）/ms

(json_t->>id)= '24426014' basic_account_info_al inx_json_id 0040

basic_account_info_al3 inx_json_id_2 0039

explain analyse select from basic_account_info_al where (json_t->>id)= '24426014' ;

explain analyse select from basic_account_info_al2 where (json_t->>id)= '24426014' ;

四．获相同的结果使用Jsonb与btree对比

jsonb支持两种特有的GIN索引jsonb_ops和jsonb_path_ops。 jsonb_ops调用gin_extract_jsonb函数生成key，每个键和值都作为一个单独的索引项。而jsonb_path_ops使用函数gin_extract_jsonb_path抽取：只为每个值创建一个索引项。{“foo”:{“bar”,”baz”}}, jsonb_ops生成3个索引项，jsonb_path_ops由foo,bar,baz组合一个hash值作为一个索引项。jsonb_path_ops索引要比jsonb_ops的小很多，性能上也会有所提升。

create index inx_gin_patn_json ON publicbasic_account_info_al4 USING gin (json_t jsonb_path_ops); -- jsonb_path_ops

create index inx_gin_json on basic_account_info_al2 using gin (json_t); --jsonb_ops

1精确查询

2范围查询

下表显示了gin索引对于jsonb数据类型可使用的 *** 作符。

名称 索引数据类型 可索引 *** 作符

jsonb_ops jsonb & | @>

json_path_ops jsonb @>

注： & | 索引key是否包含在jsonb中

对于范围(json_t->>'id')< 20000079，这样的条件 gin索引不起作用， 这里采用表达式索引方式，查询条件的两边数据类型相同才可以做索引查询，否则全表扫描。

CREATE INDEX inx_json_id_2 ON publicbasic_account_info_al2 USING btree (((json_t->>'id')::int));

总结： 当仅有一个条件查询时，gin索引与btree索引的性能差异不大，但有多个条件查询时，gin,btree单

列索引没有btree复合索引的性能高。jsonb是以二进制格式存储且不保证键的顺序。可以使用表达式索引指定到jsonb的具体键值，但是如果不能提前知道查询数据中的哪个键，确定定义GIN索引和使用@>（或者其他有利于索引的 *** 作符）查询。

五．jsonb添加数据属性

例如：

{"id":20000241,"name":"陈敏","sex":1} -> {"age":"18","id":20000241,"name":"陈敏","sex":1}

一旦创建了索引，就不需要进一步的干预：当表被修改时，系统将更新索引，当执行计划认为使用索引比顺序的表扫描更有效的时候，它会使用索引。

UPDATE basic_account_info_al4 SET json_t = json_t || '{"age":"18"}'::jsonb; -- 更新语句

gin索引名称 索引方式 修改前大小 修改后大小 带索引更新时间

inx_gin_patn_json jsonb_path_ops 574M 615M 643561004 ms

inx_gin_json jsonb_ops 665M 695M 时间过长超过1h

jsonb_ops方式建立的索引大量更新时，执行时间太长。当插入更新时gin索引比较慢，如果要向一张大表中插入大量数据时，最好先把gin索引删除，插入数据后再重建索引。

当json_t为{"id":20000241,"name":"陈敏","sex":1} 数据量为10522369 创建gin索引时间

130372955 ms

当json_t为{"age":"18","id":20000241,"name":"陈敏","sex":1} 数据量为10522369 创建gin索引时间

148971011 ms

mysql:show databases

pgsql:\l 或者\l+(显示的信息要多一些)

mysql:use xx

pgsql:\c xx

mysql:show tables

pgsql:\dt

1、通过命令行查询 \d 数据库 —— 得到所有表的名字 \d 表名 —— 得到表结构 2、通过SQL语句查询 "select from pg_tables" —— 得到当前db中所有表的信息（这里pg_tables是系统视图） "select tablename from pg_tables where schemaname='public

\d+ 表名

或者你上面的SQL调整一下

select aattname AS field,aattnum from pg_class c,pg_attribute a

where aattrelid = coid and aattnum > 0 and crelname = 'xx'

以上就是关于postgresql数据库怎么查询所有的序列名全部的内容，包括:postgresql数据库怎么查询所有的序列名、postgresqL 的Btree 与gin索引、PostgreSQL 与Mysql对照等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！