0

I am running a query with an INNER JOIN through a foreign data wrapper (postgres_fdw), and when I view the EXPLAIN ANALYZE, it shows one of the Foreign Scan subnodes underestimates that it will only scan 1 row when it actually scans 7 causing the query to take much longer than anticipated as this causes the other Foreign Scan subnode to loop 7 times which is the main bottleneck of the query. Here is the query and its respective EXPLAIN ANALYZE:

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));

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

I tried running ANALYZE on both tables after raising the default_statistics_target value to 1000, but the planner did not change.

I should also note than when I change the WHERE part of the query with BETWEEN to a series of OR statements, (w.pricedate = (SELECT current_date-6) OR w.pricedate = (SELECT current_date-5) OR...), the query does not use a Nested Loop and runs 7 times as fast. I'm not really sure why that is as using BETWEEN or a series of OR statements produces the same result, but I am assuming is has to do with the planner underestimating the number of rows in the Foreign Scan.

I am running PostgreSQL 12.1 on an Ubuntu 18.04 server.

Let me know if you have any further questions. Thanks!

geckels1
  • 347
  • 1
  • 3
  • 13
  • Do EXPLAIN VERBOSE, capture the query it shows, then go over to the foreign server and do an EXPLAIN ANALYZE of that captured query. – jjanes Jan 29 '20 at 14:23
  • This doesn't seem like a duplicate. Although based on the same scenario, it is a different question. And this time is more or less the correct question to be asking. – jjanes Jan 29 '20 at 14:25
  • Do you have use_remote_estimate turned on, either for the table or for the server? – jjanes Jan 29 '20 at 14:31

0 Answers0