sql - 按时间范围查询时的 Postgresql 性能问题

Question

我试图了解 Postgres (v10.9) 上的一个奇怪的性能问题。

我们有一个requests 表，我想获取一组特定用户在多个时间范围内发出的所有请求。以下是相关信息:

• 表中没有user_id 列。相反，有一个名为 params 的 jsonb 列，其中 user_id 字段存储为字符串。
• 有问题的用户群非常庞大，有数千人。
• 有一个类型为 timestamptz 的 time 列，它使用标准 BTREE 索引进行索引。
• params->>'user_id' 上还有一个单独的 BTREE 索引。

我正在运行的查询基于以下模板:

SELECT *
FROM requests
WHERE params->>'user_id' = ANY (VALUES ('id1'), ('id2'), ('id3')...)
AND time > 't1' AND time < 't2'

这里的ids和times是实际ids和times的占位符。

我在几个连续的时间范围内运行这样的查询，每个时间范围为 2 周。前几个时间范围的查询每个都需要几分钟，这在生产方面显然很长，但对于研究目的来说还可以。然后突然间查询运行时间急剧上升，他们开始每次都花费数小时，即使对于离线目的，这也开始变得站不住脚。

这种尖峰每次都发生在相同的范围内。值得注意的是，在此时间范围内，总请求数增加了 1.5 倍。与之前的时间范围相比肯定更多，但不足以保证出现一个完整数量级的峰值。

这里是 EXPLAIN ANALYZE 的最后一个时间范围的合理运行时间的输出:

Hash Join  (cost=442.69..446645.35 rows=986171 width=1217) (actual time=66.305..203593.238 rows=445175 loops=1)
  Hash Cond: ((requests.params ->> 'user_id'::text) = \"*VALUES*\".column1)
  ->  Index Scan using requests_time_idx on requests  (cost=0.56..428686.19 rows=1976888 width=1217) (actual time=14.336..201643.439 rows=2139604 loops=1)
        Index Cond: ((\"time\" > '2019-02-12 22:00:00+00'::timestamp with time zone) AND (\"time\" < '2019-02-26 22:00:00+00'::timestamp with time zone))
  ->  Hash  (cost=439.62..439.62 rows=200 width=32) (actual time=43.818..43.818 rows=29175 loops=1)
        Buckets: 32768 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 2536kB
        ->  HashAggregate  (cost=437.62..439.62 rows=200 width=32) (actual time=24.887..33.775 rows=29175 loops=1)
              Group Key: \"*VALUES*\".column1
              ->  Values Scan on \"*VALUES*\"  (cost=0.00..364.69 rows=29175 width=32) (actual time=0.006..10.303 rows=29175 loops=1)
Planning time: 133.807 ms
Execution time: 203697.360 ms

如果我理解正确的话，似乎大部分时间都花在按时间范围过滤请求上，即使:

• 似乎使用了时间索引。
• 在不对用户进行过滤器的情况下运行相同的查询(基本上只是按时间范围获取所有请求)时，它们都运行正常。

如果有任何关于如何解决此问题的想法，我们将不胜感激，谢谢!

Answer 1

由于您要检索如此多的行，查询永远不会很快。

很遗憾，没有一个索引可以涵盖这两种情况，但您可以使用这两个:

CREATE INDEX ON requests ((params->>'user_id'));
CREATE INDEX ON requests (time);

然后您可以希望通过“位图或”连接两个位图索引扫描。

我不确定这是否会提高性能； PostgreSQL 可能仍会选择当前的计划，这不是一个坏计划。如果您的索引已缓存或对存储的随机访问速度很快，请相应地设置 effective_cache_size 或 random_page_cost，这将使 PostgreSQL 倾向于索引扫描。