【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表现。…...

eNSP-Cloud(实现本地电脑与eNSP内设备之间通信)
说明: 想象一下,你正在用eNSP搭建一个虚拟的网络世界,里面有虚拟的路由器、交换机、电脑(PC)等等。这些设备都在你的电脑里面“运行”,它们之间可以互相通信,就像一个封闭的小王国。 但是&#…...
rknn优化教程(二)
文章目录 1. 前述2. 三方库的封装2.1 xrepo中的库2.2 xrepo之外的库2.2.1 opencv2.2.2 rknnrt2.2.3 spdlog 3. rknn_engine库 1. 前述 OK,开始写第二篇的内容了。这篇博客主要能写一下: 如何给一些三方库按照xmake方式进行封装,供调用如何按…...

练习(含atoi的模拟实现,自定义类型等练习)
一、结构体大小的计算及位段 (结构体大小计算及位段 详解请看:自定义类型:结构体进阶-CSDN博客) 1.在32位系统环境,编译选项为4字节对齐,那么sizeof(A)和sizeof(B)是多少? #pragma pack(4)st…...

聊聊 Pulsar:Producer 源码解析
一、前言 Apache Pulsar 是一个企业级的开源分布式消息传递平台,以其高性能、可扩展性和存储计算分离架构在消息队列和流处理领域独树一帜。在 Pulsar 的核心架构中,Producer(生产者) 是连接客户端应用与消息队列的第一步。生产者…...

【第二十一章 SDIO接口(SDIO)】
第二十一章 SDIO接口 目录 第二十一章 SDIO接口(SDIO) 1 SDIO 主要功能 2 SDIO 总线拓扑 3 SDIO 功能描述 3.1 SDIO 适配器 3.2 SDIOAHB 接口 4 卡功能描述 4.1 卡识别模式 4.2 卡复位 4.3 操作电压范围确认 4.4 卡识别过程 4.5 写数据块 4.6 读数据块 4.7 数据流…...

涂鸦T5AI手搓语音、emoji、otto机器人从入门到实战
“🤖手搓TuyaAI语音指令 😍秒变表情包大师,让萌系Otto机器人🔥玩出智能新花样!开整!” 🤖 Otto机器人 → 直接点明主体 手搓TuyaAI语音 → 强调 自主编程/自定义 语音控制(TuyaAI…...

【论文阅读28】-CNN-BiLSTM-Attention-(2024)
本文把滑坡位移序列拆开、筛优质因子,再用 CNN-BiLSTM-Attention 来动态预测每个子序列,最后重构出总位移,预测效果超越传统模型。 文章目录 1 引言2 方法2.1 位移时间序列加性模型2.2 变分模态分解 (VMD) 具体步骤2.3.1 样本熵(S…...
力扣-35.搜索插入位置
题目描述 给定一个排序数组和一个目标值,在数组中找到目标值,并返回其索引。如果目标值不存在于数组中,返回它将会被按顺序插入的位置。 请必须使用时间复杂度为 O(log n) 的算法。 class Solution {public int searchInsert(int[] nums, …...

永磁同步电机无速度算法--基于卡尔曼滤波器的滑模观测器
一、原理介绍 传统滑模观测器采用如下结构: 传统SMO中LPF会带来相位延迟和幅值衰减,并且需要额外的相位补偿。 采用扩展卡尔曼滤波器代替常用低通滤波器(LPF),可以去除高次谐波,并且不用相位补偿就可以获得一个误差较小的转子位…...

Linux中《基础IO》详细介绍
目录 理解"文件"狭义理解广义理解文件操作的归类认知系统角度文件类别 回顾C文件接口打开文件写文件读文件稍作修改,实现简单cat命令 输出信息到显示器,你有哪些方法stdin & stdout & stderr打开文件的方式 系统⽂件I/O⼀种传递标志位…...