2 种连接树：决定哪两张表先连接。 × 2² 种方向：两次连接中，每次都可交换内外表角色。 × 3² 种算法：两次连接中，每次均可选择 Hash、Merge 或 Nested Loop。 × 4³ 种扫描：三张表各自可选顺序、索引、仅索引或位图扫描。 = 4,608

SELECT cn.name,
       COUNT(*) AS titles
FROM   movie_companies AS mc,
       company_name AS cn
WHERE  mc.company_id = cn.id
  AND  cn.country_code = '[jp]'
GROUP  BY cn.name
ORDER  BY titles DESC
LIMIT  10;

1 种连接树：只有两张表时，连接方式唯一。 × 2¹ 种方向：一次连接只有 2 种内外表方向。 × 3¹ 种算法：可选择 Hash Join、Merge Join 或 Nested Loop。 × 4² 种扫描：两张表各自可选顺序、索引、仅索引或位图扫描。 = 96

SELECT cn.name,
       COUNT(*) AS titles
FROM   title AS t,
       movie_companies AS mc,
       company_name AS cn
WHERE  t.id = mc.movie_id
  AND  mc.company_id = cn.id
  AND  cn.country_code = '[jp]'
  AND  t.production_year BETWEEN 2000 AND 2009
GROUP  BY cn.name
ORDER  BY titles DESC
LIMIT  10;

2 Join 树：3 张表在交换输入之前的连接方式数量。 × 2² 方向：2 个 join 各自可以交换内外侧输入。 × 3² 算法：2 个 join 各自可选择 hash、merge 或 nested loop。 × 4³ 扫描：3 张表各自可顺序读取，或通过 index、index-only、bitmap 扫描。 = 4,608

SELECT MIN(t.title) AS movie_title
FROM keyword AS k,
     movie_info AS mi,
     movie_keyword AS mk,
     title AS t
WHERE k.keyword LIKE '%sequel%'
  AND mi.info IN ('Bulgaria')
  AND t.production_year > 2010
  AND t.id = mi.movie_id
  AND t.id = mk.movie_id
  AND mk.movie_id = mi.movie_id
  AND k.id = mk.keyword_id;

8 Join 树：4 张表在交换输入之前的连接方式数量。 × 2³ 方向：3 个 join 各自可以交换内外侧输入。 × 3³ 算法：3 个 join 各自可选择 hash、merge 或 nested loop。 × 4⁴ 扫描：4 张表各自可顺序读取，或通过 index、index-only、bitmap 扫描。 = 442,368

SELECT MIN(t.title) AS movie_title
FROM company_name AS cn,
     keyword AS k,
     movie_companies AS mc,
     movie_keyword AS mk,
     title AS t
WHERE cn.country_code ='[de]'
  AND k.keyword ='character-name-in-title'
  AND cn.id = mc.company_id
  AND mc.movie_id = t.id
  AND t.id = mk.movie_id
  AND mk.keyword_id = k.id
  AND mc.movie_id = mk.movie_id;

25 Join 树：5 张表在交换输入之前的连接方式数量。 × 2⁴ 方向：4 个 join 各自可以交换内外侧输入。 × 3⁴ 算法：4 个 join 各自可选择 hash、merge 或 nested loop。 × 4⁵ 扫描：5 张表各自可顺序读取，或通过 index、index-only、bitmap 扫描。 = 33,177,600

SELECT MIN(lt.link) AS link_type,
       MIN(t1.title) AS first_movie,
       MIN(t2.title) AS second_movie
FROM keyword AS k,
     link_type AS lt,
     movie_keyword AS mk,
     movie_link AS ml,
     title AS t1,
     title AS t2
WHERE k.keyword ='10,000-mile-club'
  AND mk.keyword_id = k.id
  AND t1.id = mk.movie_id
  AND ml.movie_id = t1.id
  AND ml.linked_movie_id = t2.id
  AND lt.id = ml.link_type_id
  AND mk.movie_id = t1.id;

