当前位置: 首页 > 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表现。…...

Cinnamon修改面板小工具图标

Cinnamon开始菜单-CSDN博客 设置模块都是做好的&#xff0c;比GNOME简单得多&#xff01; 在 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&#xff0c;不用String 2.创建List<>类型的数组并创建内存 List arr[] new ArrayList[26]; Arrays.setAll(arr, i -> new ArrayList<>()); 3.去掉首尾空格...

安卓基础(aar)

重新设置java21的环境&#xff0c;临时设置 $env:JAVA_HOME "D:\Android Studio\jbr" 查看当前环境变量 JAVA_HOME 的值 echo $env:JAVA_HOME 构建ARR文件 ./gradlew :private-lib:assembleRelease 目录是这样的&#xff1a; MyApp/ ├── app/ …...

华硕a豆14 Air香氛版,美学与科技的馨香融合

在快节奏的现代生活中&#xff0c;我们渴望一个能激发创想、愉悦感官的工作与生活伙伴&#xff0c;它不仅是冰冷的科技工具&#xff0c;更能触动我们内心深处的细腻情感。正是在这样的期许下&#xff0c;华硕a豆14 Air香氛版翩然而至&#xff0c;它以一种前所未有的方式&#x…...

Python 包管理器 uv 介绍

Python 包管理器 uv 全面介绍 uv 是由 Astral&#xff08;热门工具 Ruff 的开发者&#xff09;推出的下一代高性能 Python 包管理器和构建工具&#xff0c;用 Rust 编写。它旨在解决传统工具&#xff08;如 pip、virtualenv、pip-tools&#xff09;的性能瓶颈&#xff0c;同时…...

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是一个异步的、基于事件驱动的网络应用框架&#xff0c;用于…...

AirSim/Cosys-AirSim 游戏开发(四)外部固定位置监控相机

这个博客介绍了如何通过 settings.json 文件添加一个无人机外的 固定位置监控相机&#xff0c;因为在使用过程中发现 Airsim 对外部监控相机的描述模糊&#xff0c;而 Cosys-Airsim 在官方文档中没有提供外部监控相机设置&#xff0c;最后在源码示例中找到了&#xff0c;所以感…...

机器学习的数学基础:线性模型

线性模型 线性模型的基本形式为&#xff1a; f ( x ) ω T x b f\left(\boldsymbol{x}\right)\boldsymbol{\omega}^\text{T}\boldsymbol{x}b f(x)ωTxb 回归问题 利用最小二乘法&#xff0c;得到 ω \boldsymbol{\omega} ω和 b b b的参数估计$ \boldsymbol{\hat{\omega}}…...

大数据治理的常见方式

大数据治理的常见方式 大数据治理是确保数据质量、安全性和可用性的系统性方法&#xff0c;以下是几种常见的治理方式&#xff1a; 1. 数据质量管理 核心方法&#xff1a; 数据校验&#xff1a;建立数据校验规则&#xff08;格式、范围、一致性等&#xff09;数据清洗&…...

JS红宝书笔记 - 3.3 变量

要定义变量&#xff0c;可以使用var操作符&#xff0c;后跟变量名 ES实现变量初始化&#xff0c;因此可以同时定义变量并设置它的值 使用var操作符定义的变量会成为包含它的函数的局部变量。 在函数内定义变量时省略var操作符&#xff0c;可以创建一个全局变量 如果需要定义…...