0

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.

Explain

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?

Improve this question
4
  • Run explain(analyze, verbose, buffers) on the remote server, for that part that is handled in this database
    – Frank Heikens
    Commented Oct 26, 2023 at 14:05
  • @FrankHeikens so I take the sql (SELECT * FROM foreign_table_a) and run the explain on the foreign server with that? And that will give me an accurate representation of what is happening? What happens if I have a condition at the end such as (where foreign_table_a.grade > 80). Would that be filtering the foreign view first and then joining?
    – TreeWater
    Commented Oct 26, 2023 at 14:10
  • 1
    Also, would running ANALYZE foreign_view_name help out at all in this case then?
    – TreeWater
    Commented Oct 26, 2023 at 14:11
  • The capabilities of the foreign data wrapper also depend on the server version that you use. See the details for your version in the manual: postgresql.org/docs/current/postgres-fdw.html
    – Frank Heikens
    Commented Oct 26, 2023 at 14:20

1 Answer 1

Reset to default
0

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.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.