PostgreSQL 12按地址查交易及全量日志并聚合的性能优化问题

云原生阿岚

2026-6-17

优化PostgreSQL关联查询性能：按地址获取交易及全部日志

环境信息

Postgres：v12

需求与表结构

现有transactions表和logs表，logs通过transaction_hash与transactions关联。需求为：按address查询logs，关联transactions，将对应交易的全部日志聚合为数组，限制返回指定数量的交易（示例用LIMIT 2），结果需按交易的block_number降序排列。transactions.hash类型为varchar。

建表及插入数据的SQL如下：

create table transactions
(hash varchar,
 block_number integer,
 t_value varchar
);
 
insert into transactions values 
('h1',120,'v1'),
('h2',170,'v2'),
('h3',130,'v3'),
('h4',160,'v4'),
('h5',142,'v5')
;

create table logs
(transaction_hash varchar,
 address varchar,
 l_value varchar,
 l_trans_block_number integer -- 该字段与transactions的block_number重复
);
 
insert into logs values 
('h1', 'a1', 'h1.a1.1', 120),
('h1', 'a1', 'h1.a1.2', 120),
('h1', 'a3', 'h1.a3.1', 120),
('h2', 'a1', 'h2.a1.1', 170),
('h2', 'a2', 'h2.a2.1', 170),
('h2', 'a2', 'h2.a2.2', 170),
('h2', 'a3', 'h2.a3.1', 170),
('h3', 'a2', 'h3.a2.1', 130),
('h4', 'a1', 'h4.a1.1', 160),
('h5', 'a2', 'h5.a2.1', 142),
('h5', 'a3', 'h5.a3.1', 142)
;


create index on transactions(hash);
create index on transactions(block_number);
create index on logs(address);
create index on logs(l_trans_block_number);

预期结果

执行按address='a2'查询并返回2条交易时，预期输出如下：

hash    block_number    t_value logs_array
h2      170             v2      {"{\"address\" : \"a1\", \"l_value\" : \"h2.a1.1\", \"l_trans_block_number\" : 170}","{\"address\" : \"a2\", \"l_value\" : \"h2.a2.1\", \"l_trans_block_number\" : 170}","{\"address\" : \"a2\", \"l_value\" : \"h2.a2.2\", \"l_trans_block_number\" : 170}","{\"address\" : \"a3\", \"l_value\" : \"h2.a3.1\", \"l_trans_block_number\" : 170}"}
h5      142             v5      {"{\"address\" : \"a2\", \"l_value\" : \"h5.a2.1\", \"l_trans_block_number\" : 142}","{\"address\" : \"a3\", \"l_value\" : \"h5.a3.1\", \"l_trans_block_number\" : 142}"}

问题描述

以下SQL查询结果正确，但当单地址日志量达10万+时，查询耗时长达数分钟。若在物化CTE中设置LIMIT可提升速度，但会导致返回的交易日志列表不完整。需要解决性能问题，可选择不使用物化CTE，改用嵌套SELECT，或优化物化CTE写法。

推测PostgreSQL在物化CTE中无法识别需限制交易数量，而是先查询所有符合地址条件的日志，再关联交易并应用LIMIT。已创建logs(address)索引。

原查询SQL：

WITH 
    b AS MATERIALIZED (
        SELECT lg.transaction_hash
        FROM logs lg
        WHERE lg.address='a2'
      
        -- 启用以下两行会加快执行，但结果不正确
        -- ORDER BY lg.l_trans_block_number DESC
        -- LIMIT 2
    )
SELECT 
    hash,
    block_number,
    t_value,
    (SELECT array_agg(JSON_BUILD_OBJECT('address',address,'l_value',l_value,'l_trans_block_number',l_trans_block_number)) FROM logs WHERE transaction_hash = t.hash) logs_array
FROM transactions t 
WHERE t.hash IN 
    (SELECT transaction_hash FROM b)
ORDER BY t.block_number DESC -- 必须按block_number排序
LIMIT 2

真实场景执行情况

实际库中transaction_id为整数（非递增主键），查询耗时约30秒，执行计划如下：

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)

在查询中添加DISTINCT后耗时约10秒，但仍需在物化CTE内添加ORDER BY才能保证排序正确：

