PostgreSQL在过滤多排序查询时未使用索引

Question

我有一个非常简单的表格

CREATE TABLE approved_posts (
  project_id INTEGER,
  feed_id INTEGER,
  post_id INTEGER,
  approved_time TIMESTAMP NOT NULL,
  post_time TIMESTAMP NOT NULL,
  PRIMARY KEY (project_id, feed_id, post_id)
)

我正在尝试优化这个查询：

SELECT *
FROM approved_posts
WHERE feed_id IN (?, ?, ?)
AND project_id = ?
ORDER BY approved_time DESC, post_time DESC
LIMIT 1;

查询优化器会获取与谓词匹配的每个“approved_post”，将所有100k结果排序，并返回它找到的顶部结果。

我确实在“project_id，feed_id，approved_time，post_time”上有索引，如果我删除“post_time”的排序，或者用单个“=？”替换“IN（？，？，？）”，则查询优化器将使用该索引。然后它只需进行反向索引扫描即可获得第一个结果，速度非常快。

选项A：

 Limit  (cost=0.43..6.57 rows=1 width=24) (actual time=0.101..0.101 rows=1 loops=1)
   ->  Index Scan Backward using approved_posts_approved_time_idx on approved_posts p  (cost=0.43..840483.02 rows=136940 width=24) (actual time=0.100..0.100 rows=1 loops=1)
     Filter: (feed_id = ANY ('{73321,73771,73772,73773,73774}'::integer[]))
     Rows Removed by Filter: 37
 Total runtime: 0.129 ms

选项B：

Limit  (cost=0.43..3.31 rows=1 width=24) (actual time=0.065..0.065 rows=1 loops=1)
   ->  Index Scan Backward using approved_posts_full_pagination_index on approved_posts p  (cost=0.43..126884.70 rows=44049 width=24) (actual time=0.063..0.063 rows=1 loops=1)
     Index Cond: ((project_id = 148772) AND (feed_id = 73321))
 Total runtime: 0.092 ms

但是如果没有这些调整，它的性能就不太好...

Limit  (cost=169792.16..169792.17 rows=1 width=24) (actual time=510.225..510.225 rows=1 loops=1)
   ->  Sort  (cost=169792.16..170118.06 rows=130357 width=24) (actual time=510.224..510.224 rows=1 loops=1)
     Sort Key: approved_time, post_time
     Sort Method: top-N heapsort  Memory: 25kB
     ->  Bitmap Heap Scan on approved_posts p  (cost=12324.41..169140.38 rows=130357 width=24) (actual time=362.210..469.387 rows=126260 loops=1)
           Recheck Cond: (feed_id = ANY ('{73321,73771,73772,73773,73774}'::integer[]))
           ->  Bitmap Index Scan on approved_posts_feed_id_idx  (cost=0.00..12291.82 rows=130357 width=0) (actual time=354.496..354.496 rows=126260 loops=1)
                 Index Cond: (feed_id = ANY ('{73321,73771,73772,73773,73774}'::integer[]))
Total runtime: 510.265 ms

我甚至可以在这五个feed id上添加一个条件索引，它将再次执行正确的操作。

我目前最好的解决方案是将每个feed_id放入自己的查询中，并在它们之间进行大量的UNION。但是这种方法不太可扩展，因为我可能想要从30个feeds中选择前500个，拉取15k行并无故排序。此外，使用这种策略管理偏移量有些复杂。

有人知道如何在我的良好索引数据上执行这个带有两个排序的IN子句，并让Postgres执行正确的操作吗？

我正在使用Postgres 9.3.3。以下是我的索引：

 "approved_posts_project_id_feed_id_post_id_key" UNIQUE CONSTRAINT, btree (project_id, feed_id, post_id)
 "approved_posts_approved_time_idx" btree (approved_time)
 "approved_posts_feed_id_idx" btree (feed_id)
 "approved_posts_full_pagination_index" btree (project_id, feed_id, approved_time, post_time)
 "approved_posts_post_id_idx" btree (post_id)
 "approved_posts_post_time_idx" btree (post_time)
 "approved_posts_project_id_idx" btree (project_id)

所有列都不能为空。

这个表有2百万行，分别属于200个feed ID和19个项目ID。

以下是最常见的Feed ID：

 feed_id | count  
---------+--------
   73607 | 558860
   73837 | 354018
   73832 | 220285
   73836 | 172664
   73321 | 118695
   73819 |  95999
   73821 |  75871
   73056 |  65779
   73070 |  54655
   73827 |  43710
   73079 |  36700
   73574 |  36111
   73055 |  25682
   73072 |  22596
   73589 |  19856
   73953 |  15286
   73159 |  13059
   73839 |   8925

关于每个feedid/projectid配对的最小值/最大值/平均基数，我们有以下数据:

 min |  max   |          avg          
-----+--------+-----------------------
   1 | 559021 | 9427.9140271493212670

- Mike Fairhurst

---

9.3.3 提出了一个问题：为什么不至少是9.3.9（如果无法选择9.4的话）？我们始终建议所有用户运行当前使用的主要版本的最新可用次要发布版本。 - Erwin Brandstetter

我们将根据您的建议进行升级。 - Mike Fairhurst

您提供了所有必要的细节，这使我能够找到您有趣问题的答案。许多问题未能提供基础信息，这在这里是一个不断的困扰 - 而现在您的问题在这方面非常出色。 - Erwin Brandstetter

Answer 1

