Method and apparatus for generating a remote subquery pushdown execution plan based on a database

Abstract

The application discloses a generation method and device for database-based remote subquery pushdown execution plan. In view of the defect that the existing postgres_fdw cannot participate in the upper query plan generation of the subquery path, resulting in the intermediate result being forced to pull back to the local, a new callback interface is introduced in the FDW framework, so that the FDW plug-in can intervene in the subquery planning in the path generation stage; the layered security check is used to ensure the correctness of the pushdown semantics; the FDW state is inherited based on the subquery path abstract information, and the remote scanning path is generated; the subquery is embedded into the upper remote SQL through remote SQL reverse analysis reconstruction; the JOIN path retry optimization is combined with the EPQ path compatibility check, and the path matching problem in different connection sequences and concurrent update scenarios is solved. The application realizes the overall pushdown of the subquery to the remote execution, significantly reduces the network transmission under the premise of guaranteeing the semantic correctness, and improves the cross-database federation query efficiency.

Description

Technical Field

[0001] This application belongs to the field of database operation technology, and specifically relates to a method, apparatus, computer-readable storage medium, and electronic device for generating remote subquery pushdown execution plans based on a database. Background Technology

[0002] With the deepening of enterprise informatization, the demand for data access between database systems is increasing. OpenGauss, as a next-generation enterprise-level relational database, enables transparent access across database instances through the postgres_fdw (Foreign Data Wrapper) plugin, allowing local databases to query data in remote databases as if operating on local tables. postgres_fdw has implemented query pushdown optimization in scenarios such as single-table scans, multi-table joins, and aggregation operations, pushing some or all of the query logic to the remote database for execution, thereby reducing the amount of data transmitted over the network and fully utilizing the computing resources of the remote database.

[0003] However, postgres_fdw has significant limitations in subquery processing. When an SQL query contains a subquery structure, even if the tables involved in the subquery are all located on the same remote server, the optimizer can only execute the subquery locally as an independent SubqueryScan node when generating the execution plan. It cannot include the corresponding ForeignScan path of the subquery in the plan generation of the upper-level query. This forces each level of subquery nesting to pull intermediate results back to local processing, resulting in extra data transfer between the client and the remote server, severely weakening the performance gains of query pushdown. In addition, in subquery scenarios involving UPDATE / DELETE operations, the EvalPlanQual (EPQ) re-checking mechanism needs to retain a valid local execution path, and the existing local JOIN path search logic does not support the processing of SubqueryScanPath, further increasing the implementation complexity of subquery pushdown.

[0004] To address the aforementioned issues, existing solutions attempt to pass the FDW external information of the subquery to the parent query by setting an intermediate interface (such as the SetSubqueryFpInfo callback) in the optimizer. However, this approach has semantic flaws and may lead to incorrect results in certain query scenarios. Furthermore, subquery pushdown also faces technical challenges such as remote SQL reverse parsing and reconstruction, and controlling the timing of half-join / anti-join path generation, which existing solutions have failed to systematically resolve. Therefore, how to achieve safe and efficient pushdown of subqueries by postgres_fdw without disrupting the optimizer's original path generation logic has become a critical issue that urgently needs to be addressed in cross-database federated query optimization. Summary of the Invention

[0005] To address the aforementioned issues, this application proposes a novel method and apparatus for generating remote subquery pushdown execution plans based on the (openGauss) database.

[0006] More specifically, this invention proposes a subquery pushdown mechanism based on FDW callback extension. By introducing the GetForeignSubqueryPaths callback interface during the optimizer path generation stage, combined with pushdown security checks, path cross-stage propagation, JOIN path retry optimization, and remote SQL reverse parsing reconstruction, postgres_fdw can push down the entire subquery to the remote database for execution, effectively improving the execution efficiency of federated queries.

[0007] To achieve the above objectives, the present invention employs the following key design features:

[0008] 1. FDW callback interface extension mechanism

[0009] In the openGauss FDW plugin framework, all core entry points for cross-database query optimization are implemented through function pointer callbacks defined in the FdwRoutine structure. This structure, located in src / include / foreign / fdwapi.h, is the core contract interface connecting the optimizer and the FDW plugin. In the existing callback interface system, GetForeignJoinPaths is responsible for generating JOIN pushdown paths, and GetForeignUpperPaths is responsible for generating upper-level relational paths such as aggregation / sorting. However, for RelOptInfo of subquery type, the optimizer calls subquery_planner() to generate SubPlan during the execution plan construction phase, preventing the FDW plugin from participating in the remote ForeignScanPath.

[0010] This invention adds a `GetForeignSubqueryPaths` callback function to `FdwRoutine`, defined as `GetForeignSubquery_function`, which accepts four parameters: the `PlannerInfo` of the current query, the `RelOptInfo` of the subquery in the parent query, the `fdw_finalrel` generated by FDW itself within the subquery, and the sorted path information of the subquery. By implementing this callback, the FDW plugin injects the `ForeignScanPath` corresponding to the subquery into the path set of the parent query before `set_subquery_path()` generates the `SubqueryScanPath` for the subquery, enabling the subquery to participate in the upper query path competition like a regular table.

[0011] 2. Subquery path generation and rollback mechanism

[0012] This mechanism is the scheduling core for subquery pushdown path generation. In the original logic, after `set_subquery_path()` constructs the subquery subplan, it directly calls `create_subqueryscan_path()` to generate `SubqueryScanPath` and adds it to the path list of the current relationship. This invention modifies this process, introducing the following stage processing logic:

[0013] (1) The system first determines whether the subquery is a pushdownable ForeignScan type;

[0014] (2) If so, read the fdw_finalrel (i.e., RelOptInfo generated by the FDW plugin for the subquery) stored in the ForeignScan node and check whether the GetForeignSubqueryPaths callback is registered;

[0015] (3) If it has been registered, the callback is called, the FDW plugin performs a pushdown check on the subquery, and generates a ForeignScanPath for the subquery that can be pushed down and adds it to rel->pathlist;

