0

After an update of Postgres from 9.6 to 14, accessing foreign tables in queries has become dramatically slower. The creation of materialized views based on foreign tables for example, may not terminate in 24 hours whereas it used to take a few minutes on 9.6.

I create foreign data wrapper like this:

CREATE SERVER myserver
                FOREIGN DATA WRAPPER postgres_fdw
                OPTIONS (dbname 'mydb', host 'myhost', port '5432', fetch_size '100000', async_capable 'true');

and foreign tables like this:

IMPORT FOREIGN SCHEMA myforeignschema 
                    FROM SERVER myserver INTO mylocalschema;

Is there anything else I can do to increase speed or to detect the cause of the slowdown?

Lokomotywa
  • 2,624
  • 8
  • 44
  • 73
  • Just brainstorming: it is on the same server or new server? Is the network layout the same? – user_0 Jul 02 '22 at 12:08
  • yes, nothing has changed besides the postgres version – Lokomotywa Jul 02 '22 at 12:22
  • 1) How did you update from 9.6 to 14? 2) Show the `CREATE FOREIGN TABLE` definition 3) Show the `CREATE MATERIALIZED VIEW` definition. 4) Look at the Postgres logs when `CREATE MATERIALIZED VIEW` is run and report any relevant messages. 5) Have you run `ANALYZE` on the foreign table(s) and/or the database as a whole? Add answers as update to question. – Adrian Klaver Jul 02 '22 at 16:54
  • 1
    Unrelated, but there's a serious bug in 14.0. You should be using 14.4. – Jeremy Jul 03 '22 at 15:43
  • Can you provide a link? – Lokomotywa Jul 03 '22 at 16:27

0 Answers0