主页 / dba / 问题 / 268153
Accepted
daniel9x
Asked: 2020-05-30 10:33:13 +0800 CST

Postgres 10.6：为什么没有使用特定分区的索引？

  • 772

我真的很困惑是什么导致了这个问题，并希望得到一些指导。

首先，这是在我们的 STAGE 环境中，我们根据createdTime每个月对该表进行分区。我已经三次检查了每个分区和索引，每个月的所有内容都是相同的。此外，我们的 PRODUCTION 数据库表我已经对以下查询运行了 EXPLAIN ANALYZE：

explain analyze select * from ubdrecord
where jsonb_extract_path_text("metadata",'deliveryNoteInfo','deliveryNoteNumber') =  '3535359152';

这导致以下结果：

Append  (cost=0.43..23499.50 rows=21693 width=3503) (actual time=10.682..11.823 rows=7 loops=1)
  ->  Index Scan using p_01_2019_deliverynotenumberindex on ubdrecord_partition_01_2019  (cost=0.43..53.50 rows=47 width=765) (actual time=0.743..0.744 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_02_2019_deliverynotenumberindex on ubdrecord_partition_02_2019  (cost=0.43..34.23 rows=30 width=808) (actual time=0.892..0.893 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_03_2019_deliverynotenumberindex on ubdrecord_partition_03_2019  (cost=0.43..31.81 rows=28 width=800) (actual time=0.682..0.682 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_04_2019_deliverynotenumberindex on ubdrecord_partition_04_2019  (cost=0.43..37.17 rows=32 width=822) (actual time=0.904..0.904 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_05_2019_deliverynotenumberindex on ubdrecord_partition_05_2019  (cost=0.43..37.12 rows=33 width=827) (actual time=0.821..0.821 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_06_2019_deliverynotenumberindex on ubdrecord_partition_06_2019  (cost=0.43..50.06 rows=44 width=821) (actual time=0.720..0.720 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_07_2019_deliverynotenumberindex on ubdrecord_partition_07_2019  (cost=0.43..45.29 rows=40 width=816) (actual time=0.773..0.774 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_08_2019_deliverynotenumberindex on ubdrecord_partition_08_2019  (cost=0.43..37.82 rows=33 width=799) (actual time=0.950..0.950 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_09_2019_deliverynotenumberindex on ubdrecord_partition_09_2019  (cost=0.43..42.18 rows=37 width=843) (actual time=1.111..1.111 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_10_2019_deliverynotenumberindex on ubdrecord_partition_10_2019  (cost=0.43..44.22 rows=39 width=815) (actual time=0.765..0.765 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_11_2019_deliverynotenumberindex on ubdrecord_partition_11_2019  (cost=0.43..55.72 rows=49 width=738) (actual time=1.141..1.141 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_12_2019_deliverynotenumberindex on ubdrecord_partition_12_2019  (cost=0.43..37.24 rows=32 width=833) (actual time=0.720..0.720 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_01_2020_deliverynotenumberindex on ubdrecord_partition_01_2020  (cost=0.43..41.19 rows=36 width=814) (actual time=0.027..0.027 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_02_2020_deliverynotenumberindex on ubdrecord_partition_02_2020  (cost=0.43..39.26 rows=34 width=848) (actual time=0.027..0.027 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_03_2020_deliverynotenumberindex on ubdrecord_partition_03_2020  (cost=0.43..45.85 rows=40 width=856) (actual time=0.026..0.027 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_04_2020_deliverynotenumberindex on ubdrecord_partition_04_2020  (cost=0.43..3.77 rows=2 width=811) (actual time=0.025..0.026 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Bitmap Heap Scan on ubdrecord_partition_05_2020  (cost=241.19..22846.59 rows=21130 width=3575) (actual time=0.349..1.451 rows=7 loops=1)
        Recheck Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
        Heap Blocks: exact=7
        ->  Bitmap Index Scan on p_05_2020_deliverynotenumberindex  (cost=0.00..235.91 rows=21130 width=0) (actual time=0.338..0.338 rows=7 loops=1)
              Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_06_2020_deliverynotenumberindex on ubdrecord_partition_06_2020  (cost=0.14..2.36 rows=1 width=3575) (actual time=0.006..0.006 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_07_2020_deliverynotenumberindex on ubdrecord_partition_07_2020  (cost=0.14..2.36 rows=1 width=3575) (actual time=0.005..0.005 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_08_2020_deliverynotenumberindex on ubdrecord_partition_08_2020  (cost=0.14..2.36 rows=1 width=3575) (actual time=0.004..0.005 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_09_2020_deliverynotenumberindex on ubdrecord_partition_09_2020  (cost=0.14..2.36 rows=1 width=3575) (actual time=0.004..0.004 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_10_2020_deliverynotenumberindex on ubdrecord_partition_10_2020  (cost=0.14..2.36 rows=1 width=3575) (actual time=0.004..0.004 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_11_2020_deliverynotenumberindex on ubdrecord_partition_11_2020  (cost=0.14..2.36 rows=1 width=3575) (actual time=0.006..0.006 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
  ->  Index Scan using p_12_2020_deliverynotenumberindex on ubdrecord_partition_12_2020  (cost=0.14..2.36 rows=1 width=3575) (actual time=0.004..0.005 rows=0 loops=1)
        Index Cond: (jsonb_extract_path_text(metadata, VARIADIC '{deliveryNoteInfo,deliveryNoteNumber}'::text[]) = '3535359152'::text)
Planning time: 192.498 ms
Execution time: 11.997 ms

如您所见，当前月份没有像其他月份一样被扫描。这个月有两点与众不同：

1）这是我们所在的当前月份（因此对该表有很多写入）和

2) 这是实际包含一些结果的月份。

我没有探讨这些特殊区别的原因是因为我们的生产环境在本月完美地扫描索引。我应该指出每个环境中的数据和读/写模式也非常相似。我们已经验证了两种环境中的分区和索引在其特定配置中是相同的，但很好奇其他更广泛的数据库参数设置可能是导致这种差异的原因。任何见解将不胜感激，我可以提供您需要的任何其他信息，只需询问即可。

更新：这对我们来说是个问题的原因是我们正在运行的实际查询使用其他 where 子句参数（它们也被索引，尽管对于这种特殊情况来说效率低得多）。我们不可能删除其他索引，同样，在生产环境中，使用了正确的索引并且查询很快返回。但是在 STG 中，未能使用正确的索引会导致查询在几分钟内无法返回。我想后续问题是我们如何强制查询计划使用特定索引（如果很难确定为什么不使用它）？我希望我已经成功解释了。同样，两个环境之间的 DATA 几乎相同。

postgresql index-tuning

2 个回答

  1. 所以，据我所知，这不是问题；规划器的行为非常理智，查询执行得很快。而且，需要明确的是，它使用的是当月的索引，它只是使用不同的计划，将位图索引扫描馈送到位图堆扫描而不是单级索引扫描。

    您看到这个的原因 - 仅针对有结果的分区的不同计划 - 是因为您在jsonb_extract_path_text("metadata", 'deliveryNoteInfo', 'deliveryNoteNumber'). 数据库收集除每一列之外的每个表达式索引的统计信息，因此能够从这些统计信息中判断当前分区以外的分区可能只有很少的结果（行 = 47，行 = 30 等，对于早几个月），并且鉴于此，它对这些分区使用索引扫描节点。对于当前月份，统计数据表明应该有大约 21130 行，因此它使用位图索引扫描来代替，这在预期匹配许多行时要快得多。最终只找到了 7 个，但如果其他 ID 可以有很多

    统计数据的几个部分可能会起作用。如果您想深入了解，pg_stats是您可以查看的地方。让我们先去掉可能与这种情况无关的部分。这里最不可能涉及的统计数据是 most_common_vals；jsonb_extract_path_text("metadata", 'deliveryNoteInfo', 'deliveryNoteNumber')除非此 ID 是表格中最常见的值之一（我假设没有给出只有 7 个），否则它不太可能出现在此列表中。第二，n_distinct，也可能不是造成这种情况的原因，因为我假设每个月唯一的 deliveryNoteNumbers 的基数变化不大。

    histogram_bounds但是，绝对可能导致计划者认为其他月份没有可能匹配的deliveryNoteNumbers，特别是如果deliveryNoteNumber 以任何方式与时间相关（无论是单调增加还是从时间戳派生都会这样做） - 而这我猜你为什么会看到这个计划。使用直方图边界，计划者会看到 1 月的 deliveryNoteNumbers 的范围往往从（我正在编造这些）3532000000 到 3533000000，2 月的下一个块，依此类推。它不确定这一点（不能保证统计信息是正确的；在各种情况下都会跳过统计信息收集），因此无论如何它都必须检查分区，但出于规划目的，它能够预测会有结果很少，所以它适用于索引扫描。如果确实所有其他月份的 deliveryNoteNumbers 都低于查询中的那个，那么这是对每个分区索引的快速检查。对于当前月份，直方图将与查询中的 deliveryNoteNumber 重叠，并且规划器会假设会找到更多行。

    这是来自 2ndQuadrant 的一篇博文，最后，它探讨了表达式索引如何产生更好的计划。

    • 1
  2. Best Answer

    您的表格统计信息很可能已过时。

    尝试以下操作：

    ANALYZE ubdrecord_partition_05_2020;

    如果这改善了估计，您可以减少autovacuum_analyze_scale_factor分区以让 PostgreSQL 更频繁地计算统计信息。

    • 1

相关问题