56 Join trees: 在交换输入表之前，6 张表的所有可能连接拓扑结构。 × 25 Orientations: 每个连接都决定哪张表作为外表，哪张表作为内表（可互换方向）。 × 35 Algorithms: 每个连接可选择 Hash、Merge 或 Nested Loop 三种算法之一。 × 46 Scans: 每张表可选择顺序扫描或索引扫描（包括 Index-Only Scan 和 Bitmap Scan）。 = 1,783,627,776

SELECT MIN(a1.name) AS writer_pseudo_name,
       MIN(t.title) AS movie_title
FROM aka_name AS a1,
     cast_info AS ci,
     company_name AS cn,
     movie_companies AS mc,
     name AS n1,
     role_type AS rt,
     title AS t
WHERE cn.country_code ='[us]'
  AND rt.role ='writer'
  AND a1.person_id = n1.id
  AND n1.id = ci.person_id
  AND ci.movie_id = t.id
  AND t.id = mc.movie_id
  AND mc.company_id = cn.id
  AND ci.role_id = rt.id
  AND a1.person_id = ci.person_id
  AND ci.movie_id = mc.movie_id;

696 Join trees: 在交换输入表之前，7 张表的所有可能连接拓扑结构。 × 26 Orientations: 每个连接都决定哪张表作为外表，哪张表作为内表（可互换方向）。 × 36 Algorithms: 每个连接可选择 Hash、Merge 或 Nested Loop 三种算法之一。 × 47 Scans: 每张表可选择顺序扫描或索引扫描（包括 Index-Only Scan 和 Bitmap Scan）。 = 532,030,685,184

SELECT MIN(an.name) AS cool_actor_pseudonym,
       MIN(t.title) AS series_named_after_char
FROM aka_name AS an,
     cast_info AS ci,
     company_name AS cn,
     keyword AS k,
     movie_companies AS mc,
     movie_keyword AS mk,
     name AS n,
     title AS t
WHERE cn.country_code ='[us]'
  AND k.keyword ='character-name-in-title'
  AND an.person_id = n.id
  AND n.id = ci.person_id
  AND ci.movie_id = t.id
  AND t.id = mk.movie_id
  AND mk.keyword_id = k.id
  AND t.id = mc.movie_id
  AND mc.company_id = cn.id
  AND an.person_id = ci.person_id
  AND ci.movie_id = mc.movie_id
  AND ci.movie_id = mk.movie_id
  AND mc.movie_id = mk.movie_id;

4,698 连接树：8 张表在输入交换前的所有连接方式。 × 27 方向：7 个连接中每个都可以交换内外表的输入方向。 × 3^7 算法：7 个连接中每个都选择哈希、归并或嵌套循环连接。 × 4^8 扫描：8 张表中每张都可以选择顺序扫描或索引、仅索引、位图扫描。 = 86,188,970,999,808

SELECT MIN(cn.name) AS producing_company,
       MIN(miidx.info) AS rating,
       MIN(t.title) AS movie
FROM company_name AS cn,
     company_type AS ct,
     info_type AS it,
     info_type AS it2,
     kind_type AS kt,
     movie_companies AS mc,
     movie_info AS mi,
     movie_info_idx AS miidx,
     title AS t
WHERE cn.country_code ='[us]'
  AND ct.kind ='production companies'
  AND it.info ='rating'
  AND it2.info ='release dates'
  AND kt.kind ='movie'
  AND mi.movie_id = t.id
  AND it2.id = mi.info_type_id
  AND kt.id = t.kind_id
  AND mc.movie_id = t.id
  AND cn.id = mc.company_id
  AND ct.id = mc.company_type_id
  AND miidx.movie_id = t.id
  AND it.id = miidx.info_type_id
  AND mi.movie_id = miidx.movie_id
  AND mi.movie_id = mc.movie_id
  AND miidx.movie_id = mc.movie_id;

20,340 连接树：9 张表在输入交换前的所有连接方式。 × 2^8 方向：8 个连接中每个都可以交换内外表的输入方向。 × 3^8 算法：8 个连接中每个都选择哈希、归并或嵌套循环连接。 × 4^9 扫描：9 张表中每张都可以选择顺序扫描或索引、仅索引、位图扫描。 = 8,955,727,561,359,360

