如何让PostgreSQL查询规划器将主查询WHERE条件下推至子查询？

API 探路者

2026-6-28

问题描述

我正在使用Postgres 14.x，现有如下查询：

select *
from (select * from public.select_version_of_projects('2024-03-08T08:31:08.280Z')) as "project_version"
where "project_version"."id" = 'fd18211b-a400-49ed-a723-9648ab05ca4f';

自定义函数select_version_of_projects用于根据指定时间戳返回对应状态的project表数据，函数定义如下：

CREATE OR REPLACE FUNCTION public.select_version_of_projects(version_p TIMESTAMP WITH TIME ZONE)
    RETURNS TABLE
            (
                id             UUID,
                folder_id      UUID,
                name           TEXT,
                description    TEXT,
                picture        TEXT,
                files          JSON,
                keywords       TSVECTOR,
                scene          JSON,
                view           JSON,
                credits        JSON,
                published      JSON,
                imports        JSON,
                settings       JSON,
                studio_version TEXT,
                readonly       BOOLEAN,
                deleted        BOOLEAN,
                created_at     TIMESTAMP WITH TIME ZONE,
                updated_at     TIMESTAMP WITH TIME ZONE
            )
AS
$$
    SELECT  COALESCE(past.id, present.id)                         AS id,
            COALESCE(past.folder_id, present.folder_id)           AS folder_id,
            COALESCE(past.name, present.name)                     AS name,
            COALESCE(past.description, present.description)       AS description,
            COALESCE(past.picture, present.picture)               AS picture,
            COALESCE(past.files, present.files)                   AS files,
            COALESCE(past.keywords, present.keywords)             AS keywords,
            COALESCE(past.scene, present.scene)                   AS scene,
            COALESCE(past.view, present.view)                     AS view,
            COALESCE(past.credits, present.credits)               AS credits,
            COALESCE(past.published, present.published)           AS published,
            COALESCE(past.imports, present.imports)               AS imports,
            COALESCE(past.settings, present.settings)             AS settings,
            COALESCE(past.studio_version, present.studio_version) AS studio_version,
            COALESCE(past.readonly, present.readonly)             AS readonly,
            COALESCE(past.deleted, present.deleted)               AS deleted,
            COALESCE(past.created_at, present.created_at)         AS created_at,
            COALESCE(past.updated_at, present.updated_at)         AS updated_at
    FROM (SELECT *
          FROM project) AS present
    FULL OUTER JOIN
          (SELECT history.project.id,
                  (ARRAY_AGG(history.project.folder_id ORDER BY history.project.recorded_at) FILTER (WHERE history.project.folder_id IS NOT NULL))[1]           AS folder_id,
                  (ARRAY_AGG(history.project.name ORDER BY history.project.recorded_at) FILTER (WHERE history.project.name IS NOT NULL))[1]                     AS name,
                  (ARRAY_AGG(history.project.description ORDER BY history.project.recorded_at) FILTER (WHERE history.project.description IS NOT NULL))[1]       AS description,
                  (ARRAY_AGG(history.project.picture ORDER BY history.project.recorded_at) FILTER (WHERE history.project.picture IS NOT NULL))[1]               AS picture,
                  (ARRAY_AGG(history.project.files ORDER BY history.project.recorded_at) FILTER (WHERE history.project.files IS NOT NULL))[1]                   AS files,
                  (ARRAY_AGG(history.project.keywords ORDER BY history.project.recorded_at) FILTER (WHERE history.project.keywords IS NOT NULL))[1]             AS keywords,
                  (ARRAY_AGG(history.project.scene ORDER BY history.project.recorded_at) FILTER (WHERE history.project.scene IS NOT NULL))[1]                   AS scene,
                  (ARRAY_AGG(history.project.view ORDER BY history.project.recorded_at) FILTER (WHERE history.project.view IS NOT NULL))[1]                     AS view,
                  (ARRAY_AGG(history.project.credits ORDER BY history.project.recorded_at) FILTER (WHERE history.project.credits IS NOT NULL))[1]               AS credits,
                  (ARRAY_AGG(history.project.published ORDER BY history.project.recorded_at) FILTER (WHERE history.project.published IS NOT NULL))[1]           AS published,
                  (ARRAY_AGG(history.project.imports ORDER BY history.project.recorded_at) FILTER (WHERE history.project.imports IS NOT NULL))[1]               AS imports,
                  (ARRAY_AGG(history.project.settings ORDER BY history.project.recorded_at) FILTER (WHERE history.project.settings IS NOT NULL))[1]             AS settings,
                  (ARRAY_AGG(history.project.studio_version ORDER BY history.project.recorded_at) FILTER (WHERE history.project.studio_version IS NOT NULL))[1] AS studio_version,
                  (ARRAY_AGG(history.project.readonly ORDER BY history.project.recorded_at) FILTER (WHERE history.project.readonly IS NOT NULL))[1]             AS readonly,
                  (ARRAY_AGG(history.project.deleted ORDER BY history.project.recorded_at) FILTER (WHERE history.project.deleted IS NOT NULL))[1]               AS deleted,
                  (ARRAY_AGG(history.project.created_at ORDER BY history.project.recorded_at) FILTER (WHERE history.project.created_at IS NOT NULL))[1]         AS created_at,
                  (ARRAY_AGG(history.project.updated_at ORDER BY history.project.recorded_at) FILTER (WHERE history.project.updated_at IS NOT NULL))[1]         AS updated_at
          FROM history.project
          WHERE history.project.recorded_at > version_p
          GROUP BY history.project.id) AS past
    ON present.id = past.id
    WHERE COALESCE(past.created_at, present.created_at) < version_p;