[0016] (4) After the callback returns, the system checks whether rel->pathlist is empty; if the callback successfully generates ForeignScanPath, the creation of SubqueryScanPath is skipped, and the subquery will participate in the subsequent JOIN and upper-level relationship path generation in the form of ForeignScan.

[0017] (5) If rel->pathlist is empty (the subquery cannot be pushed down), the system rolls back the original logic and generates a local SubqueryScanPath to ensure the correctness of the functional degradation.

[0018] The above mechanism ensures seamless compatibility between subquery pushdown and the original planning logic, and automatically rolls back when the subquery does not meet the pushdown conditions, without affecting the correctness of the query semantics.

[0019] Figure 3This is a flowchart illustrating the openGauss subquery path generation process in this invention, showing the overall flow of subquery path generation and pushdown decision-making. The system starts from the `set_subquery_path` entry point and generates a basic plan for the subquery using `subquery_planner`. If the subquery is managed by FDW, `GetForeignSubqueryPaths` is called to participate in path generation, and the cost and summary information of the remote `ForeignPath` are combined to attempt to construct a pushdownable `ForeignScan` path. If an external path is successfully generated, it is directly used for subsequent planning; otherwise, a local `SubqueryScanPath` is generated as a fallback execution path. The entire process embodies a "prioritize pushdown, fall back on failure" strategy, while simultaneously completing the construction of the remote SQL and the preparation of execution information during the path generation phase.

[0020] Figure 4 The flowchart for generating the remote execution path of the subquery pushdown in the present invention is shown in the figure. First, it is determined whether the subquery meets the remote execution conditions. If it does not meet the conditions, the generation of the remote path is abandoned directly. If it does meet the conditions, the FDW connection and authentication information of the subquery is inherited, the optimal path summary is extracted, the sorting path (pathkey) of the subquery is processed, and finally the corresponding ForeignPath is generated, so that the subquery can participate in the subsequent optimization as a remote path.

[0021] 3. Push-down security check mechanism

[0022] This mechanism is responsible for performing a comprehensive security check on the subquery before generating the pushdown path. The check is divided into the following levels:

[0023] (1) Command type constraint check: Only SELECT type query subqueries are allowed to be pushed down. For UPDATE / DELETE queries, since the EvalPlanQual (EPQ) re-checking mechanism requires that a valid local execution path be reserved for each join relationship involving updates, and GetExistingLocalJoinPath() currently does not support processing local paths of type SubqueryScanPath, if pushdown is forced, the EPQ phase will not be able to correctly rebuild the local execution context;

[0024] (2) Lateral reference check: Check if the lateral_relids of the subquery is empty. If the subquery references columns of the outer query (i.e., there is a lateral dependency), parameterized paths are required for correct pushdown, but the current mechanism does not support parameterized subquery pushdown;

[0025] (3) Remote expression security check: Iterate through each TargetEntry in the output column (targetlist) of the subquery ForeignScan, and call is_foreign_expr() one by one to verify whether the expression can be safely executed on the remote database. This function recursively checks the expression nodes to ensure that all function calls, operators, etc. can be pushed down;

[0026] (4) Condition expression security check: Perform the same remote security verification on all local constraints (qual) of the subquery to ensure that no unsafe conditions are missed;

[0027] (5) Local condition existence check: Check if fpinfo->local_conds is empty. If classifyConditions() has identified conditions that must be evaluated locally, the subquery as a whole is not eligible for pushdown.

[0028] 4. Remote SQL reverse parsing and reconstruction mechanism

[0029] This mechanism is the core support for the subquery pushdown execution phase. Traditionally, when constructing remote SQL, postgres_fdw directly generates the complete SELECT statement text for ordinary table scans, JOINs, and upper-level aggregations using deparseSelectStmtForRel(), and caches it in the fdw_private list of the ForeignScan node for the executor to use. However, for pushdown subqueries, the cached SQL is for execution and contains expressions that have been replaced with specific parameter values, so it cannot be used as an intermediate representation for constructing upper-level SQL. This invention extends deparseRangeTblRef() as follows:

[0030] (1) Add the IS_SUBQUERY_REL() macro, which identifies subquery relationships by checking whether the rtekind field of RelOptInfo is RTE_SUBQUERY;

[0031] (2) When encountering a subquery relationship, the cached SQL is no longer used. Instead, the sort key (pathkeys), final sort flag (has_final_sort) and LIMIT flag (has_limit) recorded in fdw_finalrel, targetlist and FdwBestPathInfo are extracted from the ForeignScan node of the subquery. The deparseSelectStmtForRel() function is then called to reconstruct the complete SELECT statement of the subquery.

[0032] (3) Wrap the reconstructed subquery SQL in parentheses and add the system alias SUBQUERY_REL_ALIAS_PREFIX ("s") to the subquery relation table alias. At the same time, generate column aliases based on SUBQUERY_COL_ALIAS_PREFIX ("c") for the output columns so that they can be referenced by the FROM and WHERE clauses of the upper-level query later.

[0033] (4) In column alias generation (deparseColumnRef), use the ADD_SUBQUERY_QUALIFIER() macro to generate qualified column names such as "s1.c1" for RTE_SUBQUERY type relations, replacing the original attribute names of the underlying table;

[0034] (5) In compatibility mode, the AS keyword alias is automatically added to the subquery and output columns to ensure that the generated SQL text is compatible with the remote database.

[0035] Figure 7 This is a flowchart of the subquery reverse parsing process in this invention. As shown, the overall process starts from the `deparseSelectStmtForRel` entry point, first constructing the SELECT framework and FROM / WHERE clauses. When the current relationship is identified as a subquery (IS_SUBQUERY_REL), a dedicated subquery processing branch is entered. By obtaining the subquery execution plan, the corresponding best path information, and the metadata recorded in `fdw_private`, the complete SELECT statement of the subquery is recursively reconstructed. Subsequently, depending on whether it is a higher-level relationship (such as GROUP BY / HAVING) and whether there are sorting requirements (pathkeys), the corresponding clauses (such as ORDER BY) are added.