SELECT MIN(n.name) AS voicing_actress,
       MIN(t.title) AS jap_engl_voiced_movie
FROM aka_name AS an,
     char_name AS chn,
     cast_info AS ci,
     company_name AS cn,
     info_type AS it,
     movie_companies AS mc,
     movie_info AS mi,
     name AS n,
     role_type AS rt,
     title AS t
WHERE ci.note IN ('(voice)',
                  '(voice: Japanese version)',
                  '(voice) (uncredited)',
                  '(voice: English version)')
  AND cn.country_code ='[us]'
  AND it.info = 'release dates'
  AND n.gender ='f'
  AND rt.role ='actress'
  AND t.production_year > 2000
  AND t.id = mi.movie_id
  AND t.id = mc.movie_id
  AND t.id = ci.movie_id
  AND mc.movie_id = ci.movie_id
  AND mc.movie_id = mi.movie_id
  AND mi.movie_id = ci.movie_id
  AND cn.id = mc.company_id
  AND it.id = mi.info_type_id
  AND n.id = ci.person_id
  AND rt.id = ci.role_id
  AND n.id = an.person_id
  AND ci.person_id = an.person_id
  AND chn.id = ci.person_role_id;

242,160 种 join 树：10 张表可能的连接方式（还没考虑交换输入顺序）。 × 29 种方向：9 次 join 各自可以交换外层和内层输入。 × 39 种算法：9 次 join 各自选择 hash、merge 或 nested loop。 × 410 种扫描：10 张表各自可以选择顺序扫描，或者 index、index-only、bitmap 扫描。 = 2,558,960,455,762,575,360

1,490,850 种连接树：11 张表在输入未交换时的所有连接方式。 × 2^10 种方向：每个 10 个连接均可选择外输入和内输入。 × 3^10 种算法：每个连接可在 Hash、Merge 或 Nested Loop 中三选一。 × 4^11 种扫描方式：11 张表均可通过顺序读取、索引、仅索引或位图扫描获取数据。 = 378,099,722,048,923,238,400

Training a 4B model to produce 81% faster query plans than Postgres

SELECT MIN(chn.name) AS character_name,
       MIN(mi_idx.info) AS rating,
       MIN(t.title) AS complete_hero_movie
FROM complete_cast AS cc,
     comp_cast_type AS cct1,
     comp_cast_type AS cct2,
     char_name AS chn,
     cast_info AS ci,
     info_type AS it2,
     keyword AS k,
     kind_type AS kt,
     movie_info_idx AS mi_idx,
     movie_keyword AS mk,
     name AS n,
     title AS t
WHERE cct1.kind = 'cast'
  AND cct2.kind LIKE '%complete%'
  AND chn.name IS NOT NULL
  AND (chn.name LIKE '%man%'
       OR chn.name LIKE '%Man%')
  AND it2.info = 'rating'
  AND k.keyword IN ('superhero',
                    'marvel-comics',
                    'based-on-comic',
                    'fight')
  AND kt.kind = 'movie'
  AND mi_idx.info > '8.0'
  AND t.production_year > 2005
  AND kt.id = t.kind_id
  AND t.id = mk.movie_id
  AND t.id = ci.movie_id
  AND t.id = cc.movie_id
  AND t.id = mi_idx.movie_id
  AND mk.movie_id = ci.movie_id
  AND mk.movie_id = cc.movie_id
  AND mk.movie_id = mi_idx.movie_id
  AND ci.movie_id = cc.movie_id
  AND ci.movie_id = mi_idx.movie_id
  AND cc.movie_id = mi_idx.movie_id
  AND chn.id = ci.person_role_id
  AND n.id = ci.person_id
  AND k.id = mk.keyword_id
  AND cct1.id = cc.subject_id
  AND cct2.id = cc.status_id
  AND it2.id = mi_idx.info_type_id;

