主页 / dba / 问题 / 93158
Accepted
Sam
Asked: 2015-02-18 14:31:52 +0800 CST

如何加快选择不同的？

  • 772

我对一些时间序列数据有一个简单的选择：

SELECT DISTINCT user_id
FROM events
WHERE project_id = 6
AND time > '2015-01-11 8:00:00'
AND time < '2015-02-10 8:00:00';

它需要112秒。这是查询计划：

http://explain.depesz.com/s/NTyA

我的应用程序必须执行许多不同的操作并像这样计数。有没有更快的方法来获取这种数据？

postgresql performance

3 个回答

  1. Best Answer

    你可能不想听到这个，但加快速度的最佳选择SELECT DISTINCT是避免 DISTINCT一开始。在许多情况下（不是全部！）可以通过更好的数据库设计或更好的查询来避免。

    有时，GROUP BY更快，因为它采用不同的代码路径。

    在您的特定情况下，您似乎无法摆脱DISTINCT（好吧，见下文）。但是，如果您有许多此类查询，则可以使用特殊索引来支持查询：

    CREATE INDEX foo ON events (project_id, "time", user_id);

    在 Postgres 11 或更高版本中，您可以使用实际的“覆盖”索引，例如：

    CREATE INDEX foo ON events (project_id, "time") INCLUDE (user_id);

    user_id仅当您从中获得仅索引扫描时，添加才有用。看：

    将从您的查询计划中删除昂贵的位图堆扫描，这会消耗 90% 的查询时间。

    您EXPLAIN显示了 50 万行中的 2,491 个不同的用户。无论您做什么，这都不会变得超快，但它可以更快。每个用户大约有 200 行，在上面的索引上模拟索引跳过扫描可能会有所回报。范围条件time很复杂，每个用户 200 行仍然是一个适中的数字。所以不确定。看：

    无论哪种方式，如果您的查询中的时间间隔始终相同，那么每次MATERIALIZED VIEW折叠都会有很长的路要走。但是，在不同的时间间隔内没有机会。也许您至少可以每小时或其他一些最小时间单位折叠用户，这将购买足够的性能来保证相当大的开销。可以与任一查询样式结合使用。user_id(project_id, <fixed time interval>)

    Nitpick：
    很可能，谓词"time"真的应该是：

    AND "time" >= '2015-01-11 8:00:00'
AND "time" <  '2015-02-10 8:00:00';

    旁白：
    不要time用作标识符。它是标准 SQL 中的保留字，也是 Postgres 中的基本类型。

    • 35

  2. 这是我对 Sam 的案例和Erwin 的回答的测试

    drop table t1
create table t1 (id int, user_id int, project_id int, date_time timestamp without time zone) ;

insert into t1 -- 10 million row - size="498 MB"
select row_number() over(), round(row_number() over()/1000), round(row_number() over()/100000) , date
from generate_series('2015-01-01'::date, '2016-12-01'::date,'6 seconds'::interval
) date 
limit 10000000

-- before indexing - 10000000 row - output=100 row - time=2900ms
SELECT DISTINCT user_id
FROM t1
WHERE project_id = 1
AND date_time > '2015-01-01 8:00:00'
AND date_time < '2016-12-01 8:00:00' ;

CREATE INDEX foo ON t1 (project_id, date_time, user_id); -- time process=51.2 secs -- size="387 MB"         

-- after indexing - 10000000 row - output=100 row - time= 75ms (reduce ~ 38 times)
SELECT DISTINCT user_id
FROM t1
WHERE project_id = 1
AND date_time > '2015-01-01 00:00:00'
AND date_time < '2016-12-01 00:00:00' ;

    Erwin 说：“你可能不想听到这个，但加快 SELECT DISTINCT 的最佳选择是从一开始就避免 DISTINCT。在许多情况下（不是全部！）可以通过更好的数据库设计或更好的查询来避免” 。我认为他是对的，我们应该避免使用“distinct, group by, order by”（如果有的话）。

    我遇到了 Sam 的情况，我认为 Sam 可以按月在事件表上使用分区。它会在您查询时减少您的数据大小，但您需要一个函数（pl/pgsql）来执行而不是上面的查询。该函数将找到合适的分区（取决于条件）来执行查询。

    • 4

  3. 您可以尝试在所有列上创建空间索引，例如“rtree”索引(time, project_id, user_id)。我认为这在理论上可以加快查询速度，但我不确定。

    对于其他寻求加速的人SELECT DISTINCT：WHERE一些数据库引擎实现了一种特殊的算法（“索引跳过扫描”、“松散索引扫描”、“跳转扫描”），只是为了从 b 树索引的前导列中选择不同的值。PostgreSQL 还没有它，但在 2020 年的路线图上有它。请参阅Postgres Wiki 上的 Loose indexscan。在这种特殊情况下它没有帮助，因为您在另一列上有一个范围过滤器，它也必须使用 b 树索引的前导列。你只能选择一个。

    • 1

相关问题