首页 > 数据库 >PostgreSQL技术大讲堂 - 第30讲：多表连接方式

PostgreSQL技术大讲堂 - 第30讲：多表连接方式

时间：2023-09-22 14:55:59浏览次数：36  
标签：rows PostgreSQL .. 30 width cost tbl 多表 id

 

PostgreSQL从小白到专家，是从入门逐渐能力提升的一个系列教程，内容包括对PG基础的认知、包括安装使用、包括角色权限、包括维护管理、、等内容，希望对热爱PG、学习PG的同学们有帮助，欢迎持续关注CUUG PG技术大讲堂。

 

第30讲：多表连接方式

 

第30讲预告：9月23日(周六)19:30-20:30，钉钉群直播，群号：35822460

内容1 : Nested Loop Join连接方式

内容2 : Merge Join连接方式

内容3 : Hash Join连接方式

 

多表连接方式

多表连接方式

三种连接方式：

nested loop join

merge join

hash join

支持所有join操作：

NATURAL INNER JOIN

INNER JOIN

LEFT/RIGHT OUTER JOIN

FULL OUTER JOIN

 

嵌套循环连接方式

Nested Loop Join

嵌套循环联接是最基本的联接操作，它可以用于任何联接条件。

 

 

 

Nested Loop Join图解

 

 

 

Materialized Nested Loop Join

 

 

我们使用下面的具体示例来探索执行器如何处理具体化嵌套循环连接的计划树，以及如何估计成本。

testdb=# EXPLAIN SELECT * FROM tbl_a AS a, tbl_b AS b WHERE a.id = b.id;

QUERY PLAN

-----------------------------------------------------------------------

Nested Loop (cost=0.00..750230.50 rows=5000 width=16)

Join Filter: (a.id = b.id)

-> Seq Scan on tbl_a a (cost=0.00..145.00 rows=10000 width=8)

-> Materialize (cost=0.00..98.00 rows=5000 width=8)

-> Seq Scan on tbl_b b (cost=0.00..73.00 rows=5000 width=8)

(5 rows)

 

Materialize成本估算

 

 

(Materialized) Nested Loop成本估算

 

 

 

Indexed Nested Loop Join

 

 

testdb=# EXPLAIN SELECT * FROM tbl_c AS c, tbl_b AS b WHERE c.id = b.id;

QUERY PLAN

--------------------------------------------------------------------------------

Nested Loop (cost=0.29..1935.50 rows=5000 width=16)

-> Seq Scan on tbl_b b (cost=0.00..73.00 rows=5000 width=8)

-> Index Scan using tbl_c_pkey on tbl_c c (cost=0.29..0.36 rows=1 width=8)

Index Cond：（id=b.id）

（4 rows）

 

具有外部索引扫描的嵌套循环联接的三种变体

 

 

Merge Join连接方式

Merge Join

 

 

 

Merge Join成本估算

 

 

testdb=# EXPLAIN SELECT * FROM tbl_a AS a, tbl_b AS b WHERE a.id = b.id AND b.id < 1000;

QUERY PLAN

-------------------------------------------------------------------------

Merge Join (cost=944.71..984.71 rows=1000 width=16)

Merge Cond: (a.id = b.id)

-> Sort (cost=809.39..834.39 rows=10000 width=8)

Sort Key: a.id

-> Seq Scan on tbl_a a (cost=0.00..145.00 rows=10000 width=8)

-> Sort (cost=135.33..137.83 rows=1000 width=8)

Sort Key: b.id

-> Seq Scan on tbl_b b (cost=0.00..85.50 rows=1000 width=8)

Filter: (id < 1000)

(9 rows)

 

Materialized Merge Join

 

 

 

Other Variations

 

 

强制使用merge join

testdb=# SET enable_hashjoin TO off;

testdb=# SET enable_nestloop TO off;

testdb=# EXPLAIN SELECT * FROM tbl_c AS c, tbl_b AS b WHERE c.id = b.id AND b.id < 1000;

QUERY PLAN

--------------------------------------------------------------------------------------

Merge Join (cost=135.61..322.11 rows=1000 width=16)

Merge Cond: (c.id = b.id)

-> Index Scan using tbl_c_pkey on tbl_c c (cost=0.29..318.29 rows=10000 width=8)