11,932,560 Join trees: the ways 12 tables can be joined up, before any swapping of inputs. × 2¹¹ Orientations: each of the 11 joins can swap which input is outer and which is inner. × 3¹¹ Algorithms: each of the 11 joins picks hash, merge, or nested loop. × 4¹² Scans: each of the 12 tables is either read sequentially or via index, index-only or bitmap scans. = 72,630,206,166,931,876,085,760 lots!

Estimating, not counting

Postgres is in a tough spot here. It would be reasonable to think it could simply count cardinalities and pick the plan that minimizes the number of rows passed through to successive joins.

如何驾驭这头大象

pg_hint_plan 是一个设计得简洁优雅的第三方扩展：只需在 SQL 语句上方添加结构化的“提示”（hints）注释，就能引导 Postgres 采用符合提示指令的查询计划。例如：

/*+
The hint block. It is an ordinary SQL comment with a leading +, so Postgres ignores it and pg_hint_plan reads it.
HashJoin(a b) Join a and b using a hash join.
SeqScan(a) Read table a with a sequential scan rather than an index.
*/

EXPLAIN
-- EXPLAIN prints the plan Postgres would use instead of running the query.
SELECT *
  FROM pgbench_branches b
  JOIN pgbench_accounts a ON b.bid = a.bid
  ORDER BY a.aid;

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Sort
    The root of the plan. Rows flow upward, so this runs last: it orders the joined rows by a.aid.
   (cost=31465.84..31715.84 rows=100000 width=197)
   Postgres’s estimates for this node: startup cost..total cost, estimated rows out, and average row width in bytes.
   Sort Key: a.aid
   ->  Hash Join
      The join method the HashJoin(a b) hint asked for.
     (cost=1.02..4016.02 rows=100000 width=197)
        Hash Cond: (a.bid = b.bid)
        The join condition, taken from the ON clause.
        ->  Seq Scan on pgbench_accounts a
           The scan method the SeqScan(a) hint asked for. This is the probe side: each row looks up a match in the hash table.
          (cost=0.00..2640.00 rows=100000 width=97)
        ->  Hash
           The build side. The small table is read first and loaded into an in-memory hash table keyed on bid.
          (cost=1.01..1.01 rows=1 width=100)
               ->  Seq Scan on pgbench_branches b
                (cost=0.00..1.01 rows=1 width=100)
(7 rows)

该提示强制要求在连接 pgbench_accounts 和 pgbench_branches 时使用 HashJoin，并对 pgbench_accounts 表执行顺序扫描；实际的查询计划也很好地遵循了这一指令。

定义问题

既然我们可以利用 pg_hint_plan 的提示来影响 Postgres 选择不同（甚至更优）的查询计划，那么我们要探讨的初始问题是：

语言模型能否学会生成能产生更优查询计划的提示？

相关研究

为什么这个问题值得投入精力去解决？

我最初的思路是将查询语句和 Postgres 查询规划器所用的完全相同的信息集提供给模型。这本质上是在探究能否构建更优的基数估算器。但我不认为这是一条值得探索的路径，因为那意味着我们要去挑战数十年的基数估算研究成果。此外，仅推理延迟这一项成本，就远超模型相比 Postgres 极速查询优化器所能带来的任何收益。

第二个想法——我认为也是正确的解题方式——基于特定的数据库使用模式：重度分析型负载。当查询以次优的 Postgres 默认执行计划被运行成千上万次时，效率收益正在白白流失。相反，我们可以训练模型为特定查询寻找更优的执行方式。尽管训练过程可能需要预先执行该查询几十甚至上百次，但将所有查询运行摊薄后的成本将大幅降低。

目标并非试图在一次性查询的时间/效率帕累托前沿上战胜 Postgres，但我们有望在反复执行的查询上实现超越。

模型及其框架

我决定从一个小型的 4B 模型入手，因为我可以在家里那台配有两张 RTX 3090 的机器（名叫 FLOPper）上最轻松地自行完成训练和推理。

