postgresql - 在 postgres 上使用 `random_page_cost` 优化最近的事件搜索和缓存使用

Question

我有一张表，我在其中存储有关客户的信息以及事件的时间戳和时间范围。

我使用的索引如下所示：

event_index（客户 ID，时间）

state_index(customer_id, end, start desc)

绝大多数查询查询最近几天的状态和事件。

这是一个示例查询文本（events与我将描述的相同的问题相同states）：

SELECT "states".*
FROM "states"
WHERE ("states"."customer_id" = $1 AND "states"."start" < $2)
       AND ("states"."end" IS NULL OR "states"."end" > $3)
       AND ("states"."obsolete" = $4)
ORDER BY "states"."start" DESC

我看到有时查询计划器只使用 customer_id 进行过滤，然后使用堆扫描客户的所有行进行过滤：

Sort  (cost=103089.00..103096.17 rows=2869 width=78)
  Sort Key: start DESC
  ->  Bitmap Heap Scan on states  (cost=1222.56..102924.23 rows=2869 width=78)
        Recheck Cond: (customer_id = '----'::bpchar)
        Filter: ((NOT obsolete) AND ((start)::double precision < '1557711009'::double precision) AND ((end IS NULL) OR ((end)::double precision > '1557666000'::double precision)))
        ->  Bitmap Index Scan on states_index  (cost=0.00..1221.85 rows=26820 width=0)
              Index Cond: (customer_id = '----'::bpchar)

这与我在会话中手动看到的相反：

Sort Key: start DESC
Sort Method: quicksort  Memory: 25kB
->  Bitmap Heap Scan on states  (cost=111.12..9338.04 rows=1 width=78) (actual time=141.674..141.674 rows=0 loops=1)
      Recheck Cond: (((customer_id = '-----'::bpchar) AND (end IS NULL) AND (start < '1557349200'::numeric)) OR ((customer_id = '----'::bpchar) AND (end > '1557249200'::numeric) AND (start < '1557349200'::numeric)))
      Filter: ((NOT obsolete) AND ((title)::text = '---'::text))
      Rows Removed by Filter: 112
      Heap Blocks: exact=101
      ->  BitmapOr  (cost=111.12..111.12 rows=2333 width=0) (actual time=4.198..4.198 rows=0 loops=1)
            ->  Bitmap Index Scan on states_index  (cost=0.00..4.57 rows=1 width=0) (actual time=0.086..0.086 rows=0 loops=1)
                  Index Cond: ((customer_id = '----'::bpchar) AND (end IS NULL) AND (start < '1557349200'::numeric))
            ->  Bitmap Index Scan on state_index  (cost=0.00..106.55 rows=2332 width=0) (actual time=4.109..4.109 rows=112 loops=1)
                  Index Cond: ((customer_id = '---'::bpchar) AND (end > '1557262800'::numeric) AND (start < '1557349200'::numeric))

换句话说 - 查询规划器有时会选择仅使用索引的第一列，这会显着降低查询速度。

我明白为什么只在足够小的客户数据并在内存中过滤时才将其带入是有意义的，但问题是这些数据非常稀疏并且可能没有完全缓存（一年前的数据可能没有缓存客户，数据库是几百 GB）。如果索引将最大限度地使用时间戳（如第二个示例中所示） - 由于缓存了最近的数据，结果应该会快得多。

我在上周使用了部分索引来查看查询时间是否下降，但 postgres 有时只使用它。这解决了使用部分索引时的问题，因为该索引中不存在旧行 - 但遗憾的是，即使不需要，postgres 仍然选择更大的索引。我跑了vacuum analyze，但没有明显的效果。

我尝试使用以下方法查看缓存命中：

  Database Name   | Temporary files | Size of temporary files |  Block Hits   | Block Reads 
------------------+-----------------+-------------------------+---------------+-------------
 customers        |            1922 |             18784440622 |   69553504584 |  2401546773

然后我计算了(block_hits/(block_hits + block_reads))：

>>> 69553504584.0 / (69553504584.0 + 2401546773.0)
0.9666243477322406

所以这向我展示了 ~96.6% 的缓存（我希望它更接近 100，因为我知道查询的性质）

我还尝试在上增加统计信息 (SET STATISTICS) customer_id，因为这似乎是对面临查询计划程序问题的人的建议。它也没有帮助（我在...之后运行了分析）。startend

在进一步阅读了这个问题之后，我发现有一种方法可以让查询规划器更喜欢使用低于random_page_cost默认值 (4) 的索引扫描。我还看到了一个帖子支持这里：

https://amplitude.engineering/how-a-single-postgresql-config-change-improved-slow-query-performance-by-50x-85593b8991b0

这对我的用例有意义吗？它会让查询计划者更频繁地（最好总是）使用索引吗？

如果没有 - 我还能做些什么来降低查询时间吗？我知道分区可能非常有效，但似乎有点矫枉过正，据我所读到的内容，我当前的 postgres 版本（9.5.9）并不完全支持分区。

更新：降低后random_page_cost我没有看到决定性的差异。仍然有查询规划器选择只使用其中一部分的情况。索引的结果要慢得多。

任何建议都非常受欢迎。

谢谢 ：）

标签： postgresqloptimizationrds