Two things that changed in the most recent release of PostgreSQL are both associated with deferring certain actions to the remote server. In PostgreSQL 9.6, JOIN and ORDER BY operations are actually transmitted to the remote system, though there are some restrictions:
- Joined foreign tables exist on the same SERVER. In our case, this would be primary_db.
- The remotely joined tables must be distinct. Our third query example was a self-join which is unfortunately not supported by the pushdown logic.
- We don't want to sort and join at the same time.
Basically this means we could create pgbench_branches as a foreign table and joining it with pgbench_accounts would be done by the remote system. We could also sort ...