在我启动这个项目的同时，Qwen 3.8 系列模型发布了，但不幸的是没有 4B 版本。不过，我发现了德国一家名为 Empero 的小型实验室开发的 Qwen 3.8 4B 蒸馏模型，并对此颇感兴趣。他们以 Qwen 3.8 的 2.4T 模型作为教师模型，将其知识蒸馏到 Qwen 3.5 4B 中，从而生成了 empero-ai/Qwen3.8-4B-Distill。这款蒸馏模型并非全面优于其 3.5 基座模型：它在 MMLU 任务上表现更佳，而在 GSM8K 任务上稍逊一筹。换言之，这种蒸馏策略在评估通用知识广度时表现更好，但在多步数学推理上略逊。至于哪种更适用于我们的任务，目前尚不明确，但我最终决定使用这款蒸馏模型。

1. inspect_relation — 列出表的所有列（含类型和是否可空）、索引定义，以及预估行数和字节数
2. get_column_stats — 获取 Postgres 对某个 relation 中 1-8 个列的 planner 统计信息
3. get_plan — 获取默认计划的预估结果，或某个已提交候选计划的执行计划
4. evaluate_candidate — 校验提议的计划操作，然后实际执行以获得耗时和计划诊断信息
5. keep_default — 直接采用 Postgres 的默认计划作为候选，结束搜索
6. finish — 接收一个已提交的候选 ID 或默认计划，结束搜索

为了利用结构化输出，agent 被要求生成 PlanAction JSON 对象。调用 evaluate_candidate 时，系统会把 PlanAction 对象编译成 hint，并前置到原始查询之前。

下面是一个 agent 的执行轨迹示例：

Agent →A tool call made by the model. get_plan("default")Ask the harness for the plan Postgres picks on its own. This returns the planner’s estimates only; nothing is executed.Tool  ←The harness’s reply, fed back into the model’s context. Default plan: (t ⋈ mc) ⋈ cn, hash joins, estimated rows per node Agent → evaluate_candidate({Propose one PlanAction. The harness validates it with a plain EXPLAIN, compiles it to hints, and, with execution feedback on, runs the query once after a warmup and reports the timing. "leading": { "left": { "left": "cn", "right": "mc" }, "right": "t" },The join tree: join cn with mc first, then join the result with t. This is the first tree from the figure earlier. "joins": [{ "relations": ["cn", "mc"], "force": "hash" }]Force a hash join for the cn ⋈ mc step. })Tool  ← c1The id the harness assigns to this candidate. get_plan and finish refer to candidates by id. · valid · novel plan · 118 ms · 0.91× defaultExecution feedback: the candidate’s measured time and its preliminary ratio to the default’s median. Below 1 means slower than Postgres. · 2 attempts leftEvery submission, valid or not, consumes one of the rollout’s candidate attempts. Agent → evaluate_candidate({ "leading": { "left": { "left": "t", "right": "mc" }, "right": "cn" },Try the other tree: t with mc first, then cn. "joins": [{ "relations": ["t", "mc"], "force": "nestloop" }],A nested loop for the first join, so the filtered t rows probe mc one at a time. "scans": [{ "relation": "mc", "force": "index" }]Make the inner side of that loop an index scan on mc. })Tool  ← c2 · valid · novel plan · 87 ms · 1.24× defaultFaster than the default: a ratio above 1 is a speedup. · 1 attempt left Agent → evaluate_candidate({ "leading": { "left": { "left": "t", "right": "mc" }, "right": "cn" }, "joins": [{ "relations": ["t", "mc"], "force": "hash" }]Same tree as c2, but a hash join for the first step, to see whether the nested loop was the win. })Tool  ← c3 · valid · novel plan · 100 ms · 1.08× default · 0 attempts leftThe candidate budget is spent. From here the only tool the model is offered is finish. Agent → finish({ "selected_candidate_id": "c2" })End the search by naming a candidate, including an earlier one. The harness then measures it against the default with the final paired protocol.Tool  ← Finished · selected c2

