The wrong (?) query plan for queries with remote (postgres_fdw) tables
I have an inefficient query execution for queries with postgres_fdw.
I have an ineffective query with remote tables (postgres_fdw) that works for about 1 second. The same query with local tables (with the same data) instead of foreign ones has execution time less than 5 ms. So, the difference is almost 200 times.
So, the query works with 3 tables. 2 of them are remote, 1 is a local one.
Remote db (works on the same Postgres instance):
The idea that local_table and foreign_table have the same structure. They have 2 columns: primary key (primary_uuid) and foreign key (fkey_uuid).
Also, we have foreign_filter_table, that is a master table for 2 tables mentioned above. In addition to the primary key (primary_uuid), it also has a column filter_uuid.
What is the aim of a query: to filter the master table by filter_uuid, and then select corresponding data from unioned (union all) local_table and foreign_table, and make pagination sorted by the foreign key.
The master table (foreign_filter_table) contains 100K records. Slave tables contain about 100K records each, where they refer to about 5% of master table (each slave table contains 20 rows for 5% of master table rows).
Note, use_remote_estimate is true for the foreign server.
Logically, query execution should be: select rows from the master query and make merge join between them. It works this way when I use local tables instead of remote ones.
But with foreign tables it union child tables first, and then make a nested loop for each row with the mater table. As a result, the query becomes very slow...
So, the query is:
(select * from local_table lt
select * from foreign_server.foreign_table ft) a
join foreign_server.foreign_filter_table on a.fkey_uuid = foreign_server.foreign_filter_table.primary_uuid
where foreign_server.foreign_filter_table.filter_uuid between '56c77b02-8309-42f1-ae02-8d6922ea7dba' and '67c77b02-8309-42f1-ae02-8d6922ea7dba'