最快的 PostgreSQL Distinct 和 Format 方法

Question

我有一个包含350万行的表格acs_objects，我需要检索列creation_date并以仅年份格式进行去重。

我的第一次尝试： 180~200秒（获取了15行）

SELECT DISTINCT to_char(creation_date,'YYYY') FROM acs_objects

我的第二次尝试: 35~40秒（15行被获取）

SELECT DISTINCT to_char(creation_date,'YYYY')
FROM (SELECT DISTINCT creation_date FROM acs_objects) AS distinct_date

有没有办法让它更快？ -"我需要在ADP网站上使用它"

- Onel Sarmiento

---

你是否对该列进行了索引？ - Roger

@Rogier 我还不知道如何使用索引。 - Onel Sarmiento

@Leonel，你并不是“使用”它。但我可以想象如果该列被索引的话，排序会更快，这将提高性能。在我的MySQL表格（325,000行）中，一旦建立了索引，性能提升大约20％。 - Roger

对于这个特定的查询，一个基于 extract(year FROM creation_date) 的索引确实会非常好，并且使其运行速度飞快，但它可能会对表的其他用途产生负面影响。 - Patrick

目前被接受的答案比提出的替代方案昂贵1000倍。我认为你应该重新分配，不要在意标题的措辞。解决所提出的问题才是最重要的。 - Erwin Brandstetter

Answer 1

我认为你不应该从这个巨大的表中选择distinct。相反，尝试生成一个短的年份序列（例如从1900年到2100年），并仅从此序列中选择存在于acs_objects表中的年份。结果集将是相同的，但我认为速度会更快。 EXISTS子查询必须在索引字段creation_date上快速运行。

SELECT y 
FROM
(
   select generate_series(1900,2100) as y
) as t
WHERE EXISTS (SELECT 1 FROM acs_objects 
                    WHERE creation_date >= DATE (''||t.y||'-01-01')     
                           AND  creation_date < DATE (''||t.y + 1||'-01-01'))

SQLFiddle demo

- valex

---

哇，这太棒了，只有12~15毫秒。但问题是关于“去重和格式化”的，所以我不能接受这个作为答案，但我可以给您+1。谢谢！ - Onel Sarmiento

当你提出问题时，你应该乐于接受那些可以超越常规思维方式但仍然返回正确答案的回答（当然你可以格式化“y”）。在SQL中，通常有多种方法可以得到结果。 - dnoeth

好的回答，很好的展示了打破固有思维定势的例子！ - Frank Heikens

完全正确！！！在SQL中，你应该始终考虑想要知道什么——你想知道“这个表中有哪些年份”，并且你知道可能存在的年份范围。所以这是合乎逻辑的方式：检查每一年是否至少有一个匹配项。—— 我认为最好的方式是，在表中创建从MIN(year)到MAX(year)的动态范围，这样甚至可能更快——而且一些可怜的程序员不必在85年内更改它。 - Falco

在我不确定列是日期还是日期时间，或者如果我知道它是日期，但它永远不可能成为日期时间的情况下，WHERE creation_date >= DATE (''||t.y||'-01-01') AND creation_date < DATE (''||t.y + 1||'-01-01') 将是我的选择。 - Andriy M

@AndriyM 你说得对。我在答案中修改了这个条件。谢谢。 - valex

Answer 2

在您的第二次尝试中，您从子查询中获取到不同的日期，并将其全部转换为字符串表示形式，然后选择不同的日期。这种方法效率较低。最好先从子查询中提取creation_date中的不同年份，然后在主查询中将其简单地转换为文本：

SELECT year::text
FROM (
  SELECT DISTINCT extract(year FROM creation_date) AS year FROM acs_objects
) AS distinct_years;

如果在表格上创建一个INDEX，查询速度应该会更快：

CREATE INDEX really_fast ON acs_objects((extract(year FROM creation_date)));

然而，这可能会影响到你的表的其他用途，特别是如果你有许多修改语句（插入、更新、删除）。而且这只在 creation_date 具有 date 或 timestamp 数据类型时才有效（不能使用 timestamp with timezone）。

下面的选项看起来很有前途，因为它不使用子查询，但实际上它要慢得多（请参见下面的注释），可能是因为 DISTINCT 子句应用在字符串上。

SELECT DISTINCT extract(year FROM creation_date)::text
FROM acs_objects;

- Patrick

---

你必须使用派生表吗？SELECT DISTINCT extract(year FROM creation_date)::text AS year FROM acs_objects;会产生不同的计划吗？我问这个问题是因为我没有安装PostgreSQL，无法自行检查。 - Andriy M

我刚刚在编辑时写了那个。最好不要有子查询，对吧。但运行时间差异可能很小。 - Patrick

祝你进一步调整好它！我很想听听你对我的第二个选项的看法。 - Patrick

@Patrick 第二个需要80~90秒，第一个更好。 - Onel Sarmiento

这确实是一个很大的区别。已更新答案。 - Patrick

@Leonel，你最终创建了索引吗？如果是这样的话，更新后的运行时间是多少？我很想知道它与valex在年份上创建索引的答案相比如何。也许你可以将所有有前途的答案的运行时间与索引和没有索引的情况进行比较，并将其作为问题的更新，以使其成为未来问题解决者的真正好的问答。 - Patrick

Answer 3

我不确定你用它做什么。我建议使用一个物化视图。

现在，需要时可以刷新视图，并且有一种非常快速的检索年份列表的方法（因为数据基本上是静态存储的）。

在这里查看：

• http://www.postgresql.org/docs/current/static/sql-creatematerializedview.html
• http://www.postgresql.org/docs/current/static/sql-refreshmaterializedview.html

- Roger

Answer 4

有没有办法让它更快？

是的，要快得多。（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;

可能最快的方法：自顶向下，将每个时间戳截断到该年份的开始，然后找到更早行中最新的; 重复。

该技术的详细信息：

• 在PostgreSQL中，SELECT DISTINCT比预期的慢
• 优化GROUP BY查询以检索每个用户的最新行
• Postgres Wiki

基于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}

如果年份范围很小，这可能更快。但是无论表的大小如何，任何一种方法都只需要几毫秒甚至更短的时间。

相关:

• 在Postgres中从开始和结束日期的表中生成序列
• 在PostgreSQL中生成两个日期之间的时间序列

- Erwin Brandstetter

---

+1. 我认为使用@Falco对我的答案的评论，建议从表中使用MIN(year)和MAX(year)而不是1900年和2020年，再加上你的generate_series将会运行得更快。但我猜这已经取决于表中的年份分布了。 - valex

@valex：是的，从最小值到最大值的范围更加干净。我添加了一个链接。数值范围仍然可能存在重大差距，这将增加成本。我会使用rCTE，它适用于任何情况 - 比完美查找表稍慢，但比提供多个无关值的派生表更快。 - Erwin Brandstetter