$$
    LANGUAGE sql
    STABLE;

history.project表用于记录project表的历史变更，该函数可还原指定时间点的表状态。

目前查询性能极差：主查询的WHERE "project_version"."id" = 'fd18211b-a400-49ed-a723-9648ab05ca4f'过滤条件本应提前执行以减少数据量，但查询规划器却在最后才执行该过滤。

如果将函数中COALESCE(past.id, present.id) AS id替换为past.id AS id或present.id AS id，性能会显著提升，但这会导致逻辑错误——因为past.id或present.id可能为NULL。已知当past.id为NULL时所有past字段均为NULL，present.id为NULL时所有present字段均为NULL，因此该过滤条件完全可以安全下推至函数内部的子查询中。

需求：保留COALESCE(past.id, present.id)的逻辑前提下，让查询规划器将主查询的WHERE过滤条件下推至函数内部，提升查询性能。

解决方法

方法1：修改函数，新增id参数直接过滤

既然需要查询特定id的版本数据，最直接的方式是给函数新增一个可选的target_id参数，在函数内部直接对project和history.project表进行过滤，避免全表扫描：

CREATE OR REPLACE FUNCTION public.select_version_of_projects(
    version_p TIMESTAMP WITH TIME ZONE,
    target_id UUID DEFAULT NULL
)
    RETURNS TABLE
            (
                id             UUID,
                folder_id      UUID,
                name           TEXT,
                description    TEXT,
                picture        TEXT,
                files          JSON,
                keywords       TSVECTOR,
                scene          JSON,
                view           JSON,
                credits        JSON,
                published      JSON,
                imports        JSON,
                settings       JSON,
                studio_version TEXT,
                readonly       BOOLEAN,
                deleted        BOOLEAN,
                created_at     TIMESTAMP WITH TIME ZONE,
                updated_at     TIMESTAMP WITH TIME ZONE
            )