[0036] 5. JOIN path retry optimization mechanism

[0037] The core problem this mechanism addresses is that during the JOIN enumeration process, a certain join relationship might be determined as non-pushdown in an earlier combination attempt (because one of its sub-relationships contains a local condition that prevents pushdown), causing its `fdw_private` to be marked as non-empty and `pushdown_safe = false`. According to the original logic, once `joinrel->fdw_private` is not empty, the system considers the join relationship to have been evaluated and will not retry it. However, when the same join relationship appears with different join orders or path combinations, its internal sub-relationships may have changed, and the new sub-relationship combination may satisfy all pushdown conditions. But due to the existence of the old mark, the system will incorrectly skip the pushdown evaluation of this join relationship. This invention introduces the `should_consider_foreign_join()` function in `postgresGetForeignJoinPaths()` to implement the following retry logic:

[0038] (1) If joinrel->fdw_private == NULL, it means that the join relationship has not been evaluated, and the pushdown path is allowed directly;

[0039] (2) If joinrel->fdw_private != NULL and pushdown_safe == true, it means that the connection has been successfully pushed down, and the repeated evaluation is skipped;

[0040] (3) If joinrel->fdw_private != NULL but pushdown_safe == false, then further check the pushdown_safe flags of the current outer and inner relations. If both are true, it means that a new, fully safe sub-relationship combination has appeared. At this time, remove the old fdw_private information, reset it to NULL, and allow the pushdown evaluation to be performed again.

[0041] (4) At the same time, the timing of calling foreign_join_ok() is moved from before the EPQ path search to after the path search, so that the result of condition classification (remote_conds vs local_conds) can be available before build_tlist_to_deparse() builds fdw_scan_tlist, thereby allowing epq_path_tlist_compatible() to perform validation based on the correct target list.

[0042] Figure 5This is a flowchart of the JOIN path retry optimization mechanism in the present invention. The new strategy introduces a retry mechanism: even if the relationship has been evaluated, as long as there is no available ForeignPath, the pushdown safety of its left and right child relationships will still be checked again; if a new combination is found that satisfies the conditions, the remote path will be regenerated.

[0043] 6. Enhanced EPQ path compatibility verification mechanism

[0044] EvalPlanQual (EPQ) is a row-level re-checking mechanism designed by openGauss to ensure the correctness of concurrent updates. When the outer query is UPDATE / DELETE and involves join operations, the system needs to reserve an equivalent local join path (EPQ path) for each ForeignScan to revalidate the update conditions after concurrent modifications are detected. Existing implementations search for available local paths from the pathlist of join relationships using GetExistingLocalJoinPath(), but this function's path matching is relatively coarse and does not verify whether the path can completely produce all the target columns required by the ForeignScan. This invention adds the epq_path_tlist_compatible() function, which takes the EPQ candidate path and the target list of the ForeignScan (fdw_scan_tlist) and performs the following column-by-column verification:

[0045] (1) Obtain the outer joinpath and inner joinpath of the candidate path, as well as their respective set of reliids;

[0046] (2) Iterate through each Var type TargetEntry in fdw_scan_tlist and use bms_is_member() to determine whether the Var belongs to the outer or inner layer relationship;

[0047] (3) Call path_outputs_var() to check if the corresponding subpath outputs the Var in its reltargetlist;

[0048] (4) If any Var cannot be covered by the corresponding path, the EPQ candidate path is determined to be incompatible, and the search continues for the next candidate.

[0049] The above mechanism avoids execution anomalies caused by missing target columns in the EPQ phase.

[0050] Figure 6This diagram illustrates the enhanced EPQ path compatibility verification mechanism in the present invention. It describes the optimized process of GetExistingLocalJoinPath. The old logic simply searched the path list for non-parameterized local JoinPaths (such as HashJoin, NestLoop, MergeJoin) and reused them directly without verifying their output capabilities. The new process adds target column verification: first, it constructs the required set of output columns (fdw_scan_tlist) based on the current joinrel; then, it traverses the candidate paths, checking whether their left and right subtrees can completely produce these columns; only paths that meet the output coverage requirements are reused.

[0051] Specifically, this application provides the following technical solutions:

[0052] The first aspect of this application provides a method for generating remote subquery pushdown execution plans based on a database, applicable to the FDW (Foreign Data Wrapper) framework of PostgreSQL or openGauss databases, such as... Figure 1 As shown, the method includes the following steps:

[0053] S1. Subquery Internal Planning: Independently plan the subqueries in the SQL query. If the external table involved in the subquery meets the pushdown condition, call the corresponding path of FDW to generate a callback and push down the operation inside the subquery to the remote server for execution.

[0054] S2, Subquery Path Generation: During the path generation phase, it is determined whether the optimal plan of the subquery is of type ForeignScan. If so, the registered GetForeignSubqueryPaths callback is called to perform pushdown checks by the FDW plugin and generate a ForeignScanPath to be added to the path list of the current relationship. If the callback successfully generates a path, the creation of the local SubqueryScanPath is skipped. If no path is generated, the local SubqueryScanPath is generated in reverse order.

[0055] S3. Pushdown security check: After receiving a subquery pushdown request, the FDW plugin performs a security verification on the subquery and only allows the pushdown path generation to continue if all checks pass.

[0056] S4, FDW State Inheritance and Remote Path Generation: Copy the remote server connection metadata from the final FDW relationship inside the subquery, and construct the remote scan path for the parent relationship and add it to the candidate path list based on the optimal path summary information in the ForeignScan node of the subquery.

[0057] S5. Remote SQL Reverse Parsing and Reconstruction: When a subquery is pushed down and participates in the upper-level query, the path information is extracted from the ForeignScan node of the subquery to reconstruct the complete SELECT statement of the subquery, and the reconstructed subquery SQL is embedded into the upper-level remote SQL.

