有没有办法让它更快?
是的,要快得多。(2021年更新。)
基本评估
如果您需要经常使用并且速度很快,并且对表格的写入要么很少,要么可预测(例如:新行始终具有当前时间),则 物化视图 将是最快的选择,就像 @Roger 建议的那样。但是,您仍然需要一个查询来实现它。我将建议的查询如此之快,以至于您可能会跳过 MV...
在相关情况下,通常有一个查找表,其中包含候选值,可以实现 更快 的查询:
这种情况下的假设:
- 需要使用Postgres 9.4或更高版本。
creation_date
是数据类型 timestamp
(也适用于 date
或 timestamptz
)。
- 时间戳的实际范围未知。
acs_objects(creation_date)
上有一个B树索引。
使用rCTE模拟松散索引扫描
如果您既没有查找表,也没有包含候选值的派生表,仍然有一种非常快速的替代方法。基本上,您需要模拟“索引跳过扫描”,即“松散索引扫描”。此查询在任何情况下都有效:
WITH RECURSIVE cte AS (
SELECT date_trunc('year', max(creation_date)) AS y
FROM acs_objects
UNION ALL
SELECT (SELECT date_trunc('year', max(creation_date))
FROM acs_objects
WHERE creation_date < cte.y)
FROM cte
WHERE cte.y IS NOT NULL
)
SELECT to_char(y, 'YYYY') AS year
FROM cte
WHERE cte.y IS NOT NULL;
可能最快的方法:自顶向下,将每个时间戳截断到该年份的开始,然后找到更早行中最新的; 重复。
该技术的详细信息:
基于generate_series()
valex的想法可以更高效地实现,使用实际存在年份范围为基础的generate_series()
产生timestamp
值:
SELECT to_char(y, 'YYYY') AS year
FROM (
SELECT generate_series(date_trunc('year', min(creation_date))
, max(creation_date)
, interval '1 year')
FROM acs_objects
) t(y)
WHERE EXISTS (
SELECT FROM acs_objects
WHERE creation_date >= y
AND creation_date < y + interval '1 year'
);
db<>fiddle 在这里演示了两种方法。
旧版slfiddle
如果年份范围很小,这可能更快。但是无论表的大小如何,任何一种方法都只需要几毫秒甚至更短的时间。
相关:
extract(year FROM creation_date)
的索引确实会非常好,并且使其运行速度飞快,但它可能会对表的其他用途产生负面影响。 - Patrick