Postgres foreign table performance

Question

We have a query that uses both local tables and 1 foreign table, which is a materialized view in Oracle:

SELECT 
    ROW_NUMBER() OVER () AS sequence, 
    a.ticker, m.company_name, a.alert_trigger, d.display_rec, z.analyst_email, z.senior_email,
    s.model_update_approved, s.estimates_update_approved, s.model_released_approved,
    s.update_initiated_by_zer_analyst, s.model_update_initiated_by_rdss_analyst
FROM 
    alert_triggers a, zer_ticker_data z, display_recommendations d, uber_master_mv m,
    zer_model_alert_status s
WHERE 
    a.ticker = z.ticker
    AND a.ticker = d.ticker
    AND a.ticker = m.ticker
    AND a.ticker = s.ticker
    AND alert_trigger <> 'N/A'
ORDER BY 
    a.ticker;

The query is a bit slow, and I'm guessing because of the foreign table: uber_master_mv. The query returns 455 rows, and takes about 120 seconds.

Does Postgres have any tuning hints or anything I may be able to do to speed up the query?

Any suggestions would be great.

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**30 years!!** ago) and its use is discouraged — marc_s, May 26 '22 at 19:18
Sorry for the ignorant question, I'm a bit new at this with Postgres. But running explain analyze SELECT * ......... gave me a plan of 42 rows. Not sure I can post that here with it being so large. I've never with EXPLAIN in Postgres. — Landon Statis, May 26 '22 at 19:49
Most of the time is spent on the foreign table uber_master_mv. Can you us the definition of that table, including indexes, etc. — Frank Heikens, May 26 '22 at 21:40
According to that plan, your query takes slightly over 1.2s, no way near 120s. But we can't tell if you misread the timer and it is always that fast, or if it just faster now because everything is in memory, or if maybe the time in the real query is spent sending results over the network (between PostgreSQL DB and PostgreSQL client) which does not need to be done when just the plan result is sent. The last seems unlikely give the low number of rows produced. — jjanes, May 27 '22 at 00:22
That foreign table, uber_master_mv, is a materialized view in an Oracle DB. And the column it is joining, ticker, there is an index on it. Is fdw just very slow? — Landon Statis, May 27 '22 at 00:23
It is 100 times faster than you seem to think it is. But still slower than having the data stored locally. — jjanes, May 27 '22 at 14:39

score 2 · Answer 1 · answered May 27 '22 at 06:43

Your estimates for the foreign table scan are off, because you never collected statistics on the foreign table:

ANALYZE uber_master_mv;

However, I don't think that the plan would be much different: a nested loop join with the foreign table on the inner side would not be attractive, and less so, since the Oracle table is tiny.

The biggest surprise is that it takes 866 milliseconds to read 32529 rows from the Oracle table. Is that the network latency? Reduce that time, and you query will become faster.

Postgres foreign table performance

1 Answers1