[0058] S6. ForeignScan plan node construction: Identify the current relationship as a subquery type, classify the outer conditions for remote security, serialize the path summary and store it in the ForeignScan private data structure, integrate the remote SQL text and output column information, and construct the final ForeignScan node.

[0059] Furthermore, in the method of this application, the pushdown conditions in step S1 include: the external tables involved in the subquery belong to the same remote server, and the columns, operators and functions involved in the JOIN condition can be safely executed on the remote server;

[0060] The paths corresponding to the FDW include single table scan paths, JOIN paths, and upper-level relationship paths.

[0061] The callback-generated ForeignScan node exposes the final relationship information generated by FDW within the subquery, as well as the output target list of the subquery.

[0062] Furthermore, in the method of this application, in step S2, before calling the GetForeignSubqueryPaths callback, the parameters of the subquery are isolated to the current relation, and the internal path keys of the subquery are converted into a representation that can be recognized by the outer query.

[0063] Furthermore, in the method of this application, the security verification in step S3 includes: command type constraint check, lateral reference check, output list remote security check, constraint condition remote security check, and local condition existence check; wherein, the command type constraint check includes checking whether the command type of the outer query is SELECT, and if it is UPDATE or DELETE, it is determined that it cannot be pushed down; the lateral reference check includes checking whether the lateral_relids of the subquery is empty, and if there is a lateral dependency, it is determined that it cannot be pushed down; the output list remote security check includes traversing each TargetEntry in the output column of the ForeignScan of the subquery and calling is_foreign_expr() to verify whether the expression can be safely executed in the remote database; the local condition existence check includes checking whether the local_conds in the FDW relation information is empty, and if there is a condition that must be evaluated locally, it is determined that the entire subquery cannot be pushed down.

[0064] Furthermore, in the method of this application, the optimal path summary information in step S4 includes the startup cost, total cost, sorting path key, final sorting flag, and LIMIT flag.

[0065] Furthermore, in the method of this application, the remote SQL reverse parsing reconstruction in step S5 specifically includes: wrapping the reconstructed subquery SQL in parentheses, adding a system alias with the prefix SUBQUERY_REL_ALIAS_PREFIX to the subquery relation table, generating column aliases based on the prefix SUBQUERY_COL_ALIAS_PREFIX for the output columns, using the ADD_SUBQUERY_QUALIFIER() macro to generate qualified column names for RTE_SUBQUERY type relations, and automatically adding the AS keyword in compatibility mode.

[0066] Furthermore, the method of this application also includes:

[0067] JOIN path retry optimization steps: During the JOIN enumeration process, the `should_consider_foreign_join()` function is introduced. If the connection relationship has been evaluated as not pushdown but the current inner and outer sub-relationships are all marked as pushdown safe, the old evaluation result is removed and pushdown evaluation is allowed to be performed again. The timing of calling `foreign_join_ok()` is moved from before the EPQ path search to after the path search, so that the conditional classification results are available before the `fdw_scan_tlist` is constructed.

[0068] Enhanced EPQ path compatibility verification steps: Using the epq_path_tlist_compatible() function, iterate through the Var type TargetEntry in the ForeignScan target list and check whether the corresponding sub-path of the EPQ candidate path outputs the Var in its reltargetlist; if there is a Var that cannot be covered, then the EPQ candidate path is determined to be incompatible.

[0069] Furthermore, in the method of this application, after step S6, there is also a step of closing the upper-level relationship: recording the final output list to the FDW context, calling the FDW upper-level path callback, completing the expression to be executed late in the final output list, and applying the target list label to the ForeignScan output column.

[0070] The second aspect of this application provides an apparatus for generating a database-based remote subquery pushdown execution plan. This apparatus is applied to an FDW framework for PostgreSQL or openGauss databases and implements the steps of the aforementioned database-based remote subquery pushdown execution plan generation method at runtime, such as... Figure 2 As shown, the device includes:

[0071] The subquery planning module is used to independently plan subqueries in SQL queries. If the external table involved in the subquery meets the pushdown condition, the corresponding path of FDW is called to generate a callback, and the operation inside the subquery is pushed down to the remote server for execution.

[0072] The subquery path generation module is used during the path generation phase to determine whether the optimal plan of the subquery is of type ForeignScan. If it is, the registered GetForeignSubqueryPaths callback is called so that the FDW plugin can perform pushdown checks and generate a ForeignScanPath to be added to the path list of the current relationship. If the callback successfully generates a path, the creation of the local SubqueryScanPath is skipped. If no path is generated, the local SubqueryScanPath is generated in reverse order.

[0073] The pushdown security check module is used by the FDW plugin to perform security verification on the subquery after receiving the subquery pushdown request, and only allows the pushdown path generation to continue if all checks pass.

[0074] The FDW state inheritance and remote path generation module is used to copy remote server connection metadata from the final FDW relationship inside the subquery, and construct remote scan paths for the parent relationship and add them to the candidate path list based on the optimal path summary information in the ForeignScan node of the subquery.

[0075] The remote SQL reverse parsing and reconstruction module is used to extract path information from the ForeignScan node of the subquery and reconstruct the complete SELECT statement of the subquery when the subquery is pushed down and participates in the upper-level query, and embed the reconstructed subquery SQL into the upper-level remote SQL.

[0076] The ForeignScan plan node construction module is used to identify the current relationship as a subquery type, classify the external conditions for remote security, serialize the path summary and store it in the ForeignScan private data structure, integrate the remote SQL text and output column information, and construct the final ForeignScan node.

[0077] Furthermore, the device of this application also includes:

