postgresql - PostgreSQL generate_series 奇怪的行为
问题描述
以下两个查询产生完全相同的输出:
select
ref_date::date
from generate_series('2020-10-01', '2020-10-01'::date, interval '1 day') ref_date
-- ref_date
-- 2020-10-01
select now()::date ref_date
-- ref_date
-- 2020-10-01
但是,当explain
在它们中的每一个上运行时,我们会得到不同的东西:
# query 1
Function Scan on generate_series ref_date (cost=0.01..12.51 rows=1000 width=4)
# query 2
Result (cost=0.00..0.01 rows=1 width=4)
当在一系列连接中包含一个或另一个时,情况会变得更糟,连接条件基于ref_date
:
select
stuff
from (select ref_date::date from generate_series('2020-10-01', '2020-10-01'::date, interval '1 day') ref_date) ref_date
left join (other_stuff) x on true
left join (more_stuff) y on y.id = x.id and y.timestamp < ref_date
-- executes in 10 minutes
-- EXPLAIN is long and complex
-- query uses index on more_stuff.(id) only
despite an index on (id, timestamp) being available
select
stuff
from (select now()::date ref_date) ref_date
left join (other_stuff) x on true
left join (more_stuff) y on y.id = x.id and y.timestamp < ref_date
-- executes in ten milliseconds
-- EXPLAIN is short and simple
-- query adequately uses index on more_stuff.(id, timestamp)
我不能now()::date
在现实中使用的原因是我需要generate_series()
生成多个日期(比如,跨越 5 年)。
问题:
有没有办法使用使用日期序列的替代方法,并且now()::date
与上述示例中使用时一样有效?
笔记:
- 该
generate_series()
方法的性能比now()::date
仅生成一个日期时差得多 - 使用带有 generate_series 输出的预构建表(而不是在查询中直接使用 generate_series)产生与直接使用函数相同的结果,即使在此表上有索引
- 可以在此处找到两个版本(now() 和 generate_series())的 EXPLAIN ANALYZE 输出:https ://gist.github.com/JivanRoquet/a4f1c82ecf54b420844e652584317c76
解决方案
相关子查询可以完成您的要求。
select stuff
FROM generate_series('2020-09-01'::date, '2020-10-01'::date, interval '1 day') as ref_date
LEFT JOIN LATERAL
(select (other_stuff)) AS x on true
left join (more_stuff) y on y.timestamp < ref_date
这应该生成一个嵌套循环连接,内部部分的计划与您的快速查询相匹配。LATERAL 关键字强制数据库重新评估左侧每一行的右侧。
推荐阅读
- ruby - 使用 Ruby 服务器作为 API 中间人
- python-3.x - 熊猫向前向后填充列级别内的列
- ios - iOS - 在后台推送通知上刷新数据(发出 Api 请求)
- powerquery - Power Query 将列表中的记录转换为列
- javascript - d3js 版本 3 到 4 用于切换条形图
- asynchronous - UWP:抛出/引发不会导致应用程序崩溃,异常被忽略
- javascript - 警告:函数作为反应子问题无效......是因为我的容器吗?
- vim - 当我退出插入模式时,如何防止 Vim 复制我的文本?
- excel - 如何将多行合并为一行
- javascript - 请帮助我使用 javascript 构建 Hard Drop 功能(俄罗斯方块)