AS
$$
    SELECT  COALESCE(past.id, present.id)                         AS id,
            COALESCE(past.folder_id, present.folder_id)           AS folder_id,
            COALESCE(past.name, present.name)                     AS name,
            COALESCE(past.description, present.description)       AS description,
            COALESCE(past.picture, present.picture)               AS picture,
            COALESCE(past.files, present.files)                   AS files,
            COALESCE(past.keywords, present.keywords)             AS keywords,
            COALESCE(past.scene, present.scene)                   AS scene,
            COALESCE(past.view, present.view)                     AS view,
            COALESCE(past.credits, present.credits)               AS credits,
            COALESCE(past.published, present.published)           AS published,
            COALESCE(past.imports, present.imports)               AS imports,
            COALESCE(past.settings, present.settings)             AS settings,
            COALESCE(past.studio_version, present.studio_version) AS studio_version,
            COALESCE(past.readonly, present.readonly)             AS readonly,
            COALESCE(past.deleted, present.deleted)               AS deleted,
            COALESCE(past.created_at, present.created_at)         AS created_at,
            COALESCE(past.updated_at, present.updated_at)         AS updated_at
    FROM (
        SELECT *
        FROM project
        WHERE target_id IS NULL OR id = target_id
    ) AS present
    FULL OUTER JOIN
          (
              SELECT history.project.id,
                      (ARRAY_AGG(history.project.folder_id ORDER BY history.project.recorded_at) FILTER (WHERE history.project.folder_id IS NOT NULL))[1]           AS folder_id,
                      (ARRAY_AGG(history.project.name ORDER BY history.project.recorded_at) FILTER (WHERE history.project.name IS NOT NULL))[1]                     AS name,
                      (ARRAY_AGG(history.project.description ORDER BY history.project.recorded_at) FILTER (WHERE history.project.description IS NOT NULL))[1]       AS description,
                      (ARRAY_AGG(history.project.picture ORDER BY history.project.recorded_at) FILTER (WHERE history.project.picture IS NOT NULL))[1]               AS picture,
                      (ARRAY_AGG(history.project.files ORDER BY history.project.recorded_at) FILTER (WHERE history.project.files IS NOT NULL))[1]                   AS files,
                      (ARRAY_AGG(history.project.keywords ORDER BY history.project.recorded_at) FILTER (WHERE history.project.keywords IS NOT NULL))[1]             AS keywords,
                      (ARRAY_AGG(history.project.scene ORDER BY history.project.recorded_at) FILTER (WHERE history.project.scene IS NOT NULL))[1]                   AS scene,
                      (ARRAY_AGG(history.project.view ORDER BY history.project.recorded_at) FILTER (WHERE history.project.view IS NOT NULL))[1]                     AS view,
                      (ARRAY_AGG(history.project.credits ORDER BY history.project.recorded_at) FILTER (WHERE history.project.credits IS NOT NULL))[1]               AS credits,
                      (ARRAY_AGG(history.project.published ORDER BY history.project.recorded_at) FILTER (WHERE history.project.published IS NOT NULL))[1]           AS published,
                      (ARRAY_AGG(history.project.imports ORDER BY history.project.recorded_at) FILTER (WHERE history.project.imports IS NOT NULL))[1]               AS imports,
                      (ARRAY_AGG(history.project.settings ORDER BY history.project.recorded_at) FILTER (WHERE history.project.settings IS NOT NULL))[1]             AS settings,
                      (ARRAY_AGG(history.project.studio_version ORDER BY history.project.recorded_at) FILTER (WHERE history.project.studio_version IS NOT NULL))[1] AS studio_version,
                      (ARRAY_AGG(history.project.readonly ORDER BY history.project.recorded_at) FILTER (WHERE history.project.readonly IS NOT NULL))[1]             AS readonly,
                      (ARRAY_AGG(history.project.deleted ORDER BY history.project.recorded_at) FILTER (WHERE history.project.deleted IS NOT NULL))[1]               AS deleted,
                      (ARRAY_AGG(history.project.created_at ORDER BY history.project.recorded_at) FILTER (WHERE history.project.created_at IS NOT NULL))[1]         AS created_at,
                      (ARRAY_AGG(history.project.updated_at ORDER BY history.project.recorded_at) FILTER (WHERE history.project.updated_at IS NOT NULL))[1]         AS updated_at
              FROM history.project
              WHERE history.project.recorded_at > version_p
                AND (target_id IS NULL OR history.project.id = target_id)
              GROUP BY history.project.id
          ) AS past
    ON present.id = past.id
    WHERE COALESCE(past.created_at, present.created_at) < version_p
      AND (target_id IS NULL OR COALESCE(past.id, present.id) = target_id);
$$
    LANGUAGE sql
    STABLE;