[0078] The JOIN path retry optimization module introduces the `should_consider_foreign_join()` function during the JOIN enumeration process. If the connection relationship has been evaluated as not pushdown but the current inner and outer sub-relationships are marked as pushdown safe, the old evaluation result is removed and pushdown evaluation is allowed to be performed again. The timing of calling `foreign_join_ok()` is moved from before the EPQ path search to after the path search, so that the condition classification results are available before the `fdw_scan_tlist` is constructed.

[0079] The EPQ path compatibility verification enhancement module is used to iterate through the Var type TargetEntry in the ForeignScan target list using the epq_path_tlist_compatible() function, and check whether the corresponding sub-path of the EPQ candidate path outputs the Var in its reltargetlist; if there is a Var that cannot be covered, the EPQ candidate path is determined to be incompatible.

[0080] A third aspect of this application provides an electronic device, including: a memory and a processor;

[0081] Memory: Used to store computer programs;

[0082] Processor: Used to execute the computer program to implement the steps of the aforementioned method for generating a database-based remote subquery pushdown execution plan.

[0083] A fourth aspect of this application provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the aforementioned method for generating a database-based remote subquery pushdown execution plan.

[0084] In summary, this invention fully reuses existing FDW frameworks and optimizer structures, without relying on intrusive modifications to core system tables or executors. It achieves functional closure solely through enhanced path generation processes and callback extensions. Simultaneously, EPQ compatibility checks and JOIN path retry mechanisms ensure correctness and stability in complex query and concurrent update scenarios. The overall design possesses good scalability and engineering controllability, improving cross-database federated query performance without violating existing optimizer behavior boundaries, thus demonstrating high practical value and potential for widespread adoption. Attached Figure Description

[0085] To more clearly illustrate the technical solution of this application, the accompanying drawings involved in the description of this invention will be briefly introduced below. It should be noted that the drawings only show some embodiments of the invention. For those skilled in the art, other related drawings can be derived from these drawings without creative effort.

[0086] Figure 1 This is a flowchart illustrating the overall implementation of the database-based remote subquery pushdown execution plan generation method of the present invention.

[0087] Figure 2 This is a structural diagram of the apparatus for generating remote subquery pushdown execution plans based on a database according to the present invention.

[0088] Figure 3 This is a flowchart of the openGauss subquery path generation process in the present invention.

[0089] Figure 4 This is a flowchart illustrating the process of generating the remote execution path for the subquery pushdown in the present invention.

[0090] Figure 5 This is a flowchart of the JOIN path retry optimization mechanism in the present invention.

[0091] Figure 6 This is a schematic diagram of the EPQ path compatibility verification enhancement mechanism in the present invention.

[0092] Figure 7 This is a flowchart of the subquery reverse parsing process in the present invention. Detailed Implementation

[0093] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. It should be understood that the described embodiments are only some embodiments of this application, and not all embodiments. All other embodiments obtained by those skilled in the art based on the embodiments of this application without creative effort are within the protection scope of this application.

[0094] In this document, the term "comprising" and any variations thereof (such as "including," "including," etc.) are open-ended expressions and should be understood as "including but not limited to," meaning that the listed content is not exhaustive and may include other content not explicitly mentioned. The term "based on" should be understood as "at least partially based on," meaning that the basis or condition referred to may not be the only factor and may involve other relevant factors. The term "one embodiment" should be understood as "at least one embodiment," meaning that the described embodiment is not the only possible implementation, and other similar embodiments may exist.

[0095] To clearly illustrate the technical solution of this application, the following will provide a detailed description through specific scenario embodiments.

[0096] There are two remote tables, ft1(id int, a int, b text) and ft2(id int, c int), located on the same remote server. A user submits the following query:

[0097] SELECT sub.a, sub.c

[0098] FROM (

[0099] SELECT ft1.a, ft2.c

[0100] FROM ft1 JOIN ft2 ON ft1.id = ft2.id

[0101] WHERE ft1.a > 10

[0102] ) sub

[0103] WHERE sub.c > 100;

[0104] Before implementing this solution, the optimizer could only push the inner JOIN to the remote end, but the SubqueryScan node of the subquery was executed locally, causing all remote JOIN results to be pulled back to the local machine before the outer filtering was performed. With this invention, the optimizer can push the entire subquery along with the outer filtering to the remote end, generating a single remote SQL query, and only returning the final result locally. The following example uses this query to fully describe the entire execution process of this method.

[0105] Step 1: Subquery Internal Planning

[0106] The system first performs independent planning for the subquery. During planning, the system identifies that ft1 and ft2 are both external tables on the same remote server, and that the columns, operators, and functions involved in the JOIN condition ft1.id = ft2.id can be safely executed on the remote server. The system calls the FDW's JOIN pushdown callback, determines that the JOIN can be pushed down and marks it as pushdown safe, and then generates a JOIN-type remote scan plan (ForeignScan) for the subquery, pushing down the JOIN operation and the WHERE filter condition ft1.a > 10 to the remote server.

[0107] SELECT ft1.a, ft2.c FROM ft1 JOIN ft2 ON ft1.id = ft2.id WHERE ft1.a> 10

[0108] After the subquery is planned, its optimal plan is recorded as a ForeignScan node. This node exposes two key pieces of information:

[0109] (1) The final relation generated by FDW within the subquery (which records the remote server identifier, user mapping, FDW callback function and condition push-down classification result);

[0110] (2) The output target list of the subquery (i.e., ft1.a and ft2.c).

[0111] Step 2: Subquery path generation

[0112] After the subquery plan is generated, the system enters the path generation phase. The system first isolates the parameters of the subquery into the current relation and converts the internal path keys of the subquery into a representation that can be recognized by the outer query.

[0113] The system then checks if the optimal plan for the subquery is of type ForeignScan. If so, it further extracts the FDW final relation of its internal records and checks if the relation has registered a subquery pushdown callback function. If the callback exists, the system invokes the callback, delegating the subquery pushdown determination and path generation work to the FDW plugin.

