代码之家 › 专栏 › 技术社区 › geckels1

PostgreSQL-为什么planner在进行外部扫描时低估了行数?

postgres-fdw foreign-data-wrapper postgresql-performance sqlperformance postgresql

0

geckels1 · 技术社区 · 6 年前

我的问题是,当两个查询都返回相同的结果时,为什么计划人员决定做出不同的决策?使用嵌套循环查询运行77秒,不使用嵌套循环运行13秒,而运行13秒的查询非常难看和不雅,这使我认为有更好的方法来编写它。

这是两个问题。注意,两者之间的区别在于WHERE子句如何按日期筛选,其中第一个使用between,第二个使用一系列OR语句。我知道很奇怪,当前日期被包装在它们自己的子查询中,但这是因为这些查询使用的是外部数据包装器。这允许将当前日期作为不可变对象传递,以大大提高性能。

SELECT ROUND(AVG(m.forecast - w.wind),6) from pjm.wind_forecast_recent w
    INNER JOIN pjm.load_forecast_recent m ON w.pricedate = m.pricedate AND w.hour = m.hour
  WHERE w.hour = 5 AND m.area = 'RTO_COMBINED' AND 
            (w.pricedate BETWEEN (SELECT current_date-6) AND (SELECT current_date));

-----------

SELECT ROUND(AVG(m.forecast - w.wind),6) from pjm.wind_forecast_recent w
    INNER JOIN pjm.load_forecast_recent m ON w.pricedate = m.pricedate AND w.hour = m.hour
  WHERE w.hour = 5 AND m.area = 'RTO_COMBINED' AND (
    w.pricedate = (SELECT current_date-6) OR
    w.pricedate = (SELECT current_date-5) OR
    w.pricedate = (SELECT current_date-4) OR
    w.pricedate = (SELECT current_date-3) OR
    w.pricedate = (SELECT current_date-2) OR
    w.pricedate = (SELECT current_date-1) OR
    w.pricedate = (SELECT current_date))

Aggregate  (cost=842341.01..842341.02 rows=1 width=32) (actual time=77120.088..77120.089 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
  ->  Nested Loop  (cost=840333.25..842340.97 rows=1 width=18) (actual time=14719.661..77119.994 rows=7 loops=1)
        ->  Foreign Scan on wind_forecast_recent w  (cost=242218.45..242218.49 rows=1 width=18) (actual time=3184.714..3184.720 rows=7 loops=1)
        ->  Foreign Scan on load_forecast_recent m  (cost=598114.80..600122.47 rows=1 width=16) (actual time=10531.723..10531.724 rows=1 loops=7)
Planning Time: 744.979 ms
Execution Time: 77227.512 ms

Aggregate  (cost=841657.94..841657.95 rows=1 width=32) (actual time=13683.022..13683.023 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
  InitPlan 3 (returns $2)
    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
  InitPlan 4 (returns $3)
    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
  InitPlan 5 (returns $4)
    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
  InitPlan 6 (returns $5)
    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
  InitPlan 7 (returns $6)
    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
  ->  Foreign Scan  (cost=833725.15..841657.83 rows=1 width=18) (actual time=13682.974..13682.977 rows=7 loops=1)
        Relations: (pjm.wind_forecast_recent w) INNER JOIN (pjm.load_forecast_recent m)
Planning Time: 332.870 ms
JIT:
  Functions: 16
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 4.163 ms, Inlining 15.088 ms, Optimization 44.489 ms, Emission 28.064 ms, Total 91.804 ms
Execution Time: 13724.094 ms

我在Ubuntu 18.04服务器上运行PostgreSQL 12.1。

0 回复 | 直到 6 年前

1

2

Laurenz Albe 6 年前

规划者并不决定使用基于深层推理的特定连接策略,而是简单地构造所有可能的连接策略,估计成本并选择最便宜的连接策略。

也就是说,如果外部表很小,那么嵌套循环联接通常是最佳选择,这样就不必经常执行内部循环。此外,对内部表的连接条件进行索引可以大大降低嵌套循环连接的成本,使其成为一种有吸引力的策略。

Foreign Scan on wind_forecast_recent w  (cost=... rows=1 ...) (actual ... rows=7 ...)

这将导致内环执行7次而不是一次,因此执行时间是70秒而不是10秒。

您应该收集 wind_forecast_recent :

ANALYZE wind_forecast_recent;

记住,autoanalyze可以请外宾吃饭,你得自己处理。

use_remote_estimate 选项,并确保远程数据库中的表统计信息准确无误。