PostgreSQL非外键引用列JOIN如何创建手动依赖统计

假设有如下表结构：

CREATE TABLE main
(
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY
);

CREATE TABLE apple
(
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    main_id INTEGER NOT NULL REFERENCES main(id)
);

CREATE TABLE orange
(
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    main_id INTEGER NOT NULL REFERENCES main(id)
);

CREATE TABLE main_history
(
    id INTEGER NOT NULL,
    history_valid_from TIMESTAMPTZ NOT NULL,
    history_valid_to TIMESTAMPTZ
);

CREATE TABLE apple_history
(
    id INTEGER NOT NULL,
    main_id INTEGER NOT NULL REFERENCES main(id), -- main_history 无主键
    history_valid_from TIMESTAMPTZ NOT NULL,
    history_valid_to TIMESTAMPTZ
);

CREATE TABLE orange_history
(
    id INTEGER NOT NULL,
    main_id INTEGER NOT NULL REFERENCES main(id), -- main_history 无主键
    history_valid_from TIMESTAMPTZ NOT NULL,
    history_valid_to TIMESTAMPTZ
);

其中main为根表，apple、orange两张表通过外键引用main表；99%的场景下，每条main表记录对应的子表记录最多为20条。

性能问题表现 #

查询历史数据时出现严重性能问题，原查询语句如下：

SELECT * FROM main_history
LEFT JOIN apple_history ON apple_history.main_id = main_history.id AND
  apple_history.history_valid_from <= '2021-12-20T10:46:52.482620Z' AND
  (apple_history.history_valid_to IS NULL OR '2021-12-20T10:46:52.482620Z' < apple_history.history_valid_to)
LEFT JOIN orange_history ON orange_history.main_id = main_history.id AND
  orange_history.history_valid_from <= '2021-12-20T10:46:52.482620Z' AND
  (orange_history.history_valid_to IS NULL OR '2021-12-20T10:46:52.482620Z' < orange_history.history_valid_to)
WHERE
  main_history.id IN (1,2,3,4) AND
  main_history.history_valid_from <= '2021-12-20T10:46:52.482620Z' AND
  (main_history.history_valid_to IS NULL OR '2021-12-20T10:46:52.482620Z' < main_history.history_valid_to)

核心问题：当前关联使用的是无外键引用约束的列，PostgreSQL对返回行数的估算偏差最高可达20000倍。

已尝试的临时方案 #

执行以下配置关闭哈希连接和合并连接：

SET enable_hashjoin = off; SET enable_mergejoin = off

由于实际数据量很小，执行该配置后查询耗时降至100毫秒。

如果将查询改写为直接关联main表而非main_history表的形式（修正了原语句中不存在的表引用笔误）：

SELECT * FROM main
LEFT JOIN apple_history ON apple_history.main_id = main.id AND
  apple_history.history_valid_from <= '2021-12-20T10:46:52.482620Z' AND
  (apple_history.history_valid_to IS NULL OR '2021-12-20T10:46:52.482620Z' < apple_history.history_valid_to)
LEFT JOIN orange_history ON orange_history.main_id = main.id AND
  orange_history.history_valid_from <= '2021-12-20T10:46:52.482620Z' AND
  (orange_history.history_valid_to IS NULL OR '2021-12-20T10:46:52.482620Z' < orange_history.history_valid_to)
WHERE
  main.id IN (1,2,3,4)

此时优化器可以使用原有统计信息，查询执行正常无性能问题。

待解决问题 #

是否可以为无REFERENCES外键约束的JOIN关联列创建手动统计信息？目标是无需强制指定连接策略，即可直接使用main_history作为主表完成高效查询。

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