sql - 为什么以及如何 SQL 条件不在 psql 解释计划中?
问题描述
我尝试分析加入查询基准的计划https://github.com/gregrahn/join-order-benchmark
例如,我执行以下命令:
EXPLAIN SELECT *
FROM aka_name AS an,
cast_info AS ci,
company_name AS cn,
keyword AS k,
movie_companies AS mc,
movie_keyword AS mk,
name AS n,
title AS t
WHERE an.person_id = n.id
AND n.id = ci.person_id
AND ci.movie_id = t.id
AND t.id = mk.movie_id
AND mk.keyword_id = k.id
AND t.id = mc.movie_id
AND mc.company_id = cn.id
AND an.person_id = ci.person_id
AND ci.movie_id = mc.movie_id
AND ci.movie_id = mk.movie_id
AND mc.movie_id = mk.movie_id;
结果,我得到了以下查询计划
QUERY PLAN [0/1803]
------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1973375.70..22192463.47 rows=22337517790 width=449)
Hash Cond: (ci.movie_id = t.id)
-> Merge Join (cost=102.03..2617413.84 rows=88800840 width=203)
Merge Cond: (n.id = an.person_id)
-> Merge Join (cost=0.87..2341713.60 rows=36244344 width=130)
Merge Cond: (ci.person_id = n.id)
-> Index Scan using person_id_cast_info on cast_info ci (cost=0.44..1714393.60 rows=36244344 width=56)
-> Index Scan using name_pkey on name n (cost=0.43..163847.25 rows=4167379 width=74)
-> Materialize (cost=0.42..69770.80 rows=901343 width=73)
-> Index Scan using person_id_aka_name on aka_name an (cost=0.42..67517.44 rows=901343 width=73)
-> Hash (cost=834975.33..834975.33 rows=24906348 width=246)
-> Hash Join (cost=486218.85..834975.33 rows=24906348 width=246)
Hash Cond: (mk.movie_id = t.id)
-> Hash Join (cost=4885.82..131552.82 rows=4523930 width=37)
Hash Cond: (mk.keyword_id = k.id)
-> Seq Scan on movie_keyword mk (cost=0.00..69693.30 rows=4523930 width=12)
-> Hash (cost=2290.70..2290.70 rows=134170 width=25)
-> Seq Scan on keyword k (cost=0.00..2290.70 rows=134170 width=25)
-> Hash (cost=372278.91..372278.91 rows=2609129 width=209)
-> Hash Join (cost=141184.56..372278.91 rows=2609129 width=209)
Hash Cond: (mc.movie_id = t.id)
-> Hash Join (cost=11266.43..106748.81 rows=2609129 width=115)
Hash Cond: (mc.company_id = cn.id)
-> Seq Scan on movie_companies mc (cost=0.00..44881.29 rows=2609129 width=40)
-> Hash (cost=5344.97..5344.97 rows=234997 width=75)
-> Seq Scan on company_name cn (cost=0.00..5344.97 rows=234997 width=75)
-> Hash (cost=61280.28..61280.28 rows=2528228 width=94)
-> Seq Scan on title t (cost=0.00..61280.28 rows=2528228 width=94)
JIT:
如您所见,mc.movie_id = mk.movie_id
此计划中不存在条件。如何以及为什么可能?
解决方案
查看最后三个条件:
AND ci.movie_id = mc.movie_id
AND ci.movie_id = mk.movie_id
AND mc.movie_id = mk.movie_id;
使用movie_id
,您将 tableci
与匹配mc
,然后ci
与mk
which 意味着mc
匹配mk
,因此最后一个条件是多余的,计划者理所当然地忽略它。
推荐阅读
- three.js - Three.js BufferGeometry:.setAttribute 已重命名为 .setAttribute
- javascript - 嵌套数组并访问javascript中的所有其他索引
- javascript - 如何在 HQ 音频旁边录制最低比特率的屏幕共享视频?
- javascript - 音频 webaudio 类不接受带有“C:/”类型 url 的源
- android - 如何将框架添加到 Android Studio 项目?
- python - 我可以有一个无效范围的列表吗?欧拉项目#7
- google-bigquery - 如何使用通配符表语法(如 _TABLE_SUFFIX)加入 INFORMATION_SCHEMA 元数据,以便在 Google Big Query 中通过 table_name 获取结果
- kubernetes - 集群内无法访问 Kubernetes POD
- c# - 如何在将照片添加到数据库之前显示照片的预览?
- javascript - 如何在 JavaScript 中向 HH:MM:SS 添加分钟数?