调用方式改为：

select * from public.select_version_of_projects('2024-03-08T08:31:08.280Z', 'fd18211b-a400-49ed-a723-9648ab05ca4f');

这种方式直接在函数内部对两张表进行过滤，利用project.id和history.project.id上的索引，避免全表扫描和聚合，性能提升最明显。

方法2：改写函数逻辑，让规划器可识别过滤条件

Postgres查询规划器无法自动将COALESCE(past.id, present.id) = 'xxx'转化为past.id = 'xxx' OR present.id = 'xxx'（尤其是在FULL OUTER JOIN场景下）。你可以在函数的WHERE子句中显式添加这个逻辑，同时保留原函数的参数结构：

修改函数末尾的WHERE条件：

WHERE COALESCE(past.created_at, present.created_at) < version_p
  AND (
    -- 匹配主查询可能传入的id过滤条件
    (current_setting('app.target_project_id', true) IS NULL)
    OR past.id = current_setting('app.target_project_id', true)::UUID
    OR present.id = current_setting('app.target_project_id', true)::UUID
  )

然后在查询前设置会话级参数：

SET app.target_project_id = 'fd18211b-a400-49ed-a723-9648ab05ca4f';
select * from public.select_version_of_projects('2024-03-08T08:31:08.280Z');
RESET app.target_project_id;

这种方式不需要修改函数的参数，但需要借助会话参数传递过滤条件，让函数内部提前过滤数据。

方法3：将函数改写为可下推的SQL表达式（不使用函数）

如果不需要复用函数逻辑，可以直接将函数展开为SQL查询，显式添加id过滤条件：

SELECT  COALESCE(past.id, present.id)                         AS id,
        COALESCE(past.folder_id, present.folder_id)           AS folder_id,
        COALESCE(past.name, present.name)                     AS name,
        COALESCE(past.description, present.description)       AS description,
        COALESCE(past.picture, present.picture)               AS picture,
        COALESCE(past.files, present.files)                   AS files,
        COALESCE(past.keywords, present.keywords)             AS keywords,
        COALESCE(past.scene, present.scene)                   AS scene,
        COALESCE(past.view, present.view)                     AS view,
        COALESCE(past.credits, present.credits)               AS credits,
        COALESCE(past.published, present.published)           AS published,
        COALESCE(past.imports, present.imports)               AS imports,
        COALESCE(past.settings, present.settings)             AS settings,
        COALESCE(past.studio_version, present.studio_version) AS studio_version,
        COALESCE(past.readonly, present.readonly)             AS readonly,
        COALESCE(past.deleted, present.deleted)               AS deleted,
        COALESCE(past.created_at, present.created_at)         AS created_at,
        COALESCE(past.updated_at, present.updated_at)         AS updated_at
FROM (SELECT * FROM project WHERE id = 'fd18211b-a400-49ed-a723-9648ab05ca4f') AS present
FULL OUTER JOIN
      (SELECT history.project.id,
              (ARRAY_AGG(history.project.folder_id ORDER BY history.project.recorded_at) FILTER (WHERE history.project.folder_id IS NOT NULL))[1]           AS folder_id,
              (ARRAY_AGG(history.project.name ORDER BY history.project.recorded_at) FILTER (WHERE history.project.name IS NOT NULL))[1]                     AS name,
              (ARRAY_AGG(history.project.description ORDER BY history.project.recorded_at) FILTER (WHERE history.project.description IS NOT NULL))[1]       AS description,
              (ARRAY_AGG(history.project.picture ORDER BY history.project.recorded_at) FILTER (WHERE history.project.picture IS NOT NULL))[1]               AS picture,

如何让PostgreSQL查询规划器将主查询WHERE条件下推至子查询？

方法1：修改函数，新增id参数直接过滤 #

方法2：改写函数逻辑，让规划器可识别过滤条件 #

方法3：将函数改写为可下推的SQL表达式（不使用函数） #

方法1：修改函数，新增id参数直接过滤

方法2：改写函数逻辑，让规划器可识别过滤条件

方法3：将函数改写为可下推的SQL表达式（不使用函数）