Optimizing foreign table access
If you read the end of the previous recipe, you might assume we don't recommend that you use foreign tables at all. However, we would like to reassure you that foreign tables are not all doom and gloom. To prove it, we're going to use a disarmingly simple technique to optimize them: views.
It's true that PostgreSQL foreign data wrappers cannot combine queries for multiple tables on the same server. Provided we have access to the remote server, we can rectify this situation by creating a view to encapsulate the core of the query we want to perform. We can do this because PostgreSQL only knows the name of remote objects, not their composition. We can take advantage of this and use views to force remote joins.
In this recipe, we will describe how to use a remote view in place of a foreign table.
Getting ready
As we will be using the pgbench_accounts foreign table in this recipe, please follow all the previous recipes before proceeding.
How to do it...
For this recipe...