-> Sort (cost=135.33..137.83 rows=1000 width=8)

Sort Key: b.id

-> Seq Scan on tbl_b b (cost=0.00..85.50 rows=1000 width=8)

Filter: (id < 1000)

(7 rows)

 

materialized merge join with outer index scan

testdb=# SET enable_hashjoin TO off;

testdb=# SET enable_nestloop TO off;

testdb=# EXPLAIN SELECT * FROM tbl_c AS c, tbl_b AS b WHERE c.id = b.id AND b.id < 4500;

QUERY PLAN

--------------------------------------------------------------------------------------

Merge Join (cost=421.84..672.09 rows=4500 width=16)

Merge Cond: (c.id = b.id)

-> Index Scan using tbl_c_pkey on tbl_c c (cost=0.29..318.29 rows=10000 width=8)

-> Materialize (cost=421.55..444.05 rows=4500 width=8)

-> Sort (cost=421.55..432.80 rows=4500 width=8)

Sort Key: b.id

-> Seq Scan on tbl_b b (cost=0.00..85.50 rows=4500 width=8)

Filter: (id < 4500)

(8 rows)

 

indexed merge join with outer index scan

testdb=# SET enable_hashjoin TO off;

testdb=# SET enable_nestloop TO off;

testdb=# EXPLAIN SELECT * FROM tbl_c AS c, tbl_d AS d WHERE c.id = d.id AND d.id < 1000;

QUERY PLAN

--------------------------------------------------------------------------------------

Merge Join (cost=0.57..226.07 rows=1000 width=16)

Merge Cond: (c.id = d.id)

-> Index Scan using tbl_c_pkey on tbl_c c (cost=0.29..318.29 rows=10000 width=8)

-> Index Scan using tbl_d_pkey on tbl_d d (cost=0.28..41.78 rows=1000 width=8)

Index Cond: (id < 1000)

(5 rows)

 

Hash Join连接方式

Hash Join

 

 

In-Memory Hash Join

构建阶段：

将内部表的所有元组插入到一个批处理中

探测阶段：

将外部表的每个元组与批处理中的内部元组进行比较，如果满足连接条件，则进行连接

 

Hash Join

 

 

 

计划器处理转变

预处理

1、计划和转换CTE(如果查询中带有with列表，则计划器通过SS_process_ctes()函数处理每个with查询)

2、向上拉子查询

根据子查询的特点，改为自然连接查询。

testdb=# SELECT * FROM tbl_a AS a, (SELECT * FROM tbl_b) as b WHERE a.id = b.id;

testdb=# SELECT * FROM tbl_a AS a, tbl_b as b WHERE a.id = b.id;

3、将外部联接转换为内部联接

 

优化器可用规则

Getting the Cheapest Path

1、表数量小于12张，应用动态规划得到最优的计划

2、表数量大于12张，应用遗传查询优化器

参数 geqo_threshold指定的阈值(默认值为12)

3、分为不同的级别层次来处理

 

多表查询连接顺序选择

SGetting the Cheapest Path of a Triple-Table Query

testdb=# SELECT * FROM tbl_a AS a, tbl_b AS b, tbl_c AS c

testdb=# WHERE a.id = b.id AND b.id = c.id AND a.data < 40;

考虑3种组合：

{tbl_a,tbl_b,tbl_c}=min({tbl_a,{tbl_b,tbl_c}},{tbl_b,{tbl_a,tbl_c}},{tbl_c,{tbl_a,tbl_b}}).

创建多表查询的计划树· 此查询的EXPLAIN命令的结果如下所示

 

 

标签：rows,PostgreSQL,..,30,width,cost,tbl,多表,id
From： https://www.cnblogs.com/cnblogs5359/p/17722358.html