EXPLAIN WITH 
    b AS MATERIALIZED (
        SELECT DISTINCT lg.transaction_id
        FROM _logs lg
        WHERE lg.address in ('0xca530408c3e552b020a2300debc7bd18820fb42f', '0x68e78497a7b0db7718ccc833c164a18d8e626816')
        LIMIT  5000
    )
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)
;
                                                                          QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=87079.76..3211065.34 rows=5000 width=32)
   CTE b
     ->  Limit  (cost=86916.69..86966.69 rows=5000 width=8)
           ->  HashAggregate  (cost=86916.69..87443.81 rows=52712 width=8)
                 Group Key: lg.transaction_id
                 ->  Index Scan using _logs_address_idx on _logs lg  (cost=0.70..86723.67 rows=77206 width=8)
                       Index Cond: ((address)::text = ANY ('{0xca530408c3e552b020a2300debc7bd18820fb42f,0x68e78497a7b0db7718ccc833c164a18d8e626816}'::text[]))
   ->  HashAggregate  (cost=112.50..114.50 rows=200 width=8)
         Group Key: b.transaction_id
         ->  CTE Scan on b  (cost=0.00..100.00 rows=5000 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=624.68..624.69 rows=1 width=32)
           ->  Index Scan using _logs_transaction_id_idx on _logs  (cost=0.57..590.78 rows=6778 width=43)
                 Index Cond: (transaction_id = t.id)
 JIT:
   Functions: 19
   Options: Inlining true, Optimization true, Expressions true, Deforming true
(19 rows)

优化方案

方案1：先筛选指定数量的交易ID，再关联聚合日志

核心思路是先从logs中筛选出符合地址条件的交易ID，去重后按交易的block_number降序排序，先取指定数量的交易ID，再关联transactions和logs聚合所有日志，避免扫描全量日志：

SELECT
    t.hash,
    t.block_number,
    t.t_value,
    array_agg(JSON_BUILD_OBJECT('address', lg.address, 'l_value', lg.l_value, 'l_trans_block_number', lg.l_trans_block_number)) AS logs_array
FROM transactions t
JOIN (
    -- 先获取符合地址条件的交易ID，去重后按block_number降序取前2个
    SELECT DISTINCT lg.transaction_hash
    FROM logs lg
    JOIN transactions t2 ON lg.transaction_hash = t2.hash
    WHERE lg.address = 'a2'
    ORDER BY t2.block_number DESC
    LIMIT 2
) AS filtered_tx ON t.hash = filtered_tx.transaction_hash
JOIN logs lg ON t.hash = lg.transaction_hash
GROUP BY t.hash, t.block_number, t.t_value
ORDER BY t.block_number DESC;

方案2：使用窗口函数筛选目标交易

通过窗口函数给每个符合地址条件的交易按block_number排序，筛选出前N个交易ID后，再关联聚合日志：

WITH ranked_tx AS (
    SELECT
        lg.transaction_hash,
        t.block_number,
        ROW_NUMBER() OVER (ORDER BY t.block_number DESC) AS rn
    FROM logs lg
    JOIN transactions t ON lg.transaction_hash = t.hash
    WHERE lg.address = 'a2'
    GROUP BY lg.transaction_hash, t.block_number
)
SELECT
    t.hash,
    t.block_number,
    t.t_value,
    array_agg(JSON_BUILD_OBJECT('address', lg.address, 'l_value', lg.l_value, 'l_trans_block_number', lg.l_trans_block_number)) AS logs_array
FROM transactions t
JOIN ranked_tx rt ON t.hash = rt.transaction_hash
JOIN logs lg ON t.hash = lg.transaction_hash
WHERE rt.rn <= 2
GROUP BY t.hash, t.block_number, t.t_value
ORDER BY t.block_number DESC;

索引优化建议

创建logs(address, transaction_hash)复合索引，筛选地址时可直接获取交易ID，无需回表：

CREATE INDEX idx_logs_address_txhash ON logs(address, transaction_hash);

若logs表中按交易ID查询频繁，创建logs(transaction_hash)索引加速聚合查询：

CREATE INDEX idx_logs_txhash ON logs(transaction_hash);

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

PostgreSQL 12按地址查交易及全量日志并聚合的性能优化问题

环境信息 #

需求与表结构 #

预期结果 #

问题描述 #

真实场景执行情况 #

优化方案 #

方案1：先筛选指定数量的交易ID，再关联聚合日志 #

方案2：使用窗口函数筛选目标交易 #

索引优化建议 #