1、PostgreSQL查询优化深度探索陈线陈线柯普瑞企业柯普瑞企业ITIT学院学院PostgreSQLPostgreSQL授权讲师授权讲师资深资深数据库数据库讲师讲师PostgreSQL查询优化器概述01.PostgreSQL数据库是世界上最先进的开源关系数据库系统，查询优化器是数据库重要的核心模块之一查询优化的常规方法02.PREPARE预备、EXPLAIN执行计划、INDEX索引、PARTITION分区、VIEW视图、MV物化视图等等查询优化的深度探索03.查询树、逻辑重写、逻辑分解、统计信息与选择率、扫描路径、动态规划&遗传算法、连接路径等等主要内容：01 PostgreSQL查询优化器概

2、述3PostgreSQL数据库系统查询优化模块词法分析语法分析语义分析其它模块事务处理并发控制故障恢复规则成本语法分析模块查询执行模块数据存取模块01-1 语法分析模块4源码文件位置：src/backend/parser一词法分析：scan.l二语法分析：gram.y三语义分析:parse_*.c01-2 查询优化模块5源码文件位置：src/backend/optimizer一基于规则的优化RBO：Rule-Based Optimizer逻辑优化二基于代价的优化CBO：Cost-Based Optimizer物理优化01-3 查询执行模块6源码文件位置：src/backend/executor

3、01-4 数据存取模块7源码文件位置：src/backend/access01-5 其他模块8一事务处理二并发控制三故障恢复源码文件位置：src/backend/access/transamsrc/backedn/storage/lmgr02 查询优化的常规方法9 查询优化的目标通过调整SQL语句或设计索引，提高查询性能、减少响应时间，以期系统更高效地运行。常规查询优化有下列主要技术：一使用EXPLAIN命令：在执行查询之前，先使用EXPLAIN关键字加上要执行的查询语句，这样会返回查询计划信息。根据该信息可以判断查询是否需要重写或者添加索引等操作。二创建合适的索引(index)：对于经常被查

4、询的列，应该为其创建索引。索引可以大大提高查询速度，特别是当表中数据量非常大时。三分区表（Partitioning）：将大型表按照某个条件进行水平切分成更小的子表，每个子表都有自己的存储空间。这样可以提高查询效率并减少不必要的IO开销。四视图（View）：如果有复杂的查询逻辑，可以将其定义为视图，然后直接从视图中获取结果。这样可以避免重复编写相同的查询逻辑。五统计信息收集与更新：PostgreSQL内置了统计信息收集器，可以收集表、索引和系统状态的统计信息。及时更新这些统计信息可以确保查询优化器选择最佳的执行路径。六配置参数调优：通过修改PostgreSQL的配置文件postgresql.co

5、nf，可以调整一些影响查询性能的参数值，比如shared_buffers、work_mem等。七使用物理外部表（External Table）：如果需要处理大量的数据，而无法完全放入内存中，可以使用物理外部表来连接到外部数据源，利用外部数据库的功能来处理数据。02-1 EXPLAIN1002-2 创建索引1102-3分区表（Partitioning）1202-4视图（View）和物化视图MV1302-5统计信息收集与更新1402-6 配置参数调优1502-7使用物理外部表（External Table）1603 PostgreSQL查询优化深度探索17源码中优化器的5个基本目录：plan:总入

6、口，调用逻辑优化与物理优化prep：逻辑优化path：路径物理优化geqo：遗传算法物理优化（表连接超过11）util：辅助工具模块，公共函数，供所有目录使用。03-1 目录结构图18Plan 模块为总调用模块，Prep 和 Path 被它调用。在执行中，从Plan模块入口，先调用 Prep 模块进行预处理，再调用Path模块进行优化。Path模块中有开关，指示是 否启用遗传算法进行优化，如果启用，且连接的表超过 ll，就调用 geqo 目录中的遗传算法进行优化。util 模块为辅助工具模块，提供其他模块使用的工具函数。03-2 目录结构图：prep19prep 目录主要处理逻辑优化中的逻辑重

7、写的部分，对投影、选择条件、集合操作、连接操作都进行了重写03-2 目录结构图：path20path 目录主要生成物理路径的部分，包括生成扫描路径、连接路径等03-3 目录结构图：geqo21geqo 目录主要是实现了一种物理路径的搜索算法遗传算法，通过这种算法可以处理参与连接的表比较多的情况。相较于多表连接的穷举而言，geqo遗传算法，提供了更好的性能03-3 目录结构图：utils22utils 目录则提供了大量的公共函数，其他各个目录中均可能会调用这些函数。03-4 深度探索23PostgreSQL查询优化，可以从以下方面进一步深入：一查询树二逻辑重写优化三逻辑分解优化四统计信息和选择率

8、五扫描路径六动态规划和遗传算法七连接路径PostgreSQL查询优化器概述01.PostgreSQL数据库是世界上最先进的开源关系数据库系统，查询优化器是数据库重要的核心模块之一查询优化的常规方法02.PREPARE预备、EXPLAIN执行计划、INDEX索引、PARTITION分区、VIEW视图、MV物化视图等等查询优化的深度探索03.查询树、逻辑重写、逻辑分解、统计信息与选择率、扫描路径、动态规划&遗传算法、连接路径等等主题回顾：THANKSTHANKSPG授权培训中心南京柯普瑞信息技术有限公司公司地址：南京市秦淮区中山东路300号长发中心A栋23楼联系电话： 联 系 人：程老师网址：www.china-扫一扫有惊喜