PostgreSQL生成了一系列奇怪的行为

Question

以下两个查询产生完全相同的输出：

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_steries的预构造表(而不是直接在查询中使用generate-steries(会产生与直接使用函数相同的结果，即使该表上有索引
• 两个版本(now((和generate_series(((的EXPLAIN ANALYZE输出可以在这里找到：https://gist.github.com/JivanRoquet/a4f1c82ecf54b420844e652584317c76

Answer 1

相关的子查询可以满足您的要求。

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关键字强制数据库为左手边的每一行重新计算右手边。