CODEWITHSUNDEEP
postgresqlperformanceoracle-fdw
Landon Statis
Landon Statis

Reputation: 839

Postgres foreign table performance

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.

Upvotes: 1

Views: 1687

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246838

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.

Upvotes: 2

Related Questions