PostgreSQL的查询语句的连接方式与查询计划比较--多表连接（二）

PostgreSQL多表连接优化
最新推荐文章于 2025-05-19 21:08:09 发布
原创 最新推荐文章于 2025-05-19 21:08:09 发布 · 542 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文通过多个步骤对比分析了PostgreSQL中不同连接条件对查询计划的影响，包括连接条件的增减及其对连接顺序和算法选择的影响。
续： PostgreSQL的查询语句的连接方式与查询计划比较--多表连接（一）

--step5：对比step3，增加连接条件（连接条件变多但多余的条件可推导出相等故可合并）

test=# explain select * from A, B, C, D where A.c1=B.c1 and B.c1=C.c1 AND C.c1=D.c1 and A.c1=C.c1 and B.c1=D.c1;

                                  QUERY PLAN

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

 Merge Join  (cost=541.37..27731.14 rows=1770590 width=48)

   Merge Cond: (a.c1 = c.c1)

   ->  Merge Join  (cost=270.68..562.65 rows=18818 width=24)

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

         ->  Sort  (cost=135.34..140.19 rows=1940 width=12)

               Sort Key: a.c1

               ->  Seq Scan on a  (cost=0.00..29.40 rows=1940 width=12)

         ->  Sort  (cost=135.34..140.19 rows=1940 width=12)

               Sort Key: b.c1

               ->  Seq Scan on b  (cost=0.00..29.40 rows=1940 width=12)

   ->  Materialize  (cost=270.68..609.70 rows=18818 width=24)

         ->  Merge Join  (cost=270.68..562.65 rows=18818 width=24)

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

               ->  Sort  (cost=135.34..140.19 rows=1940 width=12)

                     Sort Key: c.c1

                     ->  Seq Scan on c  (cost=0.00..29.40 rows=1940 width=12)

               ->  Sort  (cost=135.34..140.19 rows=1940 width=12)

                     Sort Key: d.c1

                     ->  Seq Scan on d  (cost=0.00..29.40 rows=1940 width=12)

(19 rows)

分析：

1)   连接条件与setp3比，查询计划没有特殊变化

2)   连接条件可以推导出：A.c1、B.c1、C.c1、D.c1是相等的，所以和step3的查询计划没有什么变化

 

--step6：对比step3，增加连接条件

test=# explain select * from A, B, C, D where A.c1=B.c1 and B.c1=C.c1 AND C.c1=D.c1 and A.c2=C.c2 and B.c2=D.c2;

                                        QUERY PLAN

 

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

----------

 Merge Join  (cost=547.27..569.10 rows=44 width=48)

   Merge Cond: ((a.c1 = b.c1) AND (d.c2 = b.c2))

   ->  Sort  (cost=411.93..414.21 rows=912 width=36)

         Sort Key: a.c1, d.c2

         ->  Hash Join  (cost=301.90..367.09 rows=912 width=36)

               Hash Cond: (d.c1 = a.c1)

               ->  Seq Scan on d  (cost=0.00..29.40 rows=1940 width=12)

               ->  Hash  (cost=300.72..300.72 rows=94 width=24)

                     ->  Merge Join  (cost=270.68..300.72 rows=94 width=24)

                           Merge Cond: ((a.c1 = c.c1) AND (a.c2 = c.c2))

                           ->  Sort  (cost=135.34..140.19 rows=1940 width=12)

                                 Sort Key: a.c1, a.c2

                                 ->  Seq Scan on a  (cost=0.00..29.40 rows=1940

width=12)

                           ->  Sort  (cost=135.34..140.19 rows=1940 width=12)

                                 Sort Key: c.c1, c.c2

                                 ->  Seq Scan on c  (cost=0.00..29.40 rows=1940

width=12)

   ->  Sort  (cost=135.34..140.19 rows=1940 width=12)

         Sort Key: b.c1, b.c2

         ->  Seq Scan on b  (cost=0.00..29.40 rows=1940 width=12)

(19 rows)

分析：

1)   连接条件与setp3比，连接次序依旧是：AB->ABC->ABCD

 

--step6：对比step3、step4，增加新的连接条件，减少可推导出4个关系相等的连接条件

