Postgresql 对索引列的查询非常慢

Question

我有一个非常简单的查询，它使用 json 数据连接主表:

WITH
  timecode_range AS
  (
    SELECT
      (t->>'table_id')::integer AS table_id,
      (t->>'timecode_from')::bigint AS timecode_from,
      (t->>'timecode_to')::bigint AS timecode_to
    FROM (SELECT '{"table_id":1,"timecode_from":19890328,"timecode_to":119899328}'::jsonb t) rowset
  )
SELECT n.*
FROM partition.json_notification n
INNER JOIN timecode_range r ON n.table_id = r.table_id AND n.timecode > r.timecode_from AND n.timecode <= r.timecode_to

当“timecode_range”仅返回 1 条记录时，它工作得很好:

Nested Loop  (cost=0.43..4668.80 rows=1416 width=97) (actual time=0.352..0.352 rows=0 loops=1)
  CTE timecode_range
    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
  ->  CTE Scan on timecode_range r  (cost=0.00..0.02 rows=1 width=20) (actual time=0.007..0.007 rows=1 loops=1)
  ->  Index Scan using json_notification_pkey on json_notification n  (cost=0.42..4654.61 rows=1416 width=97) (actual time=0.322..0.322 rows=0 loops=1)
        Index Cond: ((timecode > r.timecode_from) AND (timecode <= r.timecode_to))
        Filter: (r.table_id = table_id)
Planning time: 2.292 ms
Execution time: 0.665 ms

但是当我需要返回多条记录时:

WITH
  timecode_range AS
  (
    SELECT
      (t->>'table_id')::integer AS table_id,
      (t->>'timecode_from')::bigint AS timecode_from,
      (t->>'timecode_to')::bigint AS timecode_to
    FROM (SELECT json_array_elements('[{"table_id":1,"timecode_from":19890328,"timecode_to":119899328}]') t) rowset
  )
SELECT n.*
FROM partition.json_notification n
INNER JOIN timecode_range r ON n.table_id = r.table_id AND n.timecode > r.timecode_from AND n.timecode <= r.timecode_to

它开始使用顺序扫描，执行时间急剧增加 :(

Hash Join  (cost=7.01..37289.68 rows=92068 width=97) (actual time=418.563..418.563 rows=0 loops=1)
  Hash Cond: (n.table_id = r.table_id)
  Join Filter: ((n.timecode > r.timecode_from) AND (n.timecode <= r.timecode_to))
  Rows Removed by Join Filter: 14444
  CTE timecode_range
    ->  Subquery Scan on rowset  (cost=0.00..3.76 rows=100 width=32) (actual time=0.233..0.234 rows=1 loops=1)
          ->  Result  (cost=0.00..0.51 rows=100 width=0) (actual time=0.218..0.218 rows=1 loops=1)
  ->  Seq Scan on json_notification n  (cost=0.00..21703.36 rows=840036 width=97) (actual time=0.205..312.991 rows=840036 loops=1)
  ->  Hash  (cost=2.00..2.00 rows=100 width=20) (actual time=0.239..0.239 rows=1 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  CTE Scan on timecode_range r  (cost=0.00..2.00 rows=100 width=20) (actual time=0.235..0.236 rows=1 loops=1)
Planning time: 4.729 ms
Execution time: 418.937 ms

我做错了什么？

Answer 1

PostgreSQL 无法估计从表函数返回的行数，因此它使用 CREATE FUNCTION 中指定的 ROWS 值(默认 1000)。

对于 json_array_elements，此值设置为 100:

SELECT prorows FROM pg_proc WHERE proname = 'json_array_elements';
┌─────────┐
│ prorows │
├─────────┤
│     100 │
└─────────┘
(1 row)

但在您的情况下，该函数仅返回 1 行。

这种错误估计使 PostgreSQL 选择了另一种连接策略(散列连接而不是嵌套循环)，从而导致执行时间更长。

如果您可以选择 PostgreSQL 可以估计的表函数以外的其他构造(例如 VALUES 语句)，您将获得更好的计划。

如果您可以安全地指定上限，另一种方法是在 CTE 定义中使用 LIMIT 子句。

如果你认为PostgreSQL在超过一定行数切换到hash join时是错误的，你可以测试如下:

• 运行查询(使用顺序扫描和散列连接)并测量持续时间(psql 的 \timing 命令会有所帮助)。

• 强制嵌套循环连接:

  SET enable_hashjoin=off;
  SET enable_mergejoin=off;
  

• 再次运行查询(使用嵌套循环连接)并测量持续时间。

如果 PostgreSQL 确实是错误的，您可以通过将 random_page_cost 降低到接近 seq_page_cost 的值来调整优化器参数。