Hi
I have postgres_fdw table called tbl_link. The source table is 2.5 GB in size with 122 lines (some lines has 70MB bytea column, but not the ones I select in the example)
I noticed that when I put the specific ids in the list "where id in (140,144,148)" it works fast (few ms), but when I put the same list as select "where id in (select 140 as id union select 144 union select 148)" it takes 50 seconds. This select union is just for the example, I obviously have a different select (which by itself takes few ms but cause the whole insert query to take 10000x more time)
Why is that? How can I still use regular select and still get reasonable response time?
Thanks
couple of things:
when you set your foreign server what are your
use_remote_estimate
fetch_size
params for the foreign server.
you need to know there are certain restrictions on what gets pushed down to the remote server
if you predicates are not pushed down, it will bring all the rows from the foreign server to your local server (and fetch_size value and network io will add to delay)
and given you used select * , it will be a lot of io, so maybe restrict only to columns needed after being filtered would help.
you can try by running
explain (verbose,analyze) query and then also enabling log_statement = 'all' / log_min_duration_statement = 0
on the foreign server to see the actual plan for the foreign scan.
That might help in trouble shooting.
as always, i have little production exposure. If i am wrong, i can be corrected.