当前位置: 首页 > news >正文

【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案例】我要查的表没有在执行计划中

问题&#xff1a;查的表没有在执行计划中 sql&#xff1a; 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 页面细节规范 》

&#x1f4e2; 大家好&#xff0c;我是 【战神刘玉栋】&#xff0c;有10多年的研发经验&#xff0c;致力于前后端技术栈的知识沉淀和传播。 &#x1f497; &#x1f33b; CSDN入驻不久&#xff0c;希望大家多多支持&#xff0c;后续会继续提升文章质量&#xff0c;绝不滥竽充数…...

操作系统面试知识点总结5

#来自ウルトラマンメビウス&#xff08;梦比优斯&#xff09; 1 IO管理概述 1.1 I/O 设备 I/O 设备的类型分类。 1.1.1 按使用特性 人机交互类外部设备&#xff0c;例如打印机、显示器等。存储设备&#xff0c;例如磁盘、光盘等。网络通信设备&#xff0c;例如网络接口等。 1…...

BigInteger和BigDecimal类

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

2024最新Uniapp的H5网页版添加谷歌授权验证

现在教程不少&#xff0c;但是自从谷歌升级验证之后&#xff0c;以前的老教程就失效了&#xff0c;现在写一个新教程以备不时之需。 由于众所周知的特殊原因&#xff0c;开发的时候一定注意网络环境&#xff0c;如果没有梯子是无法进行开发的哦~ clientID的申请方式我就不再进…...

学习java第一百四十四天

Spring通知有哪些类型&#xff1f; 在AOP术语中&#xff0c;切面的工作被称为通知。通知实际上是程序运行时要通过Spring AOP框架来触发的代码段。 Spring切面可以应用5种类型的通知&#xff1a; 前置通知&#xff08;Before&#xff09;&#xff1a;在目标方法被调用之前调用通…...

Meta 发布 Llama3.1,一站教你如何推理、微调、部署大模型

最近这一两周看到不少互联网公司都已经开始秋招提前批了。不同以往的是&#xff0c;当前职场环境已不再是那个双向奔赴时代了。求职者在变多&#xff0c;HC 在变少&#xff0c;岗位要求还更高了。 最近&#xff0c;我们又陆续整理了很多大厂的面试题&#xff0c;帮助一些球友解…...

XSSFWorkbook 和 SXSSFWorkbook 的区别

在现代办公环境中&#xff0c;处理 Excel 文件是一个常见的任务。Apache POI 是一个流行的 Java 库&#xff0c;能够读写 Microsoft Office 文档。对于处理 Excel 文件&#xff0c;Apache POI 提供了 XSSFWorkbook 和 SXSSFWorkbook 两个类。本文将详细介绍这两个类的特点和适用…...

会议主题:NICE Seminar|神经组合优化方法的大规模泛化研究(南方科技大学王振坤副研究员)

数据增强 获得更多解 TSP问题 最优解与序列无关&#xff0c;数据增强 ICML 2024 Position Rethinking Post-Hoc Search-Based Neural Approaches for Solving Large-Scale Traveling Salesman Problems...

昇思25天学习打卡营第22天|CycleGAN图像风格迁移互换

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

《Java初阶数据结构》----6.<优先级队列之PriorityQueue底层:堆>

前言 大家好&#xff0c;我目前在学习java。之前也学了一段时间&#xff0c;但是没有发布博客。时间过的真的很快。我会利用好这个暑假&#xff0c;来复习之前学过的内容&#xff0c;并整理好之前写过的博客进行发布。如果博客中有错误或者没有读懂的地方。热烈欢迎大家在评论区…...

Matrix Equation(高斯线性异或消元+bitset优化)

题目&#xff1a; 登录—专业IT笔试面试备考平台_牛客网 思路&#xff1a; 我们发现对于矩阵C可以一列一列求。 mod2&#xff0c;当这一行相乘1的个数为奇数时&#xff0c;z(i,j)为1&#xff0c;偶数为0&#xff0c;是异或消元。 对于b[i&#xff0c;j]*c[i,j],b[i,j]可以…...

【一图学技术】2.API测试9种方法图解

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

力扣刷题----42. 接雨水

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

【论文精读】 | 基于图表示的视频抑郁症识别的两阶段时间建模框架

文章目录 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音频文件

