【PostgreSQL案例】我要查的表没有在执行计划中
问题:查的表没有在执行计划中
sql:
SELECT*
FROM(SELECTA.column1 as "column1",--中间省略很多A字段A.column99 as "column99"fromtable_a Aleft join (SELECTlzl_idfromtable_a AAinner join table_b BB ON AA.lzl_key = BB.lzl_idwhereAA.column_code = '1'GROUP BYlzl_id) B ON B.lzl_id = A.lzl_keywhereA.flagflagflag = '1'AND A.typetypetype = '2') TEMP
limit100
offset1000
执行计划:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------Limit (cost=2.84..5.68 rows=1 width=1105) (actual time=0.038..0.039 rows=0 loops=1)Buffers: shared hit=2-> Seq Scan on table_a a (cost=0.00..2.84 rows=1 width=1105) (actual time=0.036..0.037 rows=0 loops=1)Filter: (((flagflagflag)::text = '1'::text) AND ((typetypetype)::text = '2'::text))Rows Removed by Filter: 38Buffers: shared hit=2Planning Time: 0.184 msExecution Time: 0.066 ms
可以看到,sql本身是比较复杂的,SQL的逻辑查了3次表,总共查了2张表。table_a 在执行计划中我可以理解,但是需要查的table_b根本没在执行计划里面!这个执行计划只不过是简单的全表扫描了table_a。
分析的心路历程
中间其实想过很多可能,不过最有可能的是逻辑优化了,也就是说pg优化器认为table_b不需要查。
观察sql发现sql最终只查询了table_a的字段,没有查table_b。此时任意增加一个中间表B的字段,sql执行计划看上去就“正常”了,访问了table_b
explain SELECT*
FROM(SELECTA.column1 as "column1",--中间省略很多A字段A.column99 as "column99",B.lzl_id --新增一个B中间表的字段fromtable_a Aleft join (SELECTlzl_idfromtable_a AAinner join table_b BB ON AA.lzl_key = BB.lzl_idwhereAA.column_code = '1'GROUP BYlzl_id) B ON B.lzl_id = A.lzl_keywhereA.flagflagflag = '1'AND A.typetypetype = '2') TEMP
limit100
offset1000
---------------------------------------------------------------------------------------------------------------------------------------------------------------Limit (cost=14.69..17.67 rows=1 width=1113)-> Nested Loop Left Join (cost=11.72..14.69 rows=1 width=1113)Join Filter: (bb.lzl_id = a.lzl_key)-> Seq Scan on table_a a (cost=0.00..2.84 rows=1 width=1113)Filter: (((flagflagflag)::text = '1'::text) AND ((typetypetype)::text = '2'::text))-> Group (cost=11.72..11.74 rows=5 width=8)Group Key: bb.lzl_id-> Sort (cost=11.72..11.73 rows=5 width=8)Sort Key: bb.lzl_id-> Nested Loop (cost=0.15..11.66 rows=5 width=8)-> Seq Scan on table_a aa (cost=0.00..2.70 rows=1 width=8)Filter: ((company_code)::text = '1'::text)-> Index Only Scan using idx_table_b_lzl_id on table_b bb (cost=0.15..8.83 rows=13 width=8)Index Cond: (lzl_id = aa.lzl_key)
这看上去跟left join有关系,但是简单想想又不对,因为右表的结果是会影响查询的最终结果的,不应该不去查右表。随便来个简单的left join,右表会被扫描
explain select lzlleft.a from lzlleft left join lzlright on lzlleft.a=lzlright.a;QUERY PLAN
--------------------------------------------------------------------Hash Left Join (cost=1.04..15.47 rows=320 width=4)Hash Cond: (lzlleft.a = lzlright.a)-> Seq Scan on lzlleft (cost=0.00..13.20 rows=320 width=4)-> Hash (cost=1.02..1.02 rows=2 width=4)-> Seq Scan on lzlright (cost=0.00..1.02 rows=2 width=4)
但是,在中间表B中,有个关键字GROUP BY
。如果把GROUP BY
去掉,那么无论有没有查询B的字段,都会访问table_b。
我们再在测试表中加个group by看看结果
> select * from lzlleft;a | b
---+-----1 | zzz
(1 row)Time: 0.259 ms
> select * from lzlright;a | b
---+-------1 | qwer1 | poiuy > select lzlright.b from lzlleft full join lzlright on lzlleft.b=lzlright.b group by lzlright.b;b
--------[null]poiuyqwer
(3 rows)
这里就意识到了group by出来的结果集一定有一个特性——唯一性。
我们再在测表里加group by
explain select lzlleft.a from lzlleft left join (select a from lzlright group by a) c on lzlleft.a=c.a;QUERY PLAN
----------------------------------------------------------
Seq Scan on lzlleft (cost=0.00..13.20 rows=320 width=4)
右表不查了!
根据右表唯一性的原则,下面还可以有一些骚操作:
--distinct确保右表唯一
> explain select lzlleft.a from lzlleft left join (select distinct a from lzlright) c on lzlleft.a=c.a;QUERY PLAN
----------------------------------------------------------
Seq Scan on lzlleft (cost=0.00..13.20 rows=320 width=4)
--唯一索引确保右表唯一,哪怕是select a from lzlright
> explain select lzlleft.a from lzlleft left join (select a from lzlright) c on lzlleft.a=c.a;QUERY PLAN
-----------------------------------------------------------------------
Hash Left Join (cost=17.20..49.12 rows=512 width=4)Hash Cond: (lzlleft.a = lzlright.a)-> Seq Scan on lzlleft (cost=0.00..13.20 rows=320 width=4)-> Hash (cost=13.20..13.20 rows=320 width=4)-> Seq Scan on lzlright (cost=0.00..13.20 rows=320 width=4)
(5 rows)Time: 0.510 ms
> create unique index idx_right on lzlright(a);
CREATE INDEX
Time: 3.576 ms
> explain select lzlleft.a from lzlleft left join (select a from lzlright) c on lzlleft.a=c.a;QUERY PLAN
----------------------------------------------------------
Seq Scan on lzlleft (cost=0.00..13.20 rows=320 width=4)
(1 row)
到这里来个分析小结:只要右表的数据是唯一的且只查询左表数据时,不需要真的去访问右表 。所以这不是一个bug,而是PG优化器的特性,是符合逻辑的。
源码分析
本期没有源码分析~
优化器源码实在太难了,这里就找了下优化器源码的注释看了下。可以搜索关键字unique-ify
,有这么一句话:
* Also, this routine and others in this module accept the special JoinTypes* JOIN_UNIQUE_OUTER and JOIN_UNIQUE_INNER to indicate that we should* unique-ify the outer or inner relation and then apply a regular inner* join. These values are not allowed to propagate outside this module,* however. Path cost estimation code may need to recognize that it's* dealing with such a case --- the combination of nominal jointype INNER* with sjinfo->jointype == JOIN_SEMI indicates that.
特殊的JoinTypes:JOIN_UNIQUE_INNER 和JOIN_UNIQUE_OUTER ,尝试把外表和内表连接唯一化后,成为inner join。Path代价估算需要考虑这种场景。
与oracle、mysql优化器的对比
对比看下oracle、mysql优化器有没有类似的逻辑优化提升
--oracle
create table lzlleft(a number);
create table lzlright(a number);select lzlleft.a from lzlleft left join (select distinct a from lzlright) c on lzlleft.a=c.a;
--group by 唯一性
SQL> select lzlleft.a from lzlleft left join (select a from lzlright group by a) c on lzlleft.a=c.a; no rows selectedExecution Plan
----------------------------------------------------------
Plan hash value: 3533354041---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 1 | 26 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL | LZLLEFT | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 13 | 3 (34)| 00:00:01 |
| 4 | HASH GROUP BY | | 1 | 13 | 3 (34)| 00:00:01 |
| 5 | TABLE ACCESS FULL| LZLRIGHT | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - access("LZLLEFT"."A"="C"."A"(+))
--ditinct 唯一
SQL> select lzlleft.a from lzlleft left join (select distinct a from lzlright) c on lzlleft.a=c.a;no rows selectedExecution Plan
----------------------------------------------------------
Plan hash value: 3859658234---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 1 | 26 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL | LZLLEFT | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 13 | 3 (34)| 00:00:01 |
| 4 | HASH UNIQUE | | 1 | 13 | 3 (34)| 00:00:01 |
| 5 | TABLE ACCESS FULL| LZLRIGHT | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - access("LZLLEFT"."A"="C"."A"(+))
--mysql
create table lzlleft(a int primary key);
create table lzlright(a int primary key);
--group by唯一
explain select lzlleft.a from lzlleft left join (select a from lzlright group by a) c on lzlleft.a=c.a;
+----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------+------+----------+-------------+
| 1 | PRIMARY | lzlleft | NULL | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using index |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | lzldb.lzlleft.a | 2 | 100.00 | Using index |
| 2 | DERIVED | lzlright | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------+------+----------+-------------+
--distinct唯一
explain select lzlleft.a from lzlleft left join (select distinct a from lzlright) c on lzlleft.a=c.a;
+----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------+------+----------+-------------+
| 1 | PRIMARY | lzlleft | NULL | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using index |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | lzldb.lzlleft.a | 2 | 100.00 | Using index |
| 2 | DERIVED | lzlright | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------+------+----------+-------------+
综上,oracle、mysql均不会对left join只查左表且右表唯一做优化,他们会访问右表。
pg优化器确实是有些东西的。
相关文章:
【PostgreSQL案例】我要查的表没有在执行计划中
问题:查的表没有在执行计划中 sql: SELECT* FROM(SELECTA.column1 as "column1",--中间省略很多A字段A.column99 as "column99"fromtable_a Aleft join (SELECTlzl_idfromtable_a AAinner join table_b BB ON AA.lzl_key BB.lzl_…...

《程序猿入职必会(5) · CURD 页面细节规范 》
📢 大家好,我是 【战神刘玉栋】,有10多年的研发经验,致力于前后端技术栈的知识沉淀和传播。 💗 🌻 CSDN入驻不久,希望大家多多支持,后续会继续提升文章质量,绝不滥竽充数…...

操作系统面试知识点总结5
#来自ウルトラマンメビウス(梦比优斯) 1 IO管理概述 1.1 I/O 设备 I/O 设备的类型分类。 1.1.1 按使用特性 人机交互类外部设备,例如打印机、显示器等。存储设备,例如磁盘、光盘等。网络通信设备,例如网络接口等。 1…...

BigInteger和BigDecimal类
一、应用场景 1. BigInteger 类 目前,我们学过最大的是long类型,但是,在实际开发时候,很有可能遇见超出long类型范围的数,我们就需要用BigInteger类; ① add 加 ② subtract 减 ③ multiply 乘…...

2024最新Uniapp的H5网页版添加谷歌授权验证
现在教程不少,但是自从谷歌升级验证之后,以前的老教程就失效了,现在写一个新教程以备不时之需。 由于众所周知的特殊原因,开发的时候一定注意网络环境,如果没有梯子是无法进行开发的哦~ clientID的申请方式我就不再进…...
学习java第一百四十四天
Spring通知有哪些类型? 在AOP术语中,切面的工作被称为通知。通知实际上是程序运行时要通过Spring AOP框架来触发的代码段。 Spring切面可以应用5种类型的通知: 前置通知(Before):在目标方法被调用之前调用通…...

Meta 发布 Llama3.1,一站教你如何推理、微调、部署大模型
最近这一两周看到不少互联网公司都已经开始秋招提前批了。不同以往的是,当前职场环境已不再是那个双向奔赴时代了。求职者在变多,HC 在变少,岗位要求还更高了。 最近,我们又陆续整理了很多大厂的面试题,帮助一些球友解…...
XSSFWorkbook 和 SXSSFWorkbook 的区别
在现代办公环境中,处理 Excel 文件是一个常见的任务。Apache POI 是一个流行的 Java 库,能够读写 Microsoft Office 文档。对于处理 Excel 文件,Apache POI 提供了 XSSFWorkbook 和 SXSSFWorkbook 两个类。本文将详细介绍这两个类的特点和适用…...

会议主题:NICE Seminar|神经组合优化方法的大规模泛化研究(南方科技大学王振坤副研究员)
数据增强 获得更多解 TSP问题 最优解与序列无关,数据增强 ICML 2024 Position Rethinking Post-Hoc Search-Based Neural Approaches for Solving Large-Scale Traveling Salesman Problems...

昇思25天学习打卡营第22天|CycleGAN图像风格迁移互换
相关知识 CycleGAN 循环生成网络,实现了在没有配对示例的情况下将图像从源域X转换到目标域Y的方法,应用于域迁移,也就是图像风格迁移。上章介绍了可以完成图像翻译任务的Pix2Pix,但是Pix2Pix的数据必须是成对的。CycleGAN中只需…...

《Java初阶数据结构》----6.<优先级队列之PriorityQueue底层:堆>
前言 大家好,我目前在学习java。之前也学了一段时间,但是没有发布博客。时间过的真的很快。我会利用好这个暑假,来复习之前学过的内容,并整理好之前写过的博客进行发布。如果博客中有错误或者没有读懂的地方。热烈欢迎大家在评论区…...
Matrix Equation(高斯线性异或消元+bitset优化)
题目: 登录—专业IT笔试面试备考平台_牛客网 思路: 我们发现对于矩阵C可以一列一列求。 mod2,当这一行相乘1的个数为奇数时,z(i,j)为1,偶数为0,是异或消元。 对于b[i,j]*c[i,j],b[i,j]可以…...

【一图学技术】2.API测试9种方法图解
9种API测试方法 冒烟测试:冒烟测试是一种快速的表面级测试,用于验证软件的基本功能是否正常工作,以确定是否值得进行更详细的测试。功能测试:功能测试是验证软件是否符合预期功能要求的测试类型。它涉及对每个功能进行测试&#…...

力扣刷题----42. 接雨水
给定 n 个非负整数表示每个宽度为 1 的柱子的高度图,计算按此排列的柱子,下雨之后能接多少雨水。 输入:height [0,1,0,2,1,0,1,3,2,1,2,1] 输出:6 解释:上面是由数组 [0,1,0,2,1,0,1,3,2,1,2,1] 表示的高度图…...

【论文精读】 | 基于图表示的视频抑郁症识别的两阶段时间建模框架
文章目录 0、Description1、Introduction2、Related work2.1 Relationship between depression and facial behaviours2.2 Video-based automatic depression analysis2.3 Facial graph representation 3、The proposed two-stage approach3.1 Short-term depressive behaviour…...
采集PCM,将base64片段转换为wav音频文件
需求 开始录音——监听录音数据——结束录音 在监听录音数据过程中:客户端每100ms给前端传输一次数据(pcm数据转成base64),前端需要将base64片段解码、合并、添加WAV头、转成File、上传到 OSS之后将 url 给到服务端处理。 {num…...

eclipse ui bug
eclipse ui bug界面缺陷,可能项目过多,特别maven项目过多,下载,自动编译,加载更新界面异常 所有窗口死活Restore不回去了 1)尝试创建项目,还原界面,失败 2)关闭所有窗口&…...
前端获取blob文件格式的两种格式
第一种,后台传递给前台是base64格式的JSON数据 这时候前台拿到base64格式的数据可以通过内置的atob解码方法结合new Uint8Array和new Blob方法转换成blob类型的数据格式,然后可以使用blob数据格式进行操作,虽然base64转换成blob要经过很多步骤,但幸运的是这些步骤都是固定的,因…...

向日葵RCE复现(CNVD-2022-10270/CNVD-2022-03672)
一、环境 1.1 网上下载低版本的向日葵<2022 二、开始复现 2.1 在目标主机上打开旧版向日葵 2.2 首先打开nmap扫描向日葵主机端口 2.3 在浏览器中访问ip端口号cgi-bin/rpc?actionverify-haras (端口号:每一个都尝试,直到获取到session值…...
Postman中的负载均衡测试:确保API的高可用性
Postman中的负载均衡测试:确保API的高可用性 在微服务架构和分布式系统中,API的负载均衡是确保系统高可用性和可扩展性的关键技术之一。Postman作为一个多功能的API开发和测试平台,提供了多种工具来帮助测试人员模拟高负载情况下的API表现。…...

Cinnamon修改面板小工具图标
Cinnamon开始菜单-CSDN博客 设置模块都是做好的,比GNOME简单得多! 在 applet.js 里增加 const Settings imports.ui.settings;this.settings new Settings.AppletSettings(this, HTYMenusonichy, instance_id); this.settings.bind(menu-icon, menu…...

算法笔记2
1.字符串拼接最好用StringBuilder,不用String 2.创建List<>类型的数组并创建内存 List arr[] new ArrayList[26]; Arrays.setAll(arr, i -> new ArrayList<>()); 3.去掉首尾空格...
安卓基础(aar)
重新设置java21的环境,临时设置 $env:JAVA_HOME "D:\Android Studio\jbr" 查看当前环境变量 JAVA_HOME 的值 echo $env:JAVA_HOME 构建ARR文件 ./gradlew :private-lib:assembleRelease 目录是这样的: MyApp/ ├── app/ …...

华硕a豆14 Air香氛版,美学与科技的馨香融合
在快节奏的现代生活中,我们渴望一个能激发创想、愉悦感官的工作与生活伙伴,它不仅是冰冷的科技工具,更能触动我们内心深处的细腻情感。正是在这样的期许下,华硕a豆14 Air香氛版翩然而至,它以一种前所未有的方式&#x…...
Python 包管理器 uv 介绍
Python 包管理器 uv 全面介绍 uv 是由 Astral(热门工具 Ruff 的开发者)推出的下一代高性能 Python 包管理器和构建工具,用 Rust 编写。它旨在解决传统工具(如 pip、virtualenv、pip-tools)的性能瓶颈,同时…...

Netty从入门到进阶(二)
二、Netty入门 1. 概述 1.1 Netty是什么 Netty is an asynchronous event-driven network application framework for rapid development of maintainable high performance protocol servers & clients. Netty是一个异步的、基于事件驱动的网络应用框架,用于…...

AirSim/Cosys-AirSim 游戏开发(四)外部固定位置监控相机
这个博客介绍了如何通过 settings.json 文件添加一个无人机外的 固定位置监控相机,因为在使用过程中发现 Airsim 对外部监控相机的描述模糊,而 Cosys-Airsim 在官方文档中没有提供外部监控相机设置,最后在源码示例中找到了,所以感…...
机器学习的数学基础:线性模型
线性模型 线性模型的基本形式为: f ( x ) ω T x b f\left(\boldsymbol{x}\right)\boldsymbol{\omega}^\text{T}\boldsymbol{x}b f(x)ωTxb 回归问题 利用最小二乘法,得到 ω \boldsymbol{\omega} ω和 b b b的参数估计$ \boldsymbol{\hat{\omega}}…...
大数据治理的常见方式
大数据治理的常见方式 大数据治理是确保数据质量、安全性和可用性的系统性方法,以下是几种常见的治理方式: 1. 数据质量管理 核心方法: 数据校验:建立数据校验规则(格式、范围、一致性等)数据清洗&…...
JS红宝书笔记 - 3.3 变量
要定义变量,可以使用var操作符,后跟变量名 ES实现变量初始化,因此可以同时定义变量并设置它的值 使用var操作符定义的变量会成为包含它的函数的局部变量。 在函数内定义变量时省略var操作符,可以创建一个全局变量 如果需要定义…...