test=# explain select * from A, B, C, D where A.c1=B.c1 AND C.c1=D.c1 and A.c2=C.c2 and B.c2=D.c2;

                                  QUERY PLAN

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

 Merge Join  (cost=3797.43..4168.23 rows=8853 width=48)

   Merge Cond: ((a.c2 = c.c2) AND (b.c2 = d.c2))

   ->  Sort  (cost=1898.72..1945.76 rows=18818 width=24)

         Sort Key: a.c2, b.c2

         ->  Merge Join  (cost=270.68..562.65 rows=18818 width=24)

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

               ->  Sort  (cost=135.34..140.19 rows=1940 width=12)

                     Sort Key: a.c1

                     ->  Seq Scan on a  (cost=0.00..29.40 rows=1940 width=12)

               ->  Sort  (cost=135.34..140.19 rows=1940 width=12)

                     Sort Key: b.c1

                     ->  Seq Scan on b  (cost=0.00..29.40 rows=1940 width=12)

   ->  Sort  (cost=1898.72..1945.76 rows=18818 width=24)

         Sort Key: c.c2, d.c2

         ->  Merge Join  (cost=270.68..562.65 rows=18818 width=24)

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

               ->  Sort  (cost=135.34..140.19 rows=1940 width=12)

                     Sort Key: c.c1

                     ->  Seq Scan on c  (cost=0.00..29.40 rows=1940 width=12)

               ->  Sort  (cost=135.34..140.19 rows=1940 width=12)

                     Sort Key: d.c1

                     ->  Seq Scan on d  (cost=0.00..29.40 rows=1940 width=12)

(22 rows)

分析：

1)   与step3比，新增AC、BD之间的连接条件(A.c2=C.c2 and B.c2=D.c2)

2)   与step3比，减少可推导出AC之间相等的连接条件(B.c1=C.c1)

3)   连接条件与setp3比，连接次序是紧密树方式：AB、CD->ABCD

4)   连接算法采用的是归并连接（因为每2个关系，都依据连接条件排序）

 

--step7：对比step3，减少连接条件

test=# explain select * from A, B, C, D where A.c1=B.c1 and B.c1=C.c1;

                                  QUERY PLAN

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

 Nested Loop  (cost=324.33..4433560.06 rows=354117900 width=48)

   ->  Hash Join  (cost=324.33..7052.06 rows=182535 width=36)

         Hash Cond: (a.c1 = c.c1)

         ->  Merge Join  (cost=270.68..562.65 rows=18818 width=24)

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

               ->  Sort  (cost=135.34..140.19 rows=1940 width=12)

                     Sort Key: a.c1

                     ->  Seq Scan on a  (cost=0.00..29.40 rows=1940 width=12)

               ->  Sort  (cost=135.34..140.19 rows=1940 width=12)

                     Sort Key: b.c1

                     ->  Seq Scan on b  (cost=0.00..29.40 rows=1940 width=12)

         ->  Hash  (cost=29.40..29.40 rows=1940 width=12)

               ->  Seq Scan on c  (cost=0.00..29.40 rows=1940 width=12)

   ->  Materialize  (cost=0.00..39.10 rows=1940 width=12)

         ->  Seq Scan on d  (cost=0.00..29.40 rows=1940 width=12)

(15 rows)

分析：

1)   与setp3比，连接次序依旧是：AB->ABC->ABCD

2)   在关系D上，不存在连接条件，所以对于关系D和ABC这个新关系，做得是笛卡尔积

