时间戳

首页 > 解决方案 > 如何在 PostgreSQL 中使用 DISTINCT 加快查询速度？

sql - 如何在 PostgreSQL 中使用 DISTINCT 加快查询速度？

问题描述

如您所见，我有非常简单的 SQL 语句：

SELECT DISTINCT("CITY" || ' | '  || "AREA" || ' | ' || "REGION") AS LOCATION
FROM youtube

我在查询中使用的youtube表有大约 2500 万条记录。查询需要很长时间才能完成（约 25 秒）。我正在尝试加快请求速度。

我创建了一个如下所示的索引，但我的更高查询仍然需要相同的时间才能完成。我做错什么了？顺便说一句，在我的情况下使用“分区”更好吗？

CREATE INDEX location_index ON youtube ("CITY", "AREA", "REGION")

EXPLAIN返回：

Unique (cost=5984116.71..6111107.27 rows=96410 width=32)
-> Sort (cost=5984116.71..6047611.99 rows=25398112 width=32)
   Sort Key: ((((("CITY" || ' | '::text) || "AREA") || ' | '::text) || "REGION"))
   -> Seq Scan on youtube (cost=0.00..1037365.24 rows=25398112 width=32)

QUERY PLAN您的脚本的@george-joseph ：

在此处输入图像描述

标签： sqlpostgresqlindexingdistinct

解决方案

索引和分区都不能在这里为您提供帮助。

由于city和（可能）密切相关area，region结果行的数量将比 PostgreSQL 估计的要少得多，因为它假设列是相互独立的。

因此，您应该在这些列上创建扩展统计信息，这是 PostgreSQL v10 中引入的一项新功能：

CREATE STATISTICS youtube_stats (ndistinct)
   ON "CITY", "AREA", "REGION" FROM youtube;

ANALYZE youtube;

现在 PostgreSQL 对有多少不同的组有了更好的了解。

然后为查询提供大量内存，以便它可以将所有这些组的哈希值放入内存。然后它可以使用哈希聚合而不是对行进行排序：

SET work_mem = '1GB';

您可能不需要那么多内存；尝试找到更合理的限制。

然后尝试 George Joseph 的回答中的查询：

SELECT x."CITY" || ' | '  || x."AREA" || ' | ' || x."REGION" AS location
FROM (SELECT DISTINCT "CITY", "AREA", "REGION"
      FROM youtube) AS x;

推荐阅读