PostgreSQL 12：限制父事务数量并返回对应全部子日志的优化问询

环境信息 #

• Postgres：v12
• 具备可复现的测试用例

需求背景 #

现有transactions表和logs表，logs通过transaction_id与transactions关联。核心需求如下：

• 按address字段筛选logs并关联对应的transactions数据
• 将同一事务下的所有日志聚合为数组格式
• 限制返回的事务数量（示例为LIMIT 2）
• 必须返回符合条件事务下的全部日志（仅通过address字段定位目标事务）

表结构及测试数据 #

create table transactions
(id int,
 hash varchar);
 
create table logs
(transaction_id int,
 address varchar,
 value varchar
);

create index on logs(address);

insert into transactions values 
(1, 'h1'),
(2, 'h2'),
(3, 'h3'),
(4, 'h4'),
(5, 'h5')
;
 
insert into logs values 
(1, 'a1', 'h1.a1.1'),
(1, 'a1', 'h1.a1.2'),
(1, 'a3', 'h1.a3.1'),
(2, 'a1', 'h2.a1.1'),
(2, 'a2', 'h2.a2.1'),
(2, 'a2', 'h2.a2.2'),
(2, 'a3', 'h2.a3.1'),
(3, 'a2', 'h3.a2.1'),
(4, 'a1', 'h4.a1.1'),
(5, 'a2', 'h5.a2.1'),
(5, 'a3', 'h5.a3.1')
;

预期结果 #

当查询条件为WHERE log.address='a2' LIMIT 2时，返回结果如下：

id  logs_array
2   [{"address":"a1","value":"h2.a1.1"},{"address":"a2","value":"h2.a2.1"},{"address":"a2","value":"h2.a2.2"},{"address":"a3","value":"h2.a3.1"}]
3   [{"address":"a2","value":"h3.a2.1"}]

问题描述 #

现有查询语句结果正确，但当某一address对应日志量极大（10万+）时，查询耗时可达数分钟。若在MATERIALIZED子句中添加LIMIT，虽能提升速度，但会导致日志列表不完整。需要解决该性能问题，可选择不使用MATERIALIZED改写嵌套查询，或优化现有MATERIALIZED方案。

推测原因 #

PostgreSQL在MATERIALIZED子句中无法正确识别需限制事务数量的需求，会先查询全部符合地址条件的日志，再关联事务并应用LIMIT。已为logs(address)创建索引。

现有查询语句 #

WITH b AS MATERIALIZED (
        SELECT lg.transaction_id
        FROM logs lg
        WHERE lg.address='a2'
      
        -- 若取消注释则速度快但结果不正确
        -- LIMIT 2
    )
SELECT 
    id,
    (SELECT array_agg(JSON_BUILD_OBJECT('address',address,'value',value)) FROM logs WHERE transaction_id = t.id) logs_array
FROM transactions t 
WHERE t.id IN 
    (SELECT transaction_id FROM b)
LIMIT 2

真实场景执行计划 #

EXPLAIN WITH 
    b AS MATERIALIZED (
        SELECT lg.transaction_id
        FROM _logs lg
        WHERE lg.address in ('0xca530408c3e552b020a2300debc7bd18820fb42f', '0x68e78497a7b0db7718ccc833c164a18d8e626816')
    )
SELECT 
    (SELECT array_agg(JSON_BUILD_OBJECT('address',address)) FROM _logs WHERE transaction_id = t.id) logs_array
FROM _transactions t 
WHERE t.id IN 
    (SELECT transaction_id FROM b)
LIMIT 5000;
                                                                    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=87540.62..3180266.26 rows=5000 width=32)
   CTE b
     ->  Index Scan using _logs_address_idx on _logs lg  (cost=0.70..85820.98 rows=76403 width=8)
           Index Cond: ((address)::text = ANY ('{0xca530408c3e552b020a2300debc7bd18820fb42f,0x68e78497a7b0db7718ccc833c164a18d8e626816}'::text[]))
   ->  Nested Loop  (cost=1719.64..47260423.09 rows=76403 width=32)
         ->  HashAggregate  (cost=1719.07..1721.07 rows=200 width=8)
               Group Key: b.transaction_id
               ->  CTE Scan on b  (cost=0.00..1528.06 rows=76403 width=8)
         ->  Index Only Scan using _transactions_pkey on _transactions t  (cost=0.57..2.79 rows=1 width=8)
               Index Cond: (id = b.transaction_id)
         SubPlan 2
           ->  Aggregate  (cost=618.53..618.54 rows=1 width=32)
                 ->  Index Scan using _logs_transaction_id_idx on _logs  (cost=0.57..584.99 rows=6707 width=43)
                       Index Cond: (transaction_id = t.id)
 JIT:
   Functions: 17
   Options: Inlining true, Optimization true, Expressions true, Deforming true
(17 rows)

更新需求 #

需同时返回transactions表中的hash字段，预期结果如下：

id   hash  logs_array
2    h2    [{"address":"a1","value":"h2.a1.1"},{"address":"a2","value":"h2.a2.1"},{"address":"a2","value":"h2.a2.2"},{"address":"a3","value":"h2.a3.1"}]
3    h3    [{"address":"a2","value":"h3.a2.1"}]

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