[0114] If the callback successfully generates a remote scan path for the current relationship, the system skips the creation of the local subquery scan path; if the callback fails to generate a path (i.e., the subquery does not meet the pushdown conditions), the system reverts to the original logic and generates a locally executed SubqueryScanPath. This dual-path mechanism of "trying to push down first and reverting if it fails" ensures complete compatibility between this solution and the original planning logic.

[0115] Step 3: Push-down security check

[0116] Upon receiving a subquery pushdown request, the FDW plugin first performs a comprehensive security check on the subquery to ensure that the pushdown will not produce semantic errors. The check is conducted at the following five levels:

[0117] The first layer, command type constraint: checks whether the command type of the outer query is SELECT. If it is UPDATE or DELETE, because the EvalPlanQual row-level re-checking mechanism requires that a valid local execution path be reserved for each relation involving updates, and the current local path search logic does not support processing subquery scan paths, this type of query does not have pushdown conditions.

[0118] The second layer involves local condition and safety flag checks: This checks if the subquery contains conditions that must be evaluated locally (e.g., calling a function defined only locally), and whether the subquery has been marked as safe to push down. If a local condition exists or the subquery is not marked as safe, it cannot be pushed down.

[0119] The third layer is the lateral reference check: This checks whether the subquery references columns from the outer query (i.e., lateral dependencies). If lateral references exist, parameterized paths are required for correct pushdown, but the current mechanism does not yet support this capability.

[0120] The fourth layer is the remote security check of the output list: Iterate through each expression in the subquery's output columns and verify whether the functions, operators, and data types involved have equivalent implementations in the remote database.

[0121] The fifth layer is the remote security check of the constraints: the same remote security verification is performed on the local constraints of the subquery to ensure that all conditions can be executed remotely.

[0122] In this example, the query type is SELECT, the subquery JOIN is marked as pushdown safe and has no local conditions, there are no lateral references, and all output columns and conditions can be executed remotely. All checks pass; proceed with path generation.

[0123] Step 4: FDW State Inheritance and Remote Path Generation

[0124] After the security check passes, the FDW plugin performs the following operations:

[0125] (1) State inheritance: Copy the remote server connection metadata (including server identifier, user mapping, FDW callback function, etc.) from the final FDW relation inside the subquery to create a new FDW relation information structure. At the same time, reuse the remote condition classification results that have been processed in the lower layer.

[0126] (2) Path information extraction: Extract the optimal path summary information from the ForeignScan node of the subquery, including the startup cost, total cost, sort path key, final sort flag, and LIMIT flag. This information will be used in the subsequent remote SQL construction phase to restore the sort and LIMIT clause.

[0127] (3) Remote path generation: Based on the above information, a remote scan path (ForeignPath) is constructed for the parent relation and added to the candidate path list. The cost of this path is directly inherited from the optimal path of the subquery.

[0128] At this point, the parent relation has obtained a remote scan path and can participate in subsequent JOINs and competition for upper-level relation paths just like a normal table.

[0129] Step 5: JOIN path retry determination

[0130] When the outer query involves JOIN operations on other tables, the system calls the FDW's JOIN path generation callback during the JOIN enumeration phase.

[0131] In the original logic, once a connection has been evaluated (whether successfully or not), it will not be retried. However, in practice, different connection orders may cause changes in the pushdown property of sub-relationships.

[0132] This scheme introduces a retry mechanism: when it is detected that both the inner and outer sub-relationships of the current connection have been marked as pushdown safe, the previous evaluation results are reset, allowing the pushdown judgment to be performed again. Simultaneously, the condition classification logic is moved forward, ensuring that the correct condition classification result is obtained before the EvalPlanQual path search.

[0133] In this example, the outer query contains only one subquery relationship and does not involve any additional JOINs; this step does not affect path generation.

[0134] Step Six: Finalizing Upper-Level Relationships

[0135] After the grouping_planner completes the planning of higher-level relationships such as sorting and LIMIT, the system executes the following processing flow:

[0136] (1) Final output list record: Record the output target list of the upper layer plan into the FDW context.

[0137] (2) Upper-layer path push: Call the FDW upper-layer path callback to determine whether a better remote execution plan is generated.

[0138] (3) Expression delay compensation: For expressions that are delayed in execution (such as those calculated after sorting), they are added to the final output list to ensure semantic consistency.

[0139] (4) Label application: Apply the target list labels to the ForeignScan output columns to ensure that the executor correctly identifies the column source.

[0140] Step 7: Constructing ForeignScan plan nodes

[0141] During the plan tree generation phase, the system calls the FDW's "Get Remote Plan" callback:

[0142] (1) Identify the current relation as a subquery type;

[0143] (2) Perform remote security classification on the outer layer WHERE conditions;

[0144] (3) Serialize the path digest and store it in the ForeignScan private data structure;

[0145] (4) Integrate remote SQL text, output columns, data size and path information to construct the final ForeignScan node.

[0146] Step 8: Reconstructing the Remote SQL Server

[0147] When a subquery is pushed down and participates in a higher-level JOIN, the subquery needs to be embedded in the remote SQL:

[0148] (1) Identify the subquery relation type;

[0149] (2) Reconstruct the subquery SELECT statement instead of using cached SQL;

[0150] (3) Recover ORDER BY and LIMIT based on the path summary;

[0151] (4) Use system-generated aliases (such as s1.c1) to unify column references;

[0152] (5) Automatically add AS keywords in compatibility mode.

[0153] After the above steps, the system finally generates the complete SQL to be sent to the remote server:

[0154] SELECT s3.c1, s3.c2