使用可能值列表中的feed_id，Postgres难以找到最佳查询计划。每个feed_id可能与1-559021行相关联（根据您提供的数字）。对于LIMIT 1的特殊情况，Postgres当前没有足够智能的能力以自主发现潜在的优化方法。使用一个feed_id和每个LIMIT 1的多个查询的UNION ALL（不仅是UNION），再加上另一个外部LIMIT 1（就像您似乎尝试过的那样）展示了这种潜力，但需要复杂的查询串联以适应变量数量的输入值。

还有一种方法可以说服查询规划器使用索引扫描从每个feed_id中选择第一行：使用LATERAL连接重写您的查询：

SELECT a.*
FROM   (VALUES (?), (?), (?)) AS t(feed_id)
     , LATERAL (
   SELECT *
   FROM   approved_posts
   WHERE  project_id = ?
   AND    feed_id = t.feed_id
   ORDER  BY approved_time DESC, post_time DESC
   LIMIT  1
   ) a
ORDER  BY approved_time DESC, post_time DESC
LIMIT  1;

或者，对于变量数量的feed_id值，更加方便的方法是：

SELECT a.*
FROM   unnest(?) AS t(feed_id)  -- provide int[] var
     , LATERAL ( ...

将整数数组传递给变量，例如 '{123, 234, 345}'::int[]。这也可以通过使用带有VARIADIC参数的函数来优雅地实现。然后可以传递一个integer值列表：

• 在单个参数中传递多个值

您在(project_id、feed_id、approved_time、post_time)上的索引适用于此，因为Postgres可以以几乎与正向扫描相同的速度进行反向扫描，但是(project_id、feed_id、approved_time DESC、post_time DESC)更好。请参见：

• 优化时间戳范围（两列）上的查询

如果您不需要返回表的所有列，甚至可以使用仅索引扫描。

您的列approved_time、post_time被定义为NOT NULL。否则，您需要做更多事情：

• 未使用的日期范围查询中的索引

相关答案详细介绍了LATERAL连接技术：

• 优化GROUP BY查询以检索每个用户的最新记录

为什么您的选项A有效？

仔细观察发现两件事情：

->  使用索引扫描approved_posts_approved_time_idx向后扫描
    在approved_posts p上（成本=0.43..840483.02行=136940宽度=24）
                        （实际时间=0.100..0.100行=1循环=1）
     过滤器：（feed_id = ANY ('{73321,73771,73772,73773,73774}'::integer[]))

粗体强调是我的。

1. 使用了不同、更小的索引，只包含(approved_time)。
2. 没有索引条件在feed_id上（在这种情况下不可能），但有一个过滤器。

Postgres选择了一种完全不同的策略：它会从底部开始读取索引行（Index Scan Backward），直到找到一个与您给定的feed_id值之一匹配的行。由于您只有非常少的项目和 feeds (200 feed IDs and 19 project IDs)， chances are it won't have to discard too many rows before the first match - which is the result. 这实际上在拥有更多feed_id值时会变得更快，因为"最新"的行会更早被找到，这与我的第一种方法相比，后者对于更少的值更快。

这是一种有前途的替代策略！根据数据分布和查询中的feeds，它可能比我第一种解决方案更快-请使用此索引启用它:

"approved_posts_foo_idx" btree (project_id, approved_time DESC, post_time DESC)

对于列project_id和feed_id，有选择性地增加统计目标可能会更好，这样可以更准确地估算两种策略之间的临界点。

• PostgreSQL - 如果查询使用enable_nestloop=false时运行速度更快，为什么规划器没有做正确的事情？

---

由于您有一些只包含旧记录的项目 (参见评论)，如果您知道每个项目(和/或每个feed_id)的最大approved_time(和/或post_time)，或者至少有一个上限，则您可以通过提示来改进此查询。

SELECT ...
WHERE  ...
AND   approved_time <= $upper_bound

- Erwin Brandstetter

---

这绝对是教授Postgres使用哪个索引的最优雅方式，而且在我们的查询构建器中更容易适应！而且了解到Postgres在处理大范围值时出现问题也是很好的。 - Mike Fairhurst

今天早上我想到一个问题：如果根本问题在于每个 feed id 只有不到 1 条记录，那么为什么当我们放弃二级排序（只按 approved_time DESC 排序）时，它会选择反向索引扫描而不需要任何其他更改呢？编辑：事实上，仔细想想，由于仅按 approved_time 排序只对 (approved_time, post_time) 进行反向索引扫描，它实际上已经以 approved_time DESC、post_time DESC 的顺序返回数据了。为什么 postgres 会在我们要求按其已经给出的顺序排序时改变其计划呢？ - Mike Fairhurst

@MikeFairhurst：非常好的问题，我自己也对这个临界点感到困惑 - 直到我仔细研究了你的EXPLAIN输出。请考虑查看我答案的补充说明。 - Erwin Brandstetter

太棒了！您提出的索引可行，无需进行查询重写！再次感谢您！ - Mike Fairhurst

我的数据非常临时...我最初使用了三个小的feed_ids，但是没有使用lateral获得更快的结果。然后我注意到project_id比我上次显示的数据更新，所以我找到了最旧的项目并比较了它的数据。在最古老的情况下，需要297毫秒，而使用lateral只需要0.12毫秒。 - Mike Fairhurst

Answer 2

据我所知，如果第一个“where”不是键的第一部分，则该键将不会被使用。尝试在查询中交换您的“where”的顺序以project_id和feed_id为例。

- CoryatJohn

---

还是没有运气！不过还是谢谢你。我已经尝试将 DESC 改为 ASC，以及 posttime/approvedtime 的交换，但没有想到要交换 WHERE 条件。这绝对值得一试！ - Mike Fairhurst