Creating a Query Tree, Connecting to the Remote Server, and Plan Tree Creation in FDW

1. Creating a Query Tree:

During query analysis, the analyzer/analyser constructs the query tree based on the input SQL statement. To build the query tree for FDW, the definitions of the foreign tables are utilized. These definitions are stored in the pg_catalog.pg_class and pg_catalog.pg_foreign_table catalogs, which are populated using commands like CREATE FOREIGN TABLE or IMPORT FOREIGN SCHEMA.

2. Connecting to the Remote Server:

To establish a connection with the remote server, the planner (or executor) relies on specific libraries designed for connecting to the respective database servers. For instance, when connecting to a remote PostgreSQL server, the postgres_fdw extension utilizes the libpq library. On the other hand, mysql_fdw, developed by EnterpriseDB, employs the libmysqlclient library for connecting to MySQL servers. The connection parameters, including the username, server's IP address, and port number, are stored in the pg_catalog.pg_user_mapping and pg_catalog.pg_foreign_server catalogs using commands like CREATE USER MAPPING and CREATE SERVER.

3. Creating a Plan Tree Using EXPLAIN Commands (Optional):

PostgreSQL's FDW provides support for obtaining statistics of foreign tables, which are utilized to estimate the plan tree of a query. Some FDW extensions, such as postgres_fdw, mysql_fdw, tds_fdw, and jdbc2_fdw, make use of this feature. If the use_remote_estimate option is enabled for a server using the ALTER SERVER command, the planner can query the cost of plans from the remote server by executing the EXPLAIN command. By default, embedded constant values are used for estimation. Here's an example of enabling the use_remote_estimate option:

ALTER SERVER remote_server_name OPTIONS (use_remote_estimate 'on');

It's important to note that while some FDW extensions can reflect the results of the EXPLAIN command (such as postgres_fdw), other extensions may not be able to utilize the EXPLAIN results for planning. For instance, the EXPLAIN command in MySQL only returns the estimated number of rows, which may not provide enough information for PostgreSQL's planner to estimate the cost accurately, as explained in Chapter 3.

When using FDW in PostgreSQL, the process involves creating a query tree, establishing a connection to the remote server using specific libraries, and optionally utilizing EXPLAIN commands to estimate the plan tree of a query. These steps allow PostgreSQL to seamlessly interact with foreign tables on remote servers, expanding the capabilities and flexibility of your database environment.