PostgreSQL执行多表JOIN查询报错：mergejoin input data is out of order

问题背景 #

执行创建exclude_records表的SQL语句时，触发内部错误：ERROR: mergejoin input data is out of order，错误码XX000。推测因数据表体量较大，查询优化器选择merge join执行内连接时，输入数据排序不符合预期导致。

涉及表结构 #

relations表 #

postgres=# \d+ relations
                                                                 Table "public.relations"
                Column                |            Type             | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------------------------------------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 system_name                          | character varying           |           |          |         | extended |             |              | 
 from_package_name                    | character varying           |           |          |         | extended |             |              | 
 from_version                         | semver                      |           |          |         | plain    |             |              | 
 to_package_name                      | character varying           |           |          |         | extended |             |              | 
 actual_requirement                   | character varying           |           |          |         | extended |             |              | 
 to_version                           | semver                      |           |          |         | plain    |             |              | 
 to_package_highest_available_release | character varying           |           |          |         | extended |             |              | 
 interval_start                       | timestamp without time zone |           |          |         | plain    |             |              | 
 interval_end                         | timestamp without time zone |           |          |         | plain    |             |              | 
 is_out_of_date                       | boolean                     |           |          |         | plain    |             |              | 
 is_regular                           | boolean                     |           |          |         | plain    |             |              | 
 warnings                             | character varying           |           |          |         | extended |             |              | 
Access method: heap

versioninfo表 #

postgres=# \d+ versioninfo
                                                    Table "public.versioninfo"
    Column    |            Type             | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 system_name  | character varying           |           |          |         | extended |             |              | 
 package_name | character varying           |           |          |         | extended |             |              | 
 version_name | semver                      |           |          |         | plain    |             |              | 
 release_date | timestamp without time zone |           |          |         | plain    |             |              | 
Access method: heap

执行的SQL语句 #

CREATE TABLE exclude_records AS
SELECT R2.system_name, R2.from_package_name, R2.from_version, R2.to_package_name, COUNT(*) AS count_no
FROM relations R2
INNER JOIN versioninfo V3
ON R2.from_package_name = V3.package_name AND R2.from_version = V3.version_name AND R2.system_name = V3.system_name
INNER JOIN versioninfo V4
ON R2.to_package_name = V4.package_name AND R2.to_version = V4.version_name AND R2.system_name = V4.system_name
GROUP BY R2.system_name, R2.from_package_name, R2.from_version, R2.to_package_name
HAVING COUNT(*) > 1;

错误信息 #

ERROR:  mergejoin input data is out of order 

SQL state: XX000

解决方法 #

1. 临时禁用merge join #

通过会话级别参数强制优化器使用其他连接算法（如hash join）：

-- 禁用merge join
SET enable_mergejoin = off;

-- 执行创建表语句
CREATE TABLE exclude_records AS
SELECT R2.system_name, R2.from_package_name, R2.from_version, R2.to_package_name, COUNT(*) AS count_no
FROM relations R2
INNER JOIN versioninfo V3
ON R2.from_package_name = V3.package_name AND R2.from_version = V3.version_name AND R2.system_name = V3.system_name
INNER JOIN versioninfo V4
ON R2.to_package_name = V4.package_name AND R2.to_version = V4.version_name AND R2.system_name = V4.system_name
GROUP BY R2.system_name, R2.from_package_name, R2.from_version, R2.to_package_name
HAVING COUNT(*) > 1;

-- 恢复merge join设置（可选）
SET enable_mergejoin = on;

2. 更新表统计信息 #

过时的统计信息可能导致优化器生成错误的执行计划，更新后重新执行查询：

ANALYZE relations;
ANALYZE versioninfo;

3. 创建复合索引辅助排序 #

针对连接条件的字段创建复合索引，帮助优化器生成正确的merge join输入排序：

-- 为versioninfo表创建连接字段索引
CREATE INDEX idx_versioninfo_sys_pkg_ver ON versioninfo (system_name, package_name, version_name);

-- 为relations表的from端连接字段创建索引
CREATE INDEX idx_relations_from ON relations (system_name, from_package_name, from_version);

-- 为relations表的to端连接字段创建索引
CREATE INDEX idx_relations_to ON relations (system_name, to_package_name, to_version);

创建完成后重新执行原查询。

4. 重写查询减少数据量 #

使用EXISTS子查询先过滤出符合条件的relations记录，再进行分组统计：

CREATE TABLE exclude_records AS
WITH filtered_relations AS (
    SELECT system_name, from_package_name, from_version, to_package_name
    FROM relations R2
    WHERE EXISTS (
        SELECT 1 FROM versioninfo V3
        WHERE R2.from_package_name = V3.package_name 
          AND R2.from_version = V3.version_name 
          AND R2.system_name = V3.system_name
    )
    AND EXISTS (
        SELECT 1 FROM versioninfo V4
        WHERE R2.to_package_name = V4.package_name 
          AND R2.to_version = V4.version_name 
          AND R2.system_name = V4.system_name
    )
)
SELECT system_name, from_package_name, from_version, to_package_name, COUNT(*) AS count_no
FROM filtered_relations
GROUP BY system_name, from_package_name, from_version, to_package_name
HAVING COUNT(*) > 1;

内容的提问来源于stack exchange，提问作者Imranur Rahman