为什么postgresql计划器选择坏计划，只有几个特定的值

Question

我有一个更大的表在postgresql 151 -也许5000万行和增长。列mmsi大约有30k个不同的值，因此每个mmsi有1000多个行。

我的问题是，我有一个查询，我需要在DB加载期间重复执行，并且对于mmsi的某些值，它需要数百秒而不是毫秒。模型查询就是

SELECT max(to_timestamp) FROM track WHERE mmsi = :mmsi

在EXPLAIN输出中可以看到异常。坏情况(只发生在mmsi值的一小部分):

trackdb=# EXPLAIN SELECT max(to_timestamp) FROM track WHERE mmsi = 354710000;
QUERY PLAN
----------
Result  (cost=413.16..413.17 rows=1 width=8)
InitPlan 1 (returns $0)
- >  Limit  (cost=0.56..413.16 rows=1 width=8)
- >  Index Scan Backward using ix_track_to_timestamp on track  (cost=0.56..3894939.14 rows=9440 width=8)
Index Cond: (to_timestamp IS NOT NULL)
Filter: (mmsi = 354710000)
(6 rows)

好案例(绝大多数):

trackdb=# EXPLAIN SELECT max(to_timestamp) FROM track WHERE mmsi = 354710001;
QUERY PLAN
----------
Aggregate  (cost=1637.99..1638.00 rows=1 width=8)
- >  Index Scan using ix_track_mmsi on track  (cost=0.44..1635.28 rows=1082 width=8)
Index Cond: (mmsi = 354710001)
(3 rows)

现在，我注意到在坏情况下估计的行数更大。我在postgresql的统计数据(pg_stats.histogram_bounds)中看不到任何东西来解释这个

当我ANALYZE表时，问题似乎发生了变化，因为触发问题的特定值变得不同。但无论如何，由于这是在DB加载期间需要的，ANALYZE不是一个解决方案。

我难住了。有人知道会发生什么吗?

为了澄清，我知道如何围绕它工作，例如，在应用max之前具体化行。但不理解让我很不开心。

Answer 1

正如Laurenz所解释的，问题是PostgreSQL认为mmsi = 354710000的大约10,000行随机分布在to_timestamp的值上，因此认为通过按顺序扫描to_timestamp的索引，它可以在找到第一个符合mmsi = 354710000的索引时立即停止，而且这将很快发生。但是所有的mmsi = 354710000都在索引的错误端，它实际上并不会很快发生。在统计数据中对此无能为力，因为没有"处理"。它可以抓住，以更好地告知它的想法。也许将来对自定义统计功能的一些扩展会做到这一点。

编辑:为了澄清，我知道如何围绕它工作，例如在应用max之前物化行。

一个更好的围绕的解决方案可能是(mmsi,to_timestamp)上的索引。这不仅可以解决当前选择非常糟糕的计划的情况，还可以通过提供更好的选择来大幅改善当前使用可容忍计划的情况。你不需要重写查询。您可以只在mmsi上删除现有的索引，因为没有理由同时使用这两个索引。