CODEWITHSUNDEEP
postgresqlforeign-data-wrapper
gogofan
gogofan

Reputation: 563

Joining Tables between Multiple Foreign Servers with Foreign Data Wrapper Causes Performance Issue

One of my legacy PHP applications is using a PostgreSQL database with Foreign Data Wrapper. This database has a local table and two foreign servers set up (one pointing to database A, another pointing to database B).

The application uses ORM to construct SQL queries. One of the complex queries is actually joining 6 tables across the two foreign servers and also the local table. And the query just hangs forever because the those 6 tables have on average millions of records.

There are many more queries like this in the legacy app. I have configured the foreign servers to use_remote_estimate 'true' and increase the fetch_size but still see no drastic improvements.

I'm wondering if there are some configurations that can be done on the foreign server to optimise the query speed. Before I start rewriting the whole application to not use PHP and ORM.

Upvotes: 0

Views: 1248

Answers (1)

jjanes
jjanes

Reputation: 44285

Selectivity estimation problems in FDW are very common, and can lead to plans with atrocious performance. Since you are looking for magic bullet, have you tried running ANALYZE on the foreign tables in the local server, so it can use local statistics to some come up with plans? You might want to set up a clone to test this in. ANALYZE can also make things worse, and there is no easy way to undo it once done.

Another step might be setting cursor_tuple_fraction to 1 (or at least much higher than the defaults) on the servers on the foreign sides. This could help if the overall query plan is sound on the local side, but the execution on the foreign sides is bad.

Barring those, you need to look at EXPLAIN (VERBOSE) and EXPLAIN (ANALYZE) of an archetypical bad query to figure out what is going on.

Before I start rewriting the whole application to not use PHP and ORM.

Why would that help? Do you already know how rewrite the queries to make them faster, you just can't get the ORM to cooperate?

Upvotes: 2

Related Questions