怎样优化 PostgreSQL 中对多表关联查询的连接顺序？
2401_86074221的博客
07-13 1568
总之，通过对表大小和数据分布的分析、合理利用索引、考虑连接条件的选择性、尝试不同的连接算法、分解复杂查询以及确保基于成本的优化器有准确的统计信息，我们能够有效地优化 PostgreSQL 中多表关联查询的连接顺序，提高数据库的性能和响应速度，为用户提供更好的服务体验。优化多表关联查询的连接顺序是一个不断尝试和调整的过程，需要结合实际的数据库结构、数据量和查询需求来综合考虑。例如，如果有一个条件能够筛选出订单表中 80%的数据，而另一个条件只能筛选出 20%的数据，那么优先处理选择性高的条件对应的连接。
如何优化 PostgreSQL 中的连接查询性能？
技术笔记
07-19 1352
左连接则会返回左表中的所有行以及与右表中匹配的行，如果右表中没有匹配的行，则相应的列值为 NULL ，这就好像是先把左边的拼图块都摆出来，然后再去找能与之匹配的右边的拼图块。全外连接则会返回两个表中的所有行，如果没有匹配的行，则相应的列值为 NULL ，这相当于把所有的拼图块不管能不能匹配都放在一起。而数据库中的连接查询，就像是超市的仓库管理员，要快速而准确地从不同的货架（表）中找到相关的商品（数据），并将它们组合在一起供前台销售（查询结果）。在进行连接查询之前，尽量减少参与连接的表的数据量。
PostgreSQL查询计划剖析
寒冰屋的专栏
07-01 1413
目录 介绍 PostgreSQL数据库中的查询生命周期 数据设置 进入计划阶段 PostgreSQL解释一个查询 一起解释分析 什么是数据库中的缓冲区和缓存？ VERBOSE命令参数 Postgres中的FORMAT解释 查询计划的元素 查询的节点 查询计划器中的成本 数据库计划和执行 从这往哪儿走 介绍 了解PostgreSQL查询计划对于开发人员和数据库管理员来说都是一项关键技能。这可能是我们开始优化查询的第一件事，也是验证我们优化的查询是否确实按照我们期望的方式优化
PG内两表JOIN查询
qq_40207018的博客
07-15 1408
在PostgreSQL上对SDB内的两个集合进行关联（JOIN）查询。要求如下： 1.为两个集合在PG内创建两张外表； 2.查看创建的两张外表的表结构； 3.JOIN关联查询需要走索引； 创建两张外表： CREATE FOREIGN TABLE student ( sid int, sname text, sage text, ssex text, cid text, sprovince text, scity text, sphone text, semail text ) SERVER sdb.
PostgreSQL 的查询计划（Query Plan）及如何读取它
My_wife_QBL的博客
08-23 1887
查询计划是数据库系统用于执行 SQL 语句的一个详细计划。它描述了数据库如何获取所需的数据，包括使用哪些索引、如何连接表、读取数据的顺序等。查询计划是数据库优化器生成的，它基于 SQL 查询的结构、表的统计信息、索引的存在等因素进行决策。解析：将 SQL 查询解析成内部表示。重写：根据规则修改查询以优化它。规划：生成执行计划并选择最佳的执行策略。执行：根据生成的执行计划读取和处理数据。理解 PostgreSQL 的查询计划对于优化数据库性能至关重要。通过使用EXPLAIN和。
sr_plan：在PostgreSQL中保存和恢复查询计划
02-05
在PostgreSQL中保存和还原查询计划 基本原理 sr_plan看起来像Oracle Outline系统。 它可以用来锁定执行计划。 如果您不信任计划者或不能制定更好的计划，则很有必要。 编译安装 make USE_PGXS=1 make USE_PGXS=1 install 并修改您的postgres配置： shared_preload_libraries = 'sr_plan' 用法 在数据库中安装扩展： CREATE EXTENSION sr_plan; 如果要保存查询计划，则必须设置变量： set sr_plan . write_mode = true; 现在，所有后续
postgresql-dart：Dart PostgreSQL驱动程序：支持扩展的查询格式，二进制协议和语句重用
02-05
这种方式可以提高性能，因为查询计划只需要在第一次执行时生成，后续的执行只需重新使用该计划。这减少了服务器解析查询的时间，对于需要多次执行相同查询的场景特别有用。 2. **二进制协议支持** `postgresql-...
PostgreSQL JDBCL连接：postgresql-42.2.5.jar包.zip
06-20
在本篇中，我们将深入探讨如何使用`postgresql-42.2.5.jar`这个PostgreSQL JDBC驱动包来建立和管理与PostgreSQL数据库的连接。 **1. PostgreSQL JDBC驱动介绍** `postgresql-42.2.5.jar`是PostgreSQL官方提供的...
精选资源
PostgreSQL语法、连接
01-27
如果需要合并多个表的数据，可以使用连接（JOIN）操作。 3. 更新数据（UPDATE语句） UPDATE语句用于更新表中的已有记录。基本语法如下： ```sql UPDATE table_name SET column1 = new_value1, column2 = new_value...
怎样在 PostgreSQL 中优化对多表关联的连接条件选择？
技术笔记
07-16 1602
在 PostgreSQL 中优化多表关联的连接条件选择是提高查询性能的关键。我们需要理解多表关联的基本概念，选择合适的连接方式和连接条件，优化连接顺序，使用子查询和临时表来优化连接，以及根据实际情况进行索引优化。通过这些优化措施，我们可以像驾驶一辆高性能的汽车一样，在数据库的世界中快速、准确地获取我们需要的信息。希望本文能够对你在 PostgreSQL 中优化多表关联的连接条件选择有所帮助。
PostgreSQL查询计划和执行计划的分析
互联网知识分享
09-26 430
查询重写和优化是指通过改变查询语句的结构和执行计划，以达到提高查询性能的目的。查询优化：在分区和分片的情况下，可以通过查询优化来提高查询性能。删除不必要的索引：索引的创建和维护需要消耗额外的存储空间和计算资源，因此应避免创建不必要的索引。缓存查询结果：如果查询的结果是经常被使用的，可以将查询结果缓存起来，避免重复执行查询操作。使用表分区和分片：将大表分割成多个较小的表，可以减少全表扫描的数据量，提高查询性能。重写子查询：子查询可以导致性能问题，可以考虑将子查询重写为连接查询或临时表查询。
自己写的postgreSQL查询语句
happyuu的博客
09-12 1054
import psycopg2 class PostgreConn(): ''' 数据库连接类 ''' def __init__(self, database, user, password, host, port): self.conn = psycopg2.connect(database=database, user=user, pass...
PostgreSQL查询计划中的路径-BitmapHeapPath-辨析（二）
那海蓝蓝的博客
03-13 362
create table t5(a int, b int);create table t6(a int, b int);insert into t5 values(generate_series(1,10000,1), generate_series(2,20000,2));insert into t6 values(generate_series(1,10000,1), generate_ser
PostgreSQL 的一个简单连接和查询操作——示例
小强快跑~~
09-23 1120
表和数据： CREATE TABLE weather ( city varchar(80), temp_lo int, -- low temperature temp_hi int
理解PostgreSQL查询执行计划（三）--复杂操作篇
weixin_37578680的博客
05-19 799
在本系列的前一篇文章中，我们探讨了如何解读执行计划输出的单行结构，并介绍了所有基本的数据获取操作（解释树中的节点）。今天，我们将深入讨论更复杂的操作。
PostgreSQL的查询语句的连接方式与查询计划比较--简单语句
那海蓝蓝的博客
03-13 500
--Step1：准备数据，创建表，插入数据，注意数据的设计，每个表是3种情况，目的是看字段为NULL时，各种连接的处理方式 test=# create table A (c1 int, c2 int); CREATE TABLE test=# create table B (c1 int, c2 int); CREATE TABLE test=# test=# insert into A val
【PostgreSQL内核学习（八）—— 查询执行（查询执行策略）】
qq_43899283的博客
07-22 1432
查询编译器将用户提交的SQL查询语句转变成执行计划之后，由查询执行器继续执行查询的处理过程。在查询执行阶段，将根据执行计划进行数据提取、处理、存储等一系列活动，以完成整个查询执行过程。查询执行过程更像一个结构良好的裸机，执行计划为输人，执行相应的功能。因此，本章重点介绍查询执行器的框架结构执行方式，结合实例为读者说明数据库执行计划的相关步骤,帮助读者进一步理解PostgreSQL的查询执行过程。查询执行器的框架结构如图6-1所示。同查询编译器一样，查询执行器也是被函数调用，只是调用的顺序上。
如何监控和分析 PostgreSQL 中的查询执行计划？
技术笔记
07-06 2226
监控和分析 PostgreSQL 中的查询执行计划是数据库性能优化的关键步骤。通过深入理解执行计划的各个元素，结合实际的业务需求和数据特点，采取合适的优化策略，可以显著提高数据库的性能，为应用程序提供更快速和高效的服务。希望以上内容能帮助您全面了解如何在 PostgreSQL 中监控和分析查询执行计划，并有效地进行性能优化。🎉相关推荐🍅关注博主🎗️带你畅游技术世界，不错过每一次成长机会！📢学习做技术博主创收📚领书：PostgreSQL 入门到精通.pdf📙PostgreSQL 中文手册。
PostgreSQL源码分析——SeqScan
内核思考
06-18 1125
这里，我们分析一下顺序扫描的过程，以最简单的语句为例，分析一下查询的过程。
PostgreSQL查询表连接语句
最新发布
08-28
PostgreSQL 支持多种表连接方式，以下是不同连接方式的简介及示例： - **左连接（LEFT JOIN）**：返回左表中的所有记录以及右表中匹配的记录，若右表无匹配记录，则右表部分用 NULL 填充。示例如下： ```sql -- 假设有表 table1 和 table2，连接它们 SELECT * FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; ``` - **右连接（RIGHT JOIN）**：与左连接相反，返回右表中的所有记录以及左表中匹配的记录，若左表无匹配记录，则左表部分用 NULL 填充。 - **全外连接（FULL OUTER JOIN）**：返回左表和右表中的所有记录，当某行在另一表中无匹配记录时，用 NULL 填充。示例如下： ```sql SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name; ``` - **内连接（INNER JOIN）**：只返回两个表中匹配的记录。可以连接两个或多个表，示例如下： ```sql -- 连接两个表 SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; -- 连接三个表 SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name INNER JOIN table3 ON table2.another_column = table3.another_column; ``` - **自连接（Self - Join）**：将表与自身进行连接，常用于查询分层数据或比较同一表的行。例如查询分层数据示例： ```sql SELECT e1.employee_name, e2.manager_name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.employee_id; ``` - **交叉连接（CROSS JOIN）**：返回两个表的笛卡尔积，即左表的每一行与右表的每一行组合。示例如下： ```sql SELECT * FROM table1 CROSS JOIN table2; ``` - **自然连接（NATURAL JOIN）**：基于两个表中具有相同名称的列进行连接。示例如下： ```sql SELECT * FROM table1 NATURAL JOIN table2; ``` - **横向连接（LATERAL JOIN）**：允许在查询中使用子查询引用外部查询的列。例如计算博客年龄使用 LATERAL JOIN 获取报表。 此外，表别名可用于使查询更具可读性，可对长表名使用表别名、在连接子句中使用表别名以及在自连接中使用表别名等 [^1]。
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值