相关文章

  • MySQL高级SQL语句之多表连接
    目录MySQL高级SQL语句1.实际操作1.1新建表1.2查看表及内容1.3通配符1.4函数1.4.1数学函数：1.4.2聚合函数：1.4.3字符串函数：1.5语句1.5.1GROUPBYsql语句的执行顺序1.5.2HAVING1.5.3别名1.6查询1.6.1子查询1.6.2EXISTS1.7表连接1.7.1innerjoin内连接1.7.2leftjoi......
  • Exam DP-300: Administering Microsoft Azure SQL Solutions 微软Azure SQL Solutions
    作为该考试的考生，您应具备构建数据库解决方案方面的主题专业知识，这些解决方案旨在支持使用数据库构建的多种工作负载：企业内部SQLServerAzureSQL服务您是一名数据库管理员，负责管理使用SQLServer和AzureSQL服务构建的内部部署和云数据库。作为Azure数据库管理员，您......
  • PG-DBA培训16：PostgreSQL负载均衡分发与双主HA架构
    一、风哥PG-DBA培训16：PostgreSQL负载均衡分发与双主HA架构本课程由风哥发布的基于PostgreSQL数据库的系列课程，本课程属于PostgreSQL主从复制与高可用集群阶段之PostgreSQL负载均衡分发与双主HA架构，学完本课程可以掌握PostgreSQL高可用负载均衡解决方案，PostgreSQL+Keepalived流复制......
  • PG-DBA培训17：PostgreSQL连接池管理与代理分发
    一、风哥PG-DBA培训17：PostgreSQL连接池管理与代理分发本课程由风哥发布的基于PostgreSQL数据库的系列课程，本课程属于PostgreSQL主从复制与高可用集群阶段之PostgreSQL连接池管理与代理分发，学完本课程可以掌握PostgreSQL连接池管理与代理分发之Pgbouncer，Pgbouncer连接池技术基础，Post......
  • PG-DBA培训19：PostgreSQL高可用集群项目实战之Patroni
    一、风哥PG-DBA培训19：PostgreSQL高可用集群项目实战之Patroni课程目标：本课程由风哥发布的基于PostgreSQL数据库的系列课程，本课程属于PostgreSQL主从复制与高可用集群阶段之PostgreSQL高可用集群项目实战之Patroni，学完本课程可以掌握Patroni运行架构与基础知识，PostgreSQL+Patroni集......
  • PG-DBA培训20：PostgreSQL逻辑复制技术与项目实战
    一、风哥PG-DBA培训20：PostgreSQL逻辑复制技术与项目实战本课程由风哥发布的基于PostgreSQL数据库的系列课程，本课程属于PostgreSQL主从复制与高可用集群阶段之PostgreSQL逻辑复制技术与项目实战，学完本课程可以掌握PostgreSQL逻辑复制基础与架构，PostgreSQL逻辑复制配置之内置默认，Post......
  • 20230921
    20230921T378733成长grow思路按题目模拟即可。时空复杂度时间：\(O(9)\)空间：\(O(11)\)T378729清洁clean思路首先我们可以将图分为上下两个矩形，以便于我们计算，然后我们会发现圆会和矩形有两个交点，而这两个交点分别会在两条边上，如下图（只有上半部分有）：跟据图形，我们......
  • Learn Git in 30 days—— 第 23 天：修正 commit 过的版本历史记录 Part 5
    写的非常好的一个Git系列文章，强烈推荐原文链接：https://github.com/doggy8088/Learn-Git-in-30-days/tree/master/zh-cn 我们上一篇文章谈到的Rebase是用来将现有的两个分支进行「重新指定基础版本」，执行Rebase之后，也会改掉原本分支的起点(分支点移动了)，所以导致版本线......
  • 浅析Postgresql cache hit ratio
    一、查找cachehitratio 查看cachehitratio 这个东西其实放到其他数据库也是一样，如果你的内存对于系统的缓冲支持不足，需要的数据无法驻留在内存，经常会产生faultpage(有些数据库对于读取的数据不在内存中的一种叫法)，那就必须要要查看你的一个系统参数cachehitratio，......
  • COMException: 检索 COM 类工厂中 CLSID 为 {DB8CBF1C-D6D3-11D4-AA51-00A024EE30BD}
    没有注册类(异常来自HRESULT:0x80040154(REGDB_E_CLASSNOTREG)) "没有注册类(异常来自HRESULT:0x80040154(REGDB_E_CLASSNOTREG))"一般有两种情况，我最近做项目都遇到了》第一种：（生成平台的问题） 解决方法：在项目属性里设置“生成”=>“目标平台”为x86而不是默认的......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99