Postgresql使用记录 - 简书
Postgresql使用记录
root用户，使用"su postgres"切换到postgres
postgres=#
例如创建一个新的fdw(foreign-data-wrapper):
postgres=#create extension postgres_
查看安装的扩展
postgres=# select * from pg_
postgres=# \dx
postgres_fdw实例
:& psql product
product=# create extension postgres_
1，创建外部服务器对象
product=# create server inventory_foreign_server foreign data wrapper postgres_fdw options(host '192.168.223.14', dbname 'b2b_inventory',port '5432');
2，创建用户映射
product=# create user mapping for tomtop server inventory_foreign_server options(user 'tomtop', password 'tomtop');
3，建立外部表
product=# create foreign table cloud_inventory(id bigint, sku varchar(255), stock integer, warehouse_id integer, warehouse_name varchar(255), residue_num integer) server inventory_foreign_server options(table_name 't_product_inventory_total');
product=# create foreign table cloud_inventory_lock(sku varchar(255), stock_locked integer, warehouse_id integer, warehouse_name varchar(255), is_effective smallint) server inventory_foreign_server options(table_name 't_product_inventory_order_lock');
product=# create foreign table micro_inventory(sku varchar(255), warehouse_id integer, warehouse_name varchar(255), stock integer, account varchar(255)) server inventory_foreign_server options(table_name 't_product_micro_inventory_total');
product=# create foreign table micro_inventory_lock(sku varchar(255), stock_locked integer, warehouse_id integer, warehouse_name varchar(255), is_effective smallint, account varchar(255)) server inventory_foreign_server options(table_name 't_product_micro_inventory_order_lock');
建立后的表和本地表看起来是一样的；使用tomtop用户：select * from cloud_
递归查询实例
数据表中，商品类目(t_category_base)是按父子级存的，类目的名称在t_category_name，现在需要拿到每个类目的树，比如"母婴产品=&奶粉=&爱他美"，在展示第三级类目"爱他美"的时候，对应的类目树应该是"(母婴用品)(奶粉)(爱他美)"，如下图
CREATE TABLE "public"."t_category_base" (
"iid" SERIAL NOT NULL,
"iparentid" int4,
"cpath" varchar(255) COLLATE "default",
"ilevel" int4,
"iposition" int4,
"ichildrencount" int4
CREATE TABLE "public"."t_category_name" (
"iid" SERIAL NOT NULL,
"icategoryid" int4,
"ilanguageid" int4,
"cname" varchar(300) COLLATE "default",
"ctitle" varchar(300) COLLATE "default",
"cdescription" varchar(2000) COLLATE "default",
"ckeywords" varchar(2000) COLLATE "default",
"cmetatitle" varchar(500) COLLATE "default",
"cmetakeyword" varchar(2000) COLLATE "default",
"cmetadescription" varchar(2000) COLLATE "default",
"ccontent" varchar(8000) COLLATE "default",
"cnickname" varchar(32) COLLATE "default"
需要用到的是递归的关键字WITH RECURSIVE，下面是真实代码：
WITH RECURSIVE T
categoryIdTree,
categoryNameTree,
t_category_base.iid,
iparentid,
ARRAY[t_category_base.iid] AS categoryIdTree,
'(' || cname || ')' AS categoryNameTree,
1 AS level
FROM t_category_base, t_category_name
iparentid = -1
AND t_category_base.iid = t_category_name.icategoryid
AND t_category_name.ilanguageid = 1
cb.iparentid,
T.categoryIdTree ||cb.iid,
T.categoryNameTree || '(' || cb.cname || ')',
T.level + 1 AS level
cb.iid, cb.iparentid, cn.cname
FROM t_category_base cb, t_category_name cn
WHERE cb.iid = cn.icategoryid
AND cn.ilanguageid = 1
JOIN T ON cb.iparentid = T.id
) select * from T;
pgsql的递归查询效率很高。
更复杂的递归实例
有一个更麻烦的业务场景，要展示商品和类目树的关系；
麻烦的是，商品和类目映射表中存数据的时候，sku会存所有级别的映射，而且某个商品可能属于两个三级类目，那这个sku在这个映射表中就会存在3+3行数据，如下图
上面递归查询结果T是平铺了所有的类目的父子级关系，所以映射表需要先过滤自己，只取关联的最后一级类目的id，这时候需要关联T来做操作.t_product_category_mapper的建标语句:
WITH RECURSIVE T
categoryIdTree,
categoryNameTree,
t_category_base.iid,
iparentid,
ARRAY[t_category_base.iid] AS categoryIdTree,
'(' || cname || ')' AS categoryNameTree,
1 AS level
FROM t_category_base, t_category_name
iparentid = -1
AND t_category_base.iid = t_category_name.icategoryid
AND t_category_name.ilanguageid = 1
cb.iparentid,
T.categoryIdTree ||cb.iid,
T.categoryNameTree || '(' || cb.cname || ')',
T.level + 1 AS level
cb.iid, cb.iparentid, cn.cname
FROM t_category_base cb, t_category_name cn
WHERE cb.iid = cn.icategoryid
AND cn.ilanguageid = 1
JOIN T ON cb.iparentid = T.id
T.categoryIdTree categoryIdTree,
T.categoryNameTree categoryNameTree
t_product_category_mapper pcm
NOT IN (select iparentid from t_category_base)
AND pcm.icategory = T.ID
使用db_link
首选在对应的库要创建'db_link'扩展，create extension db_link，创建的方法在顶部第一部分
注意，根据 ，db_link在使用之前，需要用SELECT dblink_connect语句建立连接，使用完要用SELECT dblink_disconnect关闭连接；
select dblink_connect('product_dblink','dbname=product host=127.0.0.1 port=5432 user=tomtop password=tomtop');
INSERT INTO t_product_base_mapper (
distributorid,
preferdate,
retailprice,
iwebsiteid,
masterimgurl,
warehouseno,
warehousename,
totalsales
nextval('t_product_base_mapper_iid_seq'),
T.masterimgurl,
T.warehouseno,
T.warehousename,
FROM dblink('product_dblink',
DISTINCT pb.csku,
pb.fprice,
pt.ctitle,
gi.warehouse_id,
gi.warehouse_name,
pi.cimageurl
t_product_base pb
LEFT JOIN t_product_translate pt ON pb.csku = pt.csku
LEFT JOIN t_goods_inventory gi ON pb.csku = gi.sku
LEFT JOIN t_product_image pi ON pb.csku = pi.csku
AND pi.bbaseimage=true
AND pi.bsmallimage=true
AND iorder=(select MAX(iorder) from t_product_image where csku=pb.csku and bbaseimage=true)'
sku VARCHAR(20),
price float8,
title VARCHAR(200),
warehouseno VARCHAR(20),
warehousename VARCHAR(20),
masterimgurl VARCHAR(200))
where t.sku not in (select csku from t_product_base_mapper where iwebsiteid=1)
) T where T.war博客分类：
PostgreSQL没有提供像Oracle等数据库的实用函数。
对于常见到菜单menus表：主要字段有id，parent_id...
一下是一个递归查询函数：
--查询结果集含自身
CREATE OR REPLACE FUNCTION public.query_child_menus (integer) RETURNS SETOF public.menus AS'
itemid ALIAS FOR $1;
SELECT s.* INTO itemrecord FROM public.menus s
RETURN NEXT
IF (select count(1) from public.menus s where s.parent_id=itemrecord.id) &0
for itemrecord in SELECT s.* FROM public.menus s
where s.parent_id=itemrecord.id LOOP
for itemrecord in select * from public.query_child_menus (itemrecord.id) LOOP
RETURN NEXT
LANGUAGE 'plpgsql'
用法：select * from public.query_child_menus (3);
结果集字段与menus表字段相同。
浏览: 49968 次
来自: 郑州
博主，源码不全啊，把maven 构建的项目都发上来。
你好 还有abator的源码吗 给我一个吧
谢谢 a8250 ...
这不是官网上的例子么？
mysql 的 on duplicate key update ...
叫我一声大哥，我帮你
我现在的效率是插入一条记录平均耗时1毫秒 ...用PostgreSQL找回逝去的618秒青春 - 递归收敛
PostgreSQL , 少量not in大量 , 收敛优化 , 递归优化
有一个这样的场景，一张小表A，里面存储了一些ID，大约几百个（比如说巡逻车辆ID）。
另外有一张日志表B，每条记录中的ID是来自前面那张小表的，但不是每个ID都出现在这张日志表中，比如说一天可能只有几
PostgreSQL , 少量not in大量 , 收敛优化 , 递归优化 , 收敛查询优化 select (300 ids) not in (select ids from 300万)
有一个这样的场景，一张小表A，里面存储了一些ID，大约几百个。
（比如说巡逻车辆ID，环卫车辆的ID，公交车，微公交的ID）。
另外有一张日志表B，每条记录中的ID是来自前面那张小表的，但不是每个ID都出现在这张日志表中，比如说一天可能只有几十个ID会出现在这个日志表的当天的数据中。
（比如车辆的行车轨迹数据，每秒上报轨迹，数据量就非常庞大）。
那么我怎么快速的找出今天没有出现的ID呢。
（哪些巡逻车辆没有出现在这个片区，是不是偷懒了？哪些环卫车辆没有出行，哪些公交或微公交没有出行）？
select id from A where id not in (select id from B where time between ? and ?);
这个QUERY会很慢，有什么优化方法呢。
当然，你还可以让车辆签到的方式来解决这个问题，但是总有未签到的，或者没有这种设计的时候，那么怎么解决呢？
其实方法也很精妙，和我之前做的两个CASE很相似。
在B表中，其实ID的值是很稀疏的，只是由于是流水，所以总量大。
优化的手段就是对B的取值区间，做递归的收敛查询，然后再做NOT IN就很快了。
create table a(id int primary key, info text);
create table b(id int primary key, aid int, crt_time timestamp);
create index b_aid on b(aid);
插入测试数据
-- a表插入1000条
insert into a select generate_series(1,1000), md5(random()::text);
-- b表插入500万条，只包含aid的500个id。
insert into b select generate_series(1,5000000), generate_series(1,500), clock_timestamp();
优化前的性能
explain (analyze,verbose,timing,costs,buffers) select * from a where id not in (select aid from b);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Seq Scan on public.a
(cost=0.00.. rows=500 width=37) (actual time= rows=500 loops=1)
Output: a.id, a.info
Filter: (NOT (SubPlan 1))
Rows Removed by Filter: 500
Buffers: shared hit=27037, temp read=4264454 written=8545
Materialize
(cost=0.00.. rows=5000000 width=4) (actual time=0.002..298.049 rows=2500125 loops=1000)
Output: b.aid
Buffers: shared hit=27028, temp read=4264454 written=8545
Seq Scan on public.b
(cost=0.00..77028.00 rows=5000000 width=4) (actual time=0.009..888.427 rows=5000000 loops=1)
Output: b.aid
Buffers: shared hit=27028
Planning time: 0.969 ms
Execution time:
另外你有一种选择是使用outer join, b表同样需要全扫一遍，有很大的改进，不过还可以更好，继续往后看。
postgres=# explain (analyze,verbose,timing,costs,buffers) select a.id from a left join b on (a.id=b.aid) where b.*
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Hash Right Join
(cost=31.50.. rows=25000 width=4) (actual time=76.862 rows=500 loops=1)
Output: a.id
Hash Cond: (b.aid = a.id)
Filter: (b.* IS NULL)
Rows Removed by Filter: 5000000
Buffers: shared hit=27037
Seq Scan on public.b
(cost=0.00..77028.00 rows=5000000 width=44) (actual time=0.012.. rows=5000000 loops=1)
Output: b.aid, b.*
Buffers: shared hit=27028
(cost=19.00..19.00 rows=1000 width=4) (actual time=0.355..0.355 rows=1000 loops=1)
Output: a.id
Buckets: 1024
Batches: 1
Memory Usage: 44kB
Buffers: shared hit=9
Seq Scan on public.a
(cost=0.00..19.00 rows=1000 width=4) (actual time=0.010..0.183 rows=1000 loops=1)
Output: a.id
Buffers: shared hit=9
Planning time: 0.302 ms
Execution time:
递归收敛优化后的性能
explain (analyze,verbose,timing,costs,buffers)
select * from a where id not in
with recursive skip as (
select min(aid) aid from b where aid is not null
select (select min(aid) aid from b where b.aid & s.aid and b.aid is not null)
from skip s where s.aid is not null
-- 这里的where s.aid is not null 一定要加,否则就死循环了.
select aid from skip where aid is not null
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on public.a
(cost=54.98..76.48 rows=500 width=37) (actual time=10.837..10.957 rows=500 loops=1)
Output: a.id, a.info
Filter: (NOT (hashed SubPlan 5))
Rows Removed by Filter: 500
Buffers: shared hit=2012
CTE Scan on skip
(cost=52.71..54.73 rows=100 width=4) (actual time=0.042..10.386 rows=500 loops=1)
Output: skip.aid
Filter: (skip.aid IS NOT NULL)
Rows Removed by Filter: 1
Buffers: shared hit=2003
Recursive Union
(cost=0.46..52.71 rows=101 width=4) (actual time=0.037..10.104 rows=501 loops=1)
Buffers: shared hit=2003
(cost=0.46..0.47 rows=1 width=4) (actual time=0.036..0.036 rows=1 loops=1)
Output: $1
Buffers: shared hit=4
InitPlan 3 (returns $1)
(cost=0.43..0.46 rows=1 width=4) (actual time=0.031..0.032 rows=1 loops=1)
Output: b_1.aid
Buffers: shared hit=4
Index Only Scan using b_aid on public.b b_1
(cost=0.43.. rows=5000000 width=4) (actual time=0.030..0.030 rows=1 loops=1)
Output: b_1.aid
Index Cond: (b_1.aid IS NOT NULL)
Heap Fetches: 1
Buffers: shared hit=4
WorkTable Scan on skip s
(cost=0.00..5.02 rows=10 width=4) (actual time=0.019..0.019 rows=1 loops=501)
Output: (SubPlan 2)
Filter: (s.aid IS NOT NULL)
Rows Removed by Filter: 0
Buffers: shared hit=1999
(cost=0.47..0.48 rows=1 width=4) (actual time=0.018..0.018 rows=1 loops=500)
Output: $3
Buffers: shared hit=1999
InitPlan 1 (returns $3)
(cost=0.43..0.47 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=500)
Output: b.aid
Buffers: shared hit=1999
Index Only Scan using b_aid on public.b
(cost=0.43..66153.48 rows=1666667 width=4) (actual time=0.017..0.017 rows=1 loops=500)
Output: b.aid
Index Cond: ((b.aid & s.aid) AND (b.aid IS NOT NULL))
Heap Fetches: 499
Buffers: shared hit=1999
Planning time: 0.323 ms
Execution time: 11.082 ms
采用收敛查询优化后，耗时从最初的 618794毫秒 降低到了 11毫秒 ，感觉一下子节约了好多青春。
本文为云栖社区原创内容，未经允许不得转载，如需转载请发送邮件至yqeditor@list.；如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件至：yqgroup@ 进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容。
用云栖社区APP，舒服~
【云栖快讯】浅析混合云和跨地域网络构建实践，分享高性能负载均衡设计，9月21日阿里云专家和你说说网络那些事儿，足不出户看直播，赶紧预约吧！&&
用left join和主键为null的判断和这种方式相比仍有较大性能差距吗？
依旧差了200倍。
PostgreSQL被业界誉为“最先进的开源数据库”，面向企业复杂SQL处理的OLTP在线事务处理场景，支持No...
针对日志类数据的一站式服务，用户无需开发就能快捷完成数据采集、消费、投递以及查询分析等功能，帮助提升运维、运营效...
为您提供简单高效、处理能力可弹性伸缩的计算服务，帮助您快速构建更稳定、安全的应用，提升运维效率，降低 IT 成本...
Loading...2252人阅读
postgreSQL数据库（4）
开发有需求，说需要对一张地区表进行递归查询，Postgres中有个 with recursive的查询方式，可以满足递归查询(一般&=2层)。 测试如下：
create table tb(id varchar(3) , pid varchar(3) , name varchar(10));
insert into tb values('002' , 0 , '浙江省');
insert into tb values('001' , 0 , '广东省');
insert into tb values('003' , '002' , '衢州市');
insert into tb values('004' , '002' , '杭州市') ;
insert into tb values('005' , '002' , '湖州市');
insert into tb values('006' , '002' , '嘉兴市') ;
insert into tb values('007' , '002' , '宁波市');
insert into tb values('008' , '002' , '绍兴市') ;
insert into tb values('009' , '002' , '台州市');
insert into tb values('010' , '002' , '温州市') ;
insert into tb values('011' , '002' , '丽水市');
insert into tb values('012' , '002' , '金华市') ;
insert into tb values('013' , '002' , '舟山市');
insert into tb values('014' , '004' , '上城区') ;
insert into tb values('015' , '004' , '下城区');
insert into tb values('016' , '004' , '拱墅区') ;
insert into tb values('017' , '004' , '余杭区') ;
insert into tb values('018' , '011' , '金东区') ;
insert into tb values('019' , '001' , '广州市') ;
insert into tb values('020' , '001' , '深圳市') ;
测试语句,查询浙江省及以下县市：
test=# with RECURSIVE cte as
select a.id,a.name,a.pid from tb a where id='002'
select k.id,k.name,k.pid
from tb k inner join cte c on c.id = k.pid
)select id,
-----+--------
002 | 浙江省
003 | 衢州市
004 | 杭州市
005 | 湖州市
006 | 嘉兴市
007 | 宁波市
008 | 绍兴市
009 | 台州市
010 | 温州市
011 | 丽水市
012 | 金华市
013 | 舟山市
014 | 上城区
015 | 下城区
016 | 拱墅区
017 | 余杭区
018 | 金东区
如果查询有报错如死循环跳出，则需要检查下父字段与子字段的数据是否有相同。
如果想按层次分别显示出来，也可以这么写
test=# with RECURSIVE cte as
select a.id,cast(a.name as varchar(100)) from tb a where id='002'
union all&
select k.id,cast(c.name||'&'||k.name as varchar(100)) as name &from tb k inner join cte c on c.id = k.pid
)select id,
&id &| & & & & name & & & &&
-----+----------------------
&002 | 浙江省
&003 | 浙江省&衢州市
&004 | 浙江省&杭州市
&005 | 浙江省&湖州市
&006 | 浙江省&嘉兴市
&007 | 浙江省&宁波市
&008 | 浙江省&绍兴市
&009 | 浙江省&台州市
&010 | 浙江省&温州市
&011 | 浙江省&丽水市
&012 | 浙江省&金华市
&013 | 浙江省&舟山市
&014 | 浙江省&杭州市&上城区
&015 | 浙江省&杭州市&下城区
&016 | 浙江省&杭州市&拱墅区
&017 | 浙江省&杭州市&余杭区
&018 | 浙江省&丽水市&金东区
PS: MYSQL貌似还没出这么一种功能，
附带说一下SqlServer的的递归查询，语法类似，不过把recursive去掉就可以了，如：
with cte as
select a.id,a.name,a.pid from tb a where id='002'
select k.id,k.name,k.pid
from tb k inner join cte c on c.id = k.pid
)select id,
--出自此pgsql高手的博客
&&相关文章推荐
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：291426次
积分：6070
积分：6070
排名：第4285名
原创：303篇
转载：181篇
评论：11条
(2)(2)(3)(1)(13)(3)(1)(1)(11)(31)(15)(10)(1)(13)(1)(14)(2)(29)(20)(8)(29)(56)(57)(122)(39)
(window.slotbydup = window.slotbydup || []).push({
id: '4740881',
container: s,
size: '200,200',
display: 'inlay-fix'标签：至少1个，最多5个
先声明，瞎猜的，个人理解，不一定对。
递归查询大家都知道，常见的如一张表，包含id(主键),parent_id（该记录的父亲id）,比如我们要从某1个id往下找他所有的儿子还有孙子（这时候心里要有一颗树的形象），该怎么写？
WITH RECURSIVE cat AS (
category A
A . ID = 1
category fz
INNER JOIN cat ON fz. parent_id = cat.id
解释一下（注意RECURSIVE关键字）：
执行子查询SELECT * FROM category A WHERE A . ID = 1（UNIONALL之前的部分），这个作为起点
执行外层 select * from cat（把AS后面的子查询整体当作cat），这时候因为已经有id等于1的记录了，所以能查到1条记录
执行子查询中union all后面的部分，即：select fz.* from
category fz INNER JOIN caton fz.parent_id= cat.id，因为已经有1条记录，即 cat.id = 1, 所以此时该语句就变成selectfz.* from
category fz INNER JOIN cat on fz.parent_id =1，也就是找parent_id（就是父亲啦）等于1的记录，换句话说，就是在fz中找id=1的记录的儿子记录
假如第3步找到了多条记录（大于0，否则记录数为0就结束了）,假设有1条记录，设该记录为r1。重复执行步骤2和3，只是这里执行第2步的时候，从cat中选出的记录为r1.id；执行第3步，就变成了：selectfz.* from
category fz INNER JOIN cat on fz.parent_id=r1.id，就变成了找r1的儿子记录
如果第4步返回多条记录，类似啦，反正递归嘛
如此，自顶向下，一棵树就出来了
0 收藏&&|&&0
你可能感兴趣的文章
1 收藏，731
1 收藏，938
分享到微博？
技术专栏，帮你记录编程中的点滴，提升你对技术的理解收藏感兴趣的文章，丰富自己的知识库
明天提醒我
我要该，理由是：