0

A select from a foreign wrapped view takes 15+ times longer to complete than the direct select on the source database.

Tried Adding USE_REMOTE_ESTIMATE 'true' to the foreign server. Also tried killing the where clause altogether (baking it into the view), so I know there are no predicate/aggregate pushdown issues!

-- On source system (massively complex union of 200 schemas), AWS RDS PG 9.5
explain verbose select * from stats where company_id = 26531;
-- [ 2125 lines of stuff!]
select * from stats where company_id = 26531;
-- completes in 1 - 2.5 seconds

-- From FDW (entirely different AWS RDS instance w/ PG 11)
explain verbose 
select * from remoteschema.stats where company_id = 26531;
/*
Foreign Scan on stats  (cost=311780.75..323299.91 rows=306770 width=394)
Output: columns
Remote SQL: SELECT columns FROM remoteschema.stats WHERE ((company_id = 26531))
*/
select * from remoteschema.stats where company_id = 26531;
-- completes in 20 seconds

There are no intermediate results to ship (no local joins), so I can't figure out why so slow. I've read PG 9.5 FDW has issues that later versions addressed. Is it that I just have to upgrade that database?

quickdraw
  • 101
  • 8
  • What is the impact of network lag between these two systems for this query and how are you measuring that? From what you have said it seems like you are running the first query directly on the 9.5 system rather than from the 11 system and transferring the results. – Richard Huxton Sep 20 '19 at 19:54
  • Good point about network latency. However the result is only 1800+ rows with a few hundred bytes row length. – quickdraw Sep 20 '19 at 19:59
  • That's wildly different from what PG is predicting: `...rows=306770 ...` – Richard Huxton Sep 20 '19 at 21:25
  • @RichardHuxton That is a big discrepancy, but because 3 joined entities are each views that union 200 schemas each I can't expect the optimizer to estimate correctly! I had to use a CTE- a trick because PG materializes CTE intermediates (thank goodness!) to make it perform. I'm proud I wrangled the optimizer to my will and got the query down to < 3 seconds (on massive hardware). And, a sub-optimal mix of indexes were available. – quickdraw Sep 20 '19 at 21:53
  • Did you try increasing the 'fetch_size'? The default is pretty small. – jjanes Sep 21 '19 at 13:25
  • Didn't play with fetch size. Results are1800+ rows of modest length. That would be a whole other issue if transit time was the cause. – quickdraw Sep 23 '19 at 19:03

0 Answers0