postgresql parallel join example

CREATE TABLE public.pgbench_accounts_bak (
aid integer NOT NULL,
bid integer,
abalance integer,
filler character(84)
);

 

ALTER TABLE public.pgbench_accounts_bak OWNER TO postgres;


ALTER TABLE ONLY public.pgbench_accounts_bak
ADD CONSTRAINT pgbench_accounts_bak_pkey PRIMARY KEY (aid);

explain select count(*) from pgbench_accounts;
Finalize Aggregate (cost=22188.97..22188.98 rows=1 width=8)
-> Gather (cost=22188.76..22188.97 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=21188.76..21188.77 rows=1 width=8)
-> Parallel Index Only Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.42..20147.09 rows=416667 width=0)


explain select * from pgbench_accounts a join pgbench_branches b on a.bid=b.bid ;
youge=# set parallel_setup_cost=1;
SET
youge=# set parallel_tuple_cost=0.001;
SET
youge=# explain select * from pgbench_accounts a join pgbench_branches_bak b on a.bid=b.bid ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Gather (cost=13744.46..57865.89 rows=1000000 width=461)
Workers Planned: 2
-> Parallel Hash Join (cost=13743.46..56864.89 rows=416667 width=461)
Hash Cond: (a.bid = b.bid)
-> Parallel Seq Scan on pgbench_accounts a (cost=0.00..20560.67 rows=416667 width=97)
-> Parallel Hash (cost=3092.65..3092.65 rows=176465 width=364)
-> Parallel Seq Scan on pgbench_branches_bak b (cost=0.00..3092.65 rows=176465 width=364)
(7 rows)

 

explain select * from pgbench_accounts a1 join pgbench_accounts_bak a2 on a1.bid = a2.bid where a2.aid >1000;
QUERY PLAN
------------------------------------------------------------------------------------------------
Merge Join (cost=473145.77..1499198922.65 rows=99914552113 width=194)
Merge Cond: (a2.bid = a1.bid)
-> Sort (cost=237718.93..240216.52 rows=999037 width=97)
Sort Key: a2.bid
-> Seq Scan on pgbench_accounts_bak a2 (cost=0.00..28894.00 rows=999037 width=97)
Filter: (aid > 1000)
-> Materialize (cost=235426.84..240426.84 rows=1000000 width=97)
-> Sort (cost=235426.84..237926.84 rows=1000000 width=97)
Sort Key: a1.bid
-> Seq Scan on pgbench_accounts a1 (cost=0.00..26394.00 rows=1000000 width=97)
(10 rows)


create index pgbench_accounts_bid_key on pgbench_accounts_bak(bid);

youge=# set parallel_setup_cost=1;
SET
youge=# set parallel_tuple_cost=0.001;
SET
youge=# explain select * from pgbench_accounts_bak a join pgbench_branches_bak b on a.bid=b.bid ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=26251.42..42754.18 rows=1000000 width=461)
Workers Planned: 2
-> Merge Join (cost=26250.42..41753.18 rows=416667 width=461)
Merge Cond: (a.bid = b.bid)
-> Parallel Index Scan using pgbench_accounts_bid_key on pgbench_accounts_bak a (cost=0.42..36544.09 rows=416667 width=97)
-> Index Scan using pgbench_branches_bak_pkey on pgbench_branches_bak b (cost=0.42..9131.27 rows=299990 width=364)
(6 rows)

 

转载于:https://www.cnblogs.com/youge-OneSQL/p/9913550.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值