ruby-on-rails - 按日期和 created_at 排序的多列索引对不同的查询表现出奇怪的行为

Question

在 postgres 10 上，我有一个这样的查询，对于一个包含数百万行的表，以获取属于教室的最新帖子:

SELECT "posts".*
FROM "posts"
WHERE "posts"."school_id" = 1
  AND "posts"."classroom_id" IN (10, 11, 12, 13, 14, 15, 16, 17, 18, 19)
ORDER BY date desc, created_at desc
LIMIT 30 OFFSET 30;

假设教室只属于一所学校。
我有一个像这样的索引:

t.index ["date", "created_at", "school_id", "classroom_id"], name: "optimize_post_pagination"

当我运行查询时，它会像我希望的那样向后进行索引扫描，并在 0.7 毫秒内返回。

Limit  (cost=127336.95..254673.34 rows=30 width=494) (actual time=0.189..0.242 rows=30 loops=1)
  ->  Index Scan Backward using optimize_post_pagination on posts  (cost=0.56..1018691.68 rows=240 width=494) (actual time=0.103..0.236 rows=60 loops=1)
        Index Cond: (school_id = 1)
"        Filter: (classroom_id = ANY ('{10,11,...}'::integer[]))"
Planning time: 0.112 ms
Execution time: 0.260 ms

但是，当我将查询更改为仅包含几个教室时:

SELECT "posts".*
FROM "posts"
WHERE "posts"."school_id" = 1
  AND "posts"."classroom_id" IN (10, 11)
ORDER BY date desc, created_at desc
LIMIT 30 OFFSET 30;

它吓坏了，做了很多额外的工作，花了将近 4 秒:

  ->  Sort  (cost=933989.58..933989.68 rows=40 width=494) (actual time=3857.216..3857.219 rows=60 loops=1)
"        Sort Key: date DESC, created_at DESC"
        Sort Method: top-N heapsort  Memory: 61kB
        ->  Bitmap Heap Scan on posts  (cost=615054.27..933988.51 rows=40 width=494) (actual time=2700.871..3851.518 rows=18826 loops=1)
              Recheck Cond: (school_id = 1)
"              Filter: (classroom_id = ANY ('{10,11}'::integer[]))"
              Rows Removed by Filter: 86099
              Heap Blocks: exact=29256
              ->  Bitmap Index Scan on optimize_post_pagination  (cost=0.00..615054.26 rows=105020 width=0) (actual time=2696.385..2696.385 rows=104925 loops=1)
                    Index Cond: (school_id = 485)

更奇怪的是，如果我放下 WHERE school_id的条款，教室(有几个或多个)的两种情况都可以通过向后索引扫描快速运行。
This index cookbook建议将 ORDER BY 索引列放在最后，如下所示:

t.index ["school_id", "classroom_id", "date", "created_at"], name: "activity_page_index"

但这使我的查询速度变慢，即使成本要低得多。

Limit  (cost=993.93..994.00 rows=30 width=494) (actual time=208.443..208.452 rows=30 loops=1)
  ->  Sort  (cost=993.85..994.45 rows=240 width=494) (actual time=208.436..208.443 rows=60 loops=1)
"        Sort Key: date DESC, created_at DESC"
        Sort Method: top-N heapsort  Memory: 118kB
        ->  Index Scan using activity_page_index on posts  (cost=0.56..985.56 rows=240 width=494) (actual time=0.032..178.147 rows=102403 loops=1)
"              Index Cond: ((school_id = 1) AND (classroom_id = ANY ('{10,11,...}'::integer[])))"
Planning time: 0.132 ms
Execution time: 208.482 ms

有趣的是，随着 activity_page_index查询时，它在用较少的教室进行查询时不会改变其行为。
那么，几个问题 :

使用原始查询，为什么教室数量会产生如此巨大的差异？

为什么掉线school_id WHERE 子句使两种情况都运行得很快？

为什么掉线school_id WHERE 子句使两种情况都运行得很快，即使索引仍然包含 school_id ?

高成本查询如何快速完成(65883 -> 0.7ms)而低成本查询完成更慢(994 -> 208ms)？

其他注意事项

需要同时订购date和 created_at ，即使它们看起来是多余的。

Answer 1

如图所示，您的第一个计划对于您的查询似乎是不可能的。 school_id = 1 标准应该显示为索引条件或过滤条件，但您不会在任何一个条件中显示它。

With the original query, why would the number of classrooms make such a massive difference?

使用原始计划，它通过遍历索引以所需的顺序获取行。一旦它累积了 60 行满足非索引标准，它就会提前停止。因此，其他标准的选择性越强，在获得足够多的行以提前停止之前，它需要遍历的索引最多。从列表中删除教室使其更具选择性，因此使该计划看起来不那么有吸引力。在某些时候，它越过一条线，看起来比其他东西更不吸引人。

Why does dropping the school_id WHERE clause make both cases run fast?

你说每个教室只属于一所学校。但是 PostgreSQL 不知道，它认为这两个标准是独立的，因此通过将两个单独的估计相乘得到整体估计的选择性。这使得它对整体选择性的估计非常具有误导性，这使得已经排序的索引扫描看起来比实际情况更糟。不指定多余的 school_id 可以防止它对标准的独立性做出这种错误的假设。您可以创建多列统计信息来尝试克服这个问题，但在我看来，直到 v13(由于我不明白的原因)，这实际上并不能帮助您处理此查询。
这是关于估计过程，而不是执行。所以 school_id 是否在索引中并不重要。

How can a high cost query finish quickly (65883 -> 0.7ms) and a lower cost query finish slower (994 -> 208ms)?

"It is difficult to make predictions, especially about the future."成本估算是预测。有时它们的效果不是很好。