postgresql总结的简单介绍

PostgreSQL Oracle 兼容性之存储过程

 在oracle中，函数和存储过程是经常使用到的，并且有所区别；而postgresql中函数和存储过程都是相同定义的。

让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：申请域名、网站空间、营销软件、网站建设、龙陵网站维护、网站推广。

 1.定义：定义存储过程的关键字为procedure。

 2.创建存储过程

例：

（无参数）

（有参有返）

总结 ：

 1.创建存储过程的关键字为procedure。

 2.传参列表中的参数可以用in，out，in out修饰，参数类型一定不能写大小。列表中可以有多个输入输出参数。

 3.存储过程中定义的参数列表不需要用declare声明，声明参数类型时需要写大小的一定要带上大小。

 4.as可以用is替换。

 5.调用带输出参数的过程必须要声明变量来接收输出参数值。

 6.执行存储过程有两种方式，一种是使用execute，另一种是用begin和end包住。

 1.定义：定义函数的关键字为function。

 2.创建函数

总结 ：

 1.定义函数的关键字为function 。

 2.必须有返回值，且声明返回值类型时不需要加大小。

 3.函数中定义的参数列表不需要用declare声明，声明参数类型时需要写大小的一定要带上大小。

 4.as可以用is替换。

  5.执行存储过程有两种方式，一种是使用select，另一种是用begin和end包住。

不同点：

 1.存储过程定义关键字用procedure，函数定义用function。

 2.存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。

 3.执行方式略有不同，存储过程的执行方式有两种（1.使用execute2.使用begin和end），函数除了存储过程的两种方式外，还可以当做表达式使用，例如放在select中（select f1() form dual;）。

 postgresql则将函数和存储过程合为一体，不再明确区分存储过程与函数。

 1.定义：定义函数（存储过程 ）的关键字为function。

 2.创建

例：

 1.必须有有returns（注意是returns不是return）子句，无返回值时returns viod

 2.执行时，有返回值用select，无返回值时用perform

 3.必须指定语言LANGUAGE

PostgreSQL 数据类型介绍（五）OID的理解

那oid在哪儿？到底为什么会出现这种情况 ？

来看看postgres官网对 oid的介绍：

根据stackoverflow的高票用户的回答：

*OIDs basically give you a built-in, globally unique id for every row, contained in a system column (as opposed to a user-space column). That's handy for tables where you don't have a primary key, have duplicate rows, etc. For example, if you have a table with two identical rows, and you want to delete the oldest of the two, you could do that using the oid column.

In my experience, the feature is generally unused in most postgres-backed applications (probably in part because they're non-standard), and their use is essentially deprecated :

In PostgreSQL 8.1 default_with_oids is off by default; in prior versions of PostgreSQL, it was on by default.

The use of OIDs in user tables is considered deprecated, so most installations should leave this variable disabled. Applications that require OIDs for a particular table should specify WITH OIDS when creating the table. This variable can be enabled for compatibility with old applications that do not follow this behavior.

大意是你要是有个表没有用主键，这时候可以把oid充当为主键使用，当然这是没办法的办法。

总结： oid是给内部表做标识用的，不推荐使用。 建议将 default_with_oids 设置为off。 建表的时候，如果想使用主键，请自行建立。oid本身大小固定的，万一 行数超过了oid 的最大限制数（4 byte int），那就无法插入新行了。

postgresql 查看表建立哪些索引

在数据库运维工作中，经常会有数据目录使用率较高需要调整的情况，通常会给数据库建立多个表空间，

并分别位于不同的盘上，这时需要做的工作就是调整库中现有表和索引的表空间，下面简单总结下这块维护

工作的内容，以下都是基于 PostgreSQL 9.0.1 做的测试。

一 查询某个表所在表空间的简单方法

PostgreSQL 提供类似" \ "命令很方便得到相关信息，命令如下：

skytf= \d test_2

Table "skytf.test_2"

Column | Type | Modifiers

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

id | integer |

obj_id | integer | not null

name | character varying(64) |

Indexes:

"idx_hash_name" hash (name)

"idx_test_2" btree (id, obj_id)

Tablespace: "tbs_skytf_idx"

备注：如果这个表的表空间为当前数据库的默认表空间，那么上面则不会显示 Tablespace 信息，

相反，则会显示这张有的表空间，例如上面的表 test_2 的表空间为 tbs_skytf_idx，而

表空间 "tbs_skytf_idx" 不是数据库 skytf 的默认表空间， 那么如何查询数据库的默认

表空间呢，可以通过以下命令查询。

--1.1 查询数据库的默认表空间

skytf= select datname,dattablespace from pg_database where datname='skytf';

datname | dattablespace

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

skytf | 14203070

(1 row)

skytf= select oid,spcname from pg_tablespace where oid=14203070;

oid | spcname

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

14203070 | tbs_skytf

(1 row)

备注：通过以上查出数据库 skytf 的默认表空间为 tbs_skytf。

二 批量查询数据库表和索引的表空间

--2.1 查询表和索引所在的表空间

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)), tb.spcname

from pg_class a, pg_tablespace tb

where a.reltablespace = tb.oid

and a.relkind in ('r', 'i')

order by a.relpages desc;

备注：上面只取了部分结果，这个查询能够查询表和索引所处的表空间，但是有一点需要注意，这个查询

仅显示表空间不是数据库默认表空间的数据库对像，而我们通常需要查出位于数据库默认表空间的

对像，显然上面的查询不是我们想要的，接下来看另一个查询。

--2.2 查询位于默认数据库表空间的对像

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner

from pg_class a

where a.relkind in ('r', 'i')

and reltablespace='0'

order by a.relpages desc;

备注：这个查询加入限制条件 reltablespace='0'，即可查找出位于当前数据库默认表空间的

数据库表和索引。 通常这才是我们想要的结果，接下来可以把部分表转移到其它表空间上去，转移

的方法可以用 "ALTER TABLE move tablespace "或者重建索引移表空间等方法，这里不详细介绍。

--2.3 查询在某个表空间上的对像

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner

from pg_class a, pg_tablespace tb

where a.relkind in ('r', 'i')

and a.reltablespace=tb.oid

and tb.spcname='tablespace_name'

order by a.relpages desc;

--2.4 手册上对于 pgclass 视图的 reltablespace 字段解释

The tablespace in which this relation is stored. If zero, the database is default tablespace is

implied. (Not meaningful if the relation has no on-disk file.)

postgresqL 的Btree 与gin索引

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

yum install postgresql96-contrib -- 安装插件

find / -name extension --可以看到btree_gin.control存在

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 ;

3.jsonb属性

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' ;

4.jsonb表达式索引

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

(json_t-id)= '24426014' basic_account_info_al inx_json_id 0.040

basic_account_info_al3 inx_json_id_2 0.039

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 public.basic_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 public.basic_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 643561.004 ms

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

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

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

130372.955 ms

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

148971.011 ms