需求 开始录音——监听录音数据——结束录音 在监听录音数据过程中&#xff1a;客户端每100ms给前端传输一次数据&#xff08;pcm数据转成base64&#xff09;&#xff0c;前端需要将base64片段解码、合并、添加WAV头、转成File、上传到 OSS之后将 url 给到服务端处理。 {num…...

eclipse ui bug

eclipse ui bug界面缺陷&#xff0c;可能项目过多&#xff0c;特别maven项目过多&#xff0c;下载&#xff0c;自动编译&#xff0c;加载更新界面异常 所有窗口死活Restore不回去了 1&#xff09;尝试创建项目&#xff0c;还原界面&#xff0c;失败 2&#xff09;关闭所有窗口&…...

前端获取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 &#xff08;端口号&#xff1a;每一个都尝试&#xff0c;直到获取到session值…...

Postman中的负载均衡测试:确保API的高可用性

Postman中的负载均衡测试&#xff1a;确保API的高可用性 在微服务架构和分布式系统中&#xff0c;API的负载均衡是确保系统高可用性和可扩展性的关键技术之一。Postman作为一个多功能的API开发和测试平台&#xff0c;提供了多种工具来帮助测试人员模拟高负载情况下的API表现。…...

eNSP-Cloud(实现本地电脑与eNSP内设备之间通信)

说明&#xff1a; 想象一下&#xff0c;你正在用eNSP搭建一个虚拟的网络世界&#xff0c;里面有虚拟的路由器、交换机、电脑&#xff08;PC&#xff09;等等。这些设备都在你的电脑里面“运行”&#xff0c;它们之间可以互相通信&#xff0c;就像一个封闭的小王国。 但是&#…...

rknn优化教程(二)

文章目录 1. 前述2. 三方库的封装2.1 xrepo中的库2.2 xrepo之外的库2.2.1 opencv2.2.2 rknnrt2.2.3 spdlog 3. rknn_engine库 1. 前述 OK&#xff0c;开始写第二篇的内容了。这篇博客主要能写一下&#xff1a; 如何给一些三方库按照xmake方式进行封装&#xff0c;供调用如何按…...

练习(含atoi的模拟实现,自定义类型等练习)

一、结构体大小的计算及位段 &#xff08;结构体大小计算及位段 详解请看&#xff1a;自定义类型&#xff1a;结构体进阶-CSDN博客&#xff09; 1.在32位系统环境&#xff0c;编译选项为4字节对齐&#xff0c;那么sizeof(A)和sizeof(B)是多少&#xff1f; #pragma pack(4)st…...

聊聊 Pulsar:Producer 源码解析

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

【第二十一章 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机器人从入门到实战

“&#x1f916;手搓TuyaAI语音指令 &#x1f60d;秒变表情包大师&#xff0c;让萌系Otto机器人&#x1f525;玩出智能新花样&#xff01;开整&#xff01;” &#x1f916; Otto机器人 → 直接点明主体 手搓TuyaAI语音 → 强调 自主编程/自定义 语音控制&#xff08;TuyaAI…...

【论文阅读28】-CNN-BiLSTM-Attention-(2024)

本文把滑坡位移序列拆开、筛优质因子&#xff0c;再用 CNN-BiLSTM-Attention 来动态预测每个子序列&#xff0c;最后重构出总位移&#xff0c;预测效果超越传统模型。 文章目录 1 引言2 方法2.1 位移时间序列加性模型2.2 变分模态分解 (VMD) 具体步骤2.3.1 样本熵&#xff08;S…...

力扣-35.搜索插入位置

题目描述 给定一个排序数组和一个目标值&#xff0c;在数组中找到目标值&#xff0c;并返回其索引。如果目标值不存在于数组中&#xff0c;返回它将会被按顺序插入的位置。 请必须使用时间复杂度为 O(log n) 的算法。 class Solution {public int searchInsert(int[] nums, …...

永磁同步电机无速度算法--基于卡尔曼滤波器的滑模观测器

一、原理介绍 传统滑模观测器采用如下结构&#xff1a; 传统SMO中LPF会带来相位延迟和幅值衰减&#xff0c;并且需要额外的相位补偿。 采用扩展卡尔曼滤波器代替常用低通滤波器(LPF)&#xff0c;可以去除高次谐波&#xff0c;并且不用相位补偿就可以获得一个误差较小的转子位…...

Linux中《基础IO》详细介绍

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