sql - 删除索引是否会重置性能增益？

Question

我尝试在包含数千万行的表中的外键列上添加索引，我运行的半复杂查询的执行时间从 4 秒缩短到 10 毫秒。好的!

然后我尝试再次删除它以进行更多测试，但是当我执行相同的查询时它仍然花费了 10 毫秒(与添加索引之前花费的 4 秒相比)。

删除索引是否会重置性能增益？如果没有，如何彻底删除索引？

查询如下所示(此查询每分钟运行一次并将结果存储在另一个表中):

SELECT COUNT(*) AS count
FROM (
   SELECT MAX(ze.timestamp) AS time, r.device_id
   FROM loc_zone_events ze
   INNER JOIN loc_zones z ON ze.zone_id = z.id
   INNER JOIN raw_events r ON ze.raw_event_id = r.id
   WHERE z.app_id = 1
   AND ROUND(EXTRACT('epoch' FROM NOW() - ze.timestamp) / 60) BETWEEN 0 AND 10
   GROUP BY r.device_id
   ORDER BY time DESC
   ) AS t

Answer 1

DROP INDEX 完全删除索引。

事务必须在对新查询生效之前提交，但这通常不是问题。您可能会看到其他测试工件，例如:

• 在统计数据略有变化后，Postgres 会转向不同的查询计划。那将表明您的 cost settings might be inappropriate or some other poor configuration.
• 查询的重复执行已经填充了缓存(这对于大表可能产生很大的差异)。对于中途可比较的结果，将所有候选人运行几次。
• 您的查询基于“最近十分钟”。可能有 1000 行，10 分钟后，可能只有 1 行。可能会有很大的不同。

查询

对于初学者，删除完全不需要的部分:

SELECT COUNT(*) AS count
FROM (
   SELECT 1
   FROM   loc_zones       z
   JOIN   loc_zone_events ze ON ze.zone_id = z.id
   JOIN   raw_events      r  ON r.id = ze.raw_event_id
   WHERE  z.app_id = 1
   AND    round(EXTRACT('epoch' FROM NOW() - ze.timestamp) / 60) BETWEEN 0 AND 10
   GROUP  BY r.device_id
   ) AS t;

或者:

SELECT COUNT(DISTINCT r.device_id) AS count
FROM   loc_zones       z
JOIN   loc_zone_events ze ON ze.zone_id = z.id
JOIN   raw_events      r  ON r.id = ze.raw_event_id
WHERE  z.app_id = 1
AND    round(EXTRACT('epoch' FROM NOW() - ze.timestamp) / 60) BETWEEN 0 AND 10

(不一定更快，count(DISTINCT col) 不是性能英雄。)

但还有更多:

您的WHERE 条件round(...) 不是sargable .要检索“最后 10 分钟”的事件，请改用:

...
AND    ze.timestamp >= now() - interval '10 min'
AND    ze.timestamp <  now();  -- only if there can be timestamps in the future 

这是可搜索的，可以在 ze.timestamp 上使用索引。

注意 您的表达式使用的是 round() 而不是 trunc()，这实际上涵盖范围 (-0.5, 10.5)，即 11 分钟(不是 10 分钟)，如果没有 future 时间戳，则为 10.5 分钟。以一种或另一种方式处理这种差异......

索引

由于似乎只有最后 10 分钟相关，您可以使用部分索引进一步改进它。这里的特殊困难是移动时间范围。这个相关的答案有一个完整的解决方案:

• Indexed ORDER BY with LIMIT 1

在此基础上，您有一个部分索引，如:

CREATE INDEX ze_timestamp_recent_idx ON tbl (timestamp DESC);
WHERE  created_at > f_min_ts();

并像这样调整查询:

WHERE  ...
AND    ze.timestamp > f_min_ts()   -- to match partial index
AND    ze.timestamp >= now() - interval '10 min'
AND    ze.timestamp <  now();

另外:不要使用基本类型名称 timestamp 作为列名称。