未使用 PostgresQL 索引

Question

我有一个名为 item 的包含几百万行的表，其中的列如下所示:

CREATE TABLE item (
  id bigint NOT NULL,
  company_id bigint NOT NULL,
  date_created timestamp with time zone,
  ....
)

company_id 有一个索引

CREATE INDEX idx_company_id ON photo USING btree (company_id);

此表经常搜索某个客户的最后 10 个项目，即，

SELECT * FROM item WHERE company_id = 5 ORDER BY date_created LIMIT 10;

目前，一个客户约占该表中数据的 75%，另外 25% 的数据分布在 25 个左右的其他客户中，这意味着 75% 的行的公司 ID 为5，其他行的公司 ID 介于 6 和 25 之间。

除主要公司 (id = 5) 外，所有公司的查询通常都运行得非常快。我能理解为什么 company_id 上的索引可以用于除 5 以外的公司。

我已经尝试了不同的索引来使这个搜索对公司 5 更有效率。似乎最有意义的是

CREATE INDEX idx_date_created
ON item (date_created DESC NULLS LAST);

如果我添加此索引，对主要公司 (id = 5) 的查询会大大改善，但对所有其他公司的查询都会变得很糟糕。

使用和不使用新索引时公司 id 5 和 6 的 EXPLAIN ANALYZE 的一些结果:

公司编号 5

新索引之前

QUERY PLAN
Limit  (cost=214874.63..214874.65 rows=10 width=639) (actual time=10481.989..10482.017 rows=10 loops=1)
  ->  Sort  (cost=214874.63..218560.33 rows=1474282 width=639) (actual time=10481.985..10481.994 rows=10 loops=1)
        Sort Key: photo_created
        Sort Method: top-N heapsort  Memory: 35kB
        ->  Seq Scan on photo  (cost=0.00..183015.92 rows=1474282 width=639) (actual time=0.009..5345.551 rows=1473561 loops=1)
              Filter: (company_id = 5)
              Rows Removed by Filter: 402513
Total runtime: 10482.075 ms

新索引后:

QUERY PLAN
Limit  (cost=0.43..1.98 rows=10 width=639) (actual time=0.087..0.120 rows=10 loops=1)
  ->  Index Scan using idx_photo__photo_created on photo  (cost=0.43..228408.04 rows=1474282 width=639) (actual time=0.084..0.099 rows=10 loops=1)
        Filter: (company_id = 5)
        Rows Removed by Filter: 26
Total runtime: 0.164 ms

公司编号 6

新索引之前:

QUERY PLAN
Limit  (cost=2204.27..2204.30 rows=10 width=639) (actual time=0.044..0.053 rows=3 loops=1)
  ->  Sort  (cost=2204.27..2207.55 rows=1310 width=639) (actual time=0.040..0.044 rows=3 loops=1)
        Sort Key: photo_created
        Sort Method: quicksort  Memory: 28kB
        ->  Index Scan using idx_photo__company_id on photo  (cost=0.43..2175.96 rows=1310 width=639) (actual time=0.020..0.026 rows=3 loops=1)
              Index Cond: (company_id = 6)
Total runtime: 0.100 ms

新索引后:

QUERY PLAN
Limit  (cost=0.43..1744.00 rows=10 width=639) (actual time=0.039..3938.986 rows=3 loops=1)
  ->  Index Scan using idx_photo__photo_created on photo  (cost=0.43..228408.04 rows=1310 width=639) (actual time=0.035..3938.975 rows=3 loops=1)
        Filter: (company_id = 6)
        Rows Removed by Filter: 1876071
Total runtime: 3939.028 ms

我已经在表上运行了完整的 VACUUM 和 ANALYZE，因此 PostgreSQL 应该有最新的统计信息。有什么想法可以让 PostgreSQL 为被查询的公司选择正确的索引吗？

Answer 1

这被称为 "abort-early plan problem" ，多年来一直是一个长期的错误优化。提前中止的计划在可行时令人惊奇，但在不可行时则很糟糕；有关更详细的说明，请参阅链接的邮件列表线程。基本上，规划器认为它会在不扫描整个 date_created 索引的情况下为客户 6 找到您想要的 10 行，这是错误的。

在 PostgreSQL 10(非测试版)之前，没有任何硬性的方法可以明确地改进此查询。您要做的是以各种方式插入查询规划器，以期获得您想要的结果。主要方法包括使 PostgreSQL 更可能使用多列索引的任何方法，例如:

• 降低random_page_cost (无论如何，如果您使用的是 SSD，这是个好主意)。
• 降低cpu_index_tuple_cost

您也可以通过使用表格统计信息来修复计划器行为。这包括:

• 提高statistics_target对于表并再次运行 ANALYZE，以使 PostgreSQL 获取更多样本并更好地了解行分布；
• 增加n_distinct在统计数据中准确反射(reflect) customer_id 的数量或不同的 created_dates。

但是，所有这些解决方案都是近似的，如果将来随着数据的变化查询性能出现问题，这应该是您首先要查看的查询。

在 PostgreSQL 10 中，您将能够创建 Cross-Column Stats这应该更可靠地改善情况。根据这对您来说有多糟糕，您可以尝试使用测试版。

如果这些都不起作用，我建议使用 Freenode 上的#postgresql IRC channel 或pgsql-performance mailing list .那里的人会要求您提供详细的牌 table 统计数据，以便提出一些建议。