[0155] FROM (

[0156] SELECT ft1.a AS c1, ft2.c AS c2

[0157] FROM ft1 JOIN ft2 ON ft1.id = ft2.id

[0158] WHERE ft1.a > 10

[0159] AS s3

[0160] WHERE s3.c2 > 100;

[0161] After the remote server executes the SQL, it completes the JOIN, subquery filtering, and outer filtering locally, returning only the results that satisfy a > 10 AND c > 100 to the client. There is no need to transmit intermediate JOIN results to the local machine, thus significantly reducing network traffic and improving overall query performance.

[0162] The above describes the complete execution flow of the enhanced subquery pushdown function of postgres_fdw implemented based on the openGauss database in this invention. This function extends the FDW callback interface and optimizer path generation mechanism, enabling subqueries to participate in remote path planning and cost comparison. While ensuring semantic correctness, it pushes the entire subquery down to the remote execution stage. By introducing subquery path generation callbacks during the planning phase, reconstructing the remote SQL during the execution phase, and combining strict security checks and rollback mechanisms, it solves the problem of existing postgres_fdw failing to push down in subquery scenarios, leading to intermediate result pullback.

[0163] The flowcharts and block diagrams in the accompanying drawings illustrate possible implementations of apparatus, methods, and computer program products according to various embodiments of this application, including architecture, functionality, and operation. In these figures, each block may represent a module, program segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should be noted that each block in the block diagrams and / or flowcharts, and combinations thereof, can be implemented using either a dedicated hardware-based system or a combination of dedicated hardware and computer instructions to achieve the specified function or operation.

[0164] Embodiments of this application also disclose an electronic device, including: a processor, a communication interface, a memory for storing a processor-executable computer program, and a communication bus. The processor, communication interface, and memory communicate with each other via the communication bus. The processor executes the executable computer program to implement the steps of the above-described method for generating a database-based remote subquery pushdown execution plan.

[0165] It is understood that, in addition to memory and a processor, this electronic device may also include input devices (such as a keyboard), output devices (such as a display), and other communication modules. These input devices, output devices, and other communication modules all communicate with the processor through I / O interfaces (i.e., input / output interfaces).

[0166] The operations described in this application can be implemented by writing computer program code using one or more programming languages ​​or a combination thereof. The programming languages ​​include, but are not limited to, the following types:

[0167] Object-oriented programming languages, such as Java, Smalltalk, C++, etc.

[0168] Conventional procedural programming languages, such as "C" or similar programming languages.

[0169] The execution methods of program code include, but are not limited to:

[0170] It runs entirely on the user's computer;

[0171] Part of it executes on the user's computer, and part of it executes on a remote computer;

[0172] Execute as a standalone software package;

[0173] It is executed entirely on a remote computer or server.

[0174] In scenarios involving remote computers, the remote computer can connect to the user's computer via any type of network, including but not limited to local area networks (LANs) or wide area networks (WANs). Furthermore, the remote computer can also connect to external computers through an internet service provider, for example, by utilizing the internet for connection.

[0175] Furthermore, this application also discloses a computer-readable storage medium, wherein when the instructions in the computer-readable storage medium are executed by a processor of an electronic device, the electronic device is able to perform the various steps of the database-based remote subquery pushdown execution plan generation method disclosed in this application.

[0176] In the context of this application, a computer-readable storage medium refers to a tangible medium capable of storing computer program code and related data. Such computer-readable storage media can be used to store the program code and related data described in this application to support program execution and persistent data storage.

[0177] Specifically, according to embodiments of this application, the processes described in the flowcharts can be implemented as computer software programs. For example, embodiments of this application relate to a computer program product comprising a computer program carried on a non-transitory computer-readable medium. This computer program includes program code for executing the database-based remote subquery pushdown execution plan generation method disclosed in this application. When the computer program is executed by a processing device, it can achieve the functions defined in the embodiments of this application.

[0178] While the foregoing discussion contains several specific implementation details, these details should not be construed as limiting the scope of this application. The above description is merely a preferred embodiment of this application and an explanation of the technical principles employed. Those skilled in the art should understand that the scope of this application is not limited to technical solutions formed by specific combinations of the above-described technical features. Furthermore, this application should also cover other technical solutions formed by any combination of the above-described technical features or their equivalents without departing from the foregoing disclosed concept.

[0179] Those skilled in the art should also understand that modifications can be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features, without departing from the spirit and scope of the technical solutions of the embodiments of this application. These modifications or substitutions will not cause the essence of the corresponding technical solutions to deviate from the core spirit and scope of the technical solutions of the embodiments of this application.

Claims

1. A method for generating a remote subquery pushdown execution plan based on a database, characterized in that, An FDW framework for PostgreSQL or openGauss databases includes the following steps: S1. Subquery Internal Planning: Independently plan the subqueries in the SQL query. If the external table involved in the subquery meets the pushdown condition, call the corresponding path of FDW to generate a callback and push down the operation inside the subquery to the remote server for execution. S2, Subquery Path Generation: During the path generation phase, it is determined whether the optimal plan of the subquery is of type ForeignScan. If so, the registered GetForeignSubqueryPaths callback is called to perform pushdown checks by the FDW plugin and generate a ForeignScanPath to be added to the path list of the current relationship. If the callback successfully generates a path, the creation of the local SubqueryScanPath is skipped. If no path is generated, the local SubqueryScanPath is generated in reverse order. S3. Pushdown security check: After receiving a subquery pushdown request, the FDW plugin performs a security verification on the subquery and only allows the pushdown path generation to continue if all checks pass. S4, FDW State Inheritance and Remote Path Generation: Copy the remote server connection metadata from the final FDW relationship inside the subquery, and construct the remote scan path for the parent relationship and add it to the candidate path list based on the optimal path summary information in the ForeignScan node of the subquery. S5. Remote SQL Reverse Parsing and Reconstruction: When a subquery is pushed down and participates in the upper-level query, the path information is extracted from the ForeignScan node of the subquery to reconstruct the complete SELECT statement of the subquery, and the reconstructed subquery SQL is embedded into the upper-level remote SQL. S6. ForeignScan plan node construction: Identify the current relationship as a subquery type, classify the outer conditions for remote security, serialize the path summary and store it in the ForeignScan private data structure, integrate the remote SQL text and output column information, and construct the final ForeignScan node.

2. The method according to claim 1, characterized in that, The pushdown conditions mentioned in step S1 include: the external tables involved in the subquery belong to the same remote server, and the columns, operators and functions involved in the JOIN condition can be safely executed on the remote server; The paths corresponding to the FDW include single table scan paths, JOIN paths, and upper-level relationship paths. The callback-generated ForeignScan node exposes the final relationship information generated by FDW within the subquery, as well as the output target list of the subquery.

3. The method according to claim 1, characterized in that, In step S2, before calling the GetForeignSubqueryPaths callback, the parameters of the subquery are isolated to the current relation, and the internal path keys of the subquery are converted into a representation that the outer query can recognize.

4. The method according to claim 1, characterized in that, The security verification in step S3 includes: command type constraint check, lateral reference check, output list remote security check, constraint condition remote security check, and local condition existence check. Specifically, the command type constraint check checks whether the command type of the outer query is SELECT; if it is UPDATE or DELETE, it is determined that pushdown is not possible. The lateral reference check checks whether the subquery's lateral_relids is empty; if a lateral dependency exists, it is determined that pushdown is not possible. The output list remote security check iterates through each TargetEntry in the subquery's ForeignScan output column and calls is_foreign_expr() to verify whether the expression can be safely executed on the remote database. The local condition existence check checks whether the FDW relation information's local_conds is empty; if a condition exists that must be evaluated locally, the entire subquery is determined that pushdown is not possible.

5. The method according to claim 1, characterized in that, The optimal path summary information mentioned in step S4 includes the startup cost, total cost, sorting path key, final sorting flag, and LIMIT flag.

6. The method according to claim 1, characterized in that, The remote SQL reverse parsing reconstruction described in step S5 specifically includes: wrapping the reconstructed subquery SQL in parentheses, adding a system alias with the prefix SUBQUERY_REL_ALIAS_PREFIX to the subquery relation table, generating column aliases based on the prefix SUBQUERY_COL_ALIAS_PREFIX for the output columns, using the ADD_SUBQUERY_QUALIFIER() macro to generate qualified column names for RTE_SUBQUERY type relations, and automatically adding the AS keyword in compatibility mode.

7. The method according to claim 1, characterized in that, The method also includes: JOIN path retry optimization steps: During the JOIN enumeration process, the `should_consider_foreign_join()` function is introduced. If the connection relationship has been evaluated as not pushdown but the current inner and outer sub-relationships are all marked as pushdown safe, the old evaluation result is removed and pushdown evaluation is allowed to be performed again. The timing of calling `foreign_join_ok()` is moved from before the EPQ path search to after the path search, so that the conditional classification results are available before the `fdw_scan_tlist` is constructed. Enhanced EPQ path compatibility verification steps: Using the epq_path_tlist_compatible() function, iterate through the Var type TargetEntry in the ForeignScan target list and check whether the corresponding sub-path of the EPQ candidate path outputs the Var in its reltargetlist; if there is a Var that cannot be covered, then the EPQ candidate path is determined to be incompatible.

8. The method according to claim 1, characterized in that, The method step S6 is followed by a step to close the upper-level relationship: record the final output list to the FDW context, call the FDW upper-level path callback, complete the expression to be executed late in the final output list, and apply the target list label to the ForeignScan output column.

9. A device for generating remote subquery pushdown execution plans based on a database, characterized in that, The apparatus is applied to the FDW framework of PostgreSQL or openGauss database, and at runtime implements the steps of the database-based remote subquery pushdown execution plan generation method as described in any one of claims 1-8, including: The subquery planning module is used to independently plan subqueries in SQL queries. If the external table involved in the subquery meets the pushdown condition, the corresponding path of FDW is called to generate a callback, and the operation inside the subquery is pushed down to the remote server for execution. The subquery path generation module is used during the path generation phase to determine whether the optimal plan of the subquery is of type ForeignScan. If it is, the registered GetForeignSubqueryPaths callback is called so that the FDW plugin can perform pushdown checks and generate a ForeignScanPath to be added to the path list of the current relationship. If the callback successfully generates a path, the creation of the local SubqueryScanPath is skipped. If no path is generated, the local SubqueryScanPath is generated in reverse order. The pushdown security check module is used by the FDW plugin to perform security verification on the subquery after receiving the subquery pushdown request, and only allows the pushdown path generation to continue if all checks pass. The FDW state inheritance and remote path generation module is used to copy remote server connection metadata from the final FDW relationship inside the subquery, and construct remote scan paths for the parent relationship and add them to the candidate path list based on the optimal path summary information in the ForeignScan node of the subquery. The remote SQL reverse parsing and reconstruction module is used to extract path information from the ForeignScan node of the subquery and reconstruct the complete SELECT statement of the subquery when the subquery is pushed down and participates in the upper-level query, and embed the reconstructed subquery SQL into the upper-level remote SQL. The ForeignScan plan node construction module is used to identify the current relationship as a subquery type, classify the external conditions for remote security, serialize the path summary and store it in the ForeignScan private data structure, integrate the remote SQL text and output column information, and construct the final ForeignScan node.

10. The apparatus according to claim 9, characterized in that, The device also includes: The JOIN path retry optimization module introduces the `should_consider_foreign_join()` function during the JOIN enumeration process. If the connection relationship has been evaluated as not pushdown but the current inner and outer sub-relationships are marked as pushdown safe, the old evaluation result is removed and pushdown evaluation is allowed to be performed again. The timing of calling `foreign_join_ok()` is moved from before the EPQ path search to after the path search, so that the condition classification results are available before the `fdw_scan_tlist` is constructed. The EPQ path compatibility verification enhancement module is used to iterate through the Var type TargetEntry in the ForeignScan target list using the epq_path_tlist_compatible() function, and check whether the corresponding sub-path of the EPQ candidate path outputs the Var in its reltargetlist; if there is a Var that cannot be covered, the EPQ candidate path is determined to be incompatible.

Images