How to get explain for a foreign table

Question

I have a query that is essentially

SELECT *
FROM foreign_table_a
LEFT JOIN local_table_b on foreign_table_a.id = local_table_b.id

I'm looking at the explain analyse in datagrip and I don't really get any information other than I spend most of my time in the foreign scan.

How do I figure out what is going on in the foreign table? Do I need to parse out the sql that is running that foreign scan and run an explain on the foreign server?

I'm also not sure why there is a nested loop being used for this join. Everything being compared has an index so you would think it would be more effecient, right?

Also, the foreign table is actually a view. Is it possible to collect statistics on that foreign view using ANALYZE?

Answer 1

I assume that you mean postgres_fdw.

You can see the remote query with EXPLAIN (VERBOSE) SELECT .... If it is still unclear why that takes so long, take that remote query and run it with EXPLAIN (ANALYZE, BUFFERS, SETTINGS) directly on the remote server.