sql - 优化和/或索引此查询的正确方法是什么？

Question

我有一张 pings 表，其中包含大约 1500 万行。我在 postgres 9.2.4 上。它具有的相关列是外键 monitor_id、created_at 时间戳和 response_time(代表毫秒的整数)。这是确切的结构:

     Column      |            Type             |                     Modifiers                      
-----------------+-----------------------------+----------------------------------------------------
 id              | integer                     | not null default nextval('pings_id_seq'::regclass)
 url             | character varying(255)      | 
 monitor_id      | integer                     | 
 response_status | integer                     | 
 response_time   | integer                     | 
 created_at      | timestamp without time zone | 
 updated_at      | timestamp without time zone | 
 response_body   | text                        | 
Indexes:
    "pings_pkey" PRIMARY KEY, btree (id)
    "index_pings_on_created_at_and_monitor_id" btree (created_at DESC, monitor_id)
    "index_pings_on_monitor_id" btree (monitor_id)

我想查询所有非NULL的响应时间(90%不会是NULL，大约10%会是NULL)，它们具有特定的 monitor_id，并且是在上个月创建的。我正在使用 ActiveRecord 进行查询，但最终结果如下所示:

SELECT "pings"."response_time"
FROM "pings"
WHERE "pings"."monitor_id" = 3
AND (created_at > '2014-03-03 20:23:07.254281'
AND response_time IS NOT NULL)

这是一个非常基本的查询，但运行大约需要 2000 毫秒，这看起来相当慢。我假设索引会使它更快，但我尝试过的所有索引都不起作用，我假设这意味着我没有正确编制索引。

当我运行 EXPLAIN ANALYZE 时，这是我得到的:

Bitmap Heap Scan on pings  (cost=6643.25..183652.31 rows=83343 width=4) (actual time=58.997..1736.179 rows=42063 loops=1)
  Recheck Cond: (monitor_id = 3)
  Rows Removed by Index Recheck: 11643313
  Filter: ((response_time IS NOT NULL) AND (created_at > '2014-03-03 20:23:07.254281'::timestamp without time zone))
  Rows Removed by Filter: 324834
  ->  Bitmap Index Scan on index_pings_on_monitor_id  (cost=0.00..6622.41 rows=358471 width=0) (actual time=57.935..57.935 rows=366897 loops=1)
        Index Cond: (monitor_id = 3)

所以在 monitor_id 上有一个索引被使用到最后，但没有别的。我使用 monitor_id、created_at 和 response_time 尝试了复合索引的各种排列和顺序。我试过按 created_at 降序排列索引。我已经尝试使用 response_time IS NOT NULL 的部分索引。

我尝试过的任何方法都无法使查询更快。您将如何对其进行优化和/或编制索引？

Answer 1

列的顺序

创建一个 partial multicolumn index具有正确的列顺序。你有一个:

"index_pings_on_created_at_and_monitor_id" btree (created_at DESC, monitor_id)

但是列的顺序并不适合您。反转它:

CREATE INDEX idx_pings_monitor_created ON pings (<b>monitor_id, created_at DESC</b>)
WHERE response_time IS NOT NULL;

这里的经验法则是:首先是平等，然后是范围。更多相关信息:
Multicolumn index and performance

正如所讨论的，条件 WHERE response_time IS NOT NULL 对您来说意义不大。如果您有其他查询可以利用此索引，包括 response_time 中的 NULL 值，请删除它。否则，保留它。

您也可以删除其他两个现有索引。更多关于 btree 索引中列的顺序:
Working of indexes in PostgreSQL

覆盖索引

如果您只需要从表中获取response_time，这可能会快得多 - 如果您没有对表的行进行大量写操作。在索引的最后位置包含该列以允许 index-only scans (使其成为“覆盖索引”):

CREATE INDEX idx_pings_monitor_created
ON     pings (monitor_id, created_at DESC, <b>response_time</b>)
WHERE  response_time IS NOT NULL;  -- maybe

或者，你甚至尝试这个..

更激进的部分索引

创建一个小辅助函数。实际上是您数据库中的“全局常量”:

CREATE OR REPLACE FUNCTION f_ping_event_horizon()
  RETURNS timestamp LANGUAGE sql IMMUTABLE COST 1 AS
$$SELECT '2014-03-03 0:0'::timestamp$$;  -- One month in the past

将其用作索引中的条件:

CREATE INDEX idx_pings_monitor_created_response_time
ON     pings (monitor_id, created_at DESC, response_time)
WHERE  response_time IS NOT NULL  -- maybe
<b>AND   created_at > f_ping_event_horizon()</b>;

您的查询现在看起来像这样:

SELECT response_time
FROM   pings
WHERE  monitor_id = 3
AND    response_time IS NOT NULL
AND    created_at > '2014-03-03 20:23:07.254281'
AND    created_at > f_ping_event_horizon();

旁白:我削减了一些噪音。

最后一个条件在逻辑上似乎是多余的。只包含它，如果 Postgres 不理解它可以在没有它的情况下使用索引。可能是必要的。条件中的实际时间戳必须大于函数中的时间戳。但根据您的评论，情况显然是这样。

通过这种方式，我们删除了所有不相关的行并使索引更小。随着时间的推移，效果会缓慢降低。重新调整事件视界并不时重新创建索引以摆脱增加的重量。例如，您可以每周执行一次 cron 作业。

更新(重新创建)函数时，您需要重新创建以任何方式使用该函数的所有索引。最好在同一笔交易中。因为辅助函数的 IMMUTABLE 声明有点虚假。但是 Postgres 只接受索引定义中的不可变函数。所以我们不得不撒谎。更多相关信息:
Does PostgreSQL support "accent insensitive" collations?

为什么要有这个功能？这样，所有使用索引的查询都可以保持不变。

通过所有这些更改，查询现在应该快几个数量级。只需一次连续的仅索引扫描即可。你能证实吗？