SELECT MIN(t.title) AS movie_title
FROM   company_name AS cn
JOIN   movie_companies AS mc ON mc.company_id = cn.id
JOIN   title AS t ON t.id = mc.movie_id
JOIN   movie_keyword AS mk ON mk.movie_id = t.id
JOIN   keyword AS k ON k.id = mk.keyword_id
WHERE  cn.country_code = :country_code
  AND  k.keyword = 'character-name-in-title';

JOB：113 个查询，33 个模板，33 种拓扑结构

• JOB 1 · 4 个查询 · 5 张表 1
• JOB 2 · 4 个查询 · 5 张表 2
• JOB 3 · 3 个查询 · 4 张表 3
• JOB 4 · 3 个查询 · 5 张表 4
• JOB 5 · 3 个查询 · 5 张表 5
• JOB 6 · 6 个查询 · 5 张表 6
• JOB 7 · 3 个查询 · 8 张表 7
• JOB 8 · 4 个查询 · 7 张表 8
• JOB 9 · 4 个查询 · 8 张表 9
• JOB 10 · 3 个查询 · 7 张表 10
• JOB 11 · 4 个查询 · 8 张表 11
• JOB 12 · 3 个查询 · 8 张表 12
• JOB 13 · 4 个查询 · 9 张表 13
• JOB 14 · 3 个查询 · 8 张表 14
• JOB 15 · 4 个查询 · 9 张表 15
• JOB 16 · 4 个查询 · 8 张表 16
• JOB 17 · 6 个查询 · 7 张表 17
• JOB 18 · 3 个查询 · 7 张表 18
• JOB 19 · 4 个查询 · 10 张表 19
• JOB 20 · 3 个查询 · 10 张表 20
• JOB 21 · 3 个查询 · 9 张表 21
• JOB 22 · 4 个查询 · 11 张表 22
• JOB 23 · 3 个查询 · 11 张表 23
• JOB 24 · 2 个查询 · 12 张表 24
• JOB 25 · 3 个查询 · 9 张表 25
• JOB 26 · 3 个查询 · 12 张表 26
• JOB 27 · 3 个查询 · 12 张表 27
• JOB 28 · 3 个查询 · 14 张表 28
• JOB 29 · 3 个查询 · 17 张表 29
• JOB 30 · 3 个查询 · 12 张表 30
• JOB 31 · 3 个查询 · 11 张表 31
• JOB 32 · 2 个查询 · 6 张表 32
• JOB 33 · 3 个查询 · 14 张表 33

CEB：13,646 个查询，16 个模板，12 种拓扑结构

• CEB 1a · 3,000 个查询 · 9 张表 1a
• CEB 2a · 888 个查询 · 11 张表 2a
• CEB 2b · 500 个查询 · 11 张表 2b
• CEB 2c · 298 个查询 · 9 张表 2c
• CEB 3a · 1,383 个查询 · 10 张表 3a
• CEB 3b · 256 个查询 · 10 张表 3b
• CEB 4a · 516 个查询 · 6 张表 4a
• CEB 5a · 1,014 个查询 · 10 张表 5a
• CEB 6a · 465 个查询 · 14 张表 6a
• CEB 7a · 167 条查询 · 16 张表 7a
• CEB 8a · 515 条查询 · 12 张表 8a
• CEB 9a · 2,247 条查询 · 9 张表 9a
• CEB 9b · 537 条查询 · 9 张表 9b
• CEB 10a · 1,019 条查询 · 7 张表 10a
• CEB 11a · 491 条查询 · 10 张表 11a
• CEB 11b · 350 条查询 · 12 张表 11b

1. container 0 — warmup measure idle
2. container 1 — warmup measure idle
3. container 2 — warmup measure idle
4. container 3 — warmup measure idle

Query A runs

查询 A 查询 B 共享命中块数 0 共享读取块数 0

预热后仍从 Linux 读取 完全驻留在 shared_buffers 中

job-13b 128 MB shared_buffers

job-13b 128 MB shared_buffers · 一个 no-op 候选（即与默认完全相同）

—— 抽样中……

0 轮 · 平局 0 · 虚假胜 0 · 虚假负 0 · 被骗率 0%