Postgres大表关联分组查询优化：代币最高出价计算效率提升方案

问题根源 #

当前查询性能瓶颈来自嵌套循环产生的超量无效中间结果：对单合约下的1万多个token，每个都需要和1.2万个有效区间出价逐行判断token_id是否在区间范围内，中间产生了1.4亿条被过滤的无效数据，99%的执行时间都消耗在这一步。

零改造成本优化方案（仅改索引+查询） #

1. 优化buy_orders索引 #

原有索引仅包含区间字段，没有覆盖常用过滤条件，需要新增复合索引避免回表，同时缩小索引扫描范围：

-- 过滤掉已取消、已成交的订单，仅保留有效订单的索引，同时覆盖价格字段不需要回表
CREATE INDEX idx_buy_orders_active_interval ON buy_orders (contract, start_token_id, end_token_id DESC, price DESC)
INCLUDE (valid_between)
WHERE NOT cancelled AND NOT executed;

如果你的Postgres版本支持generated列，还可以新增预生成的范围字段加GIST索引，进一步加速区间匹配：

-- 预生成token_id的闭区间字段
ALTER TABLE buy_orders ADD COLUMN token_id_range NUMRANGE GENERATED ALWAYS AS (numrange(start_token_id, end_token_id, '[]')) STORED;
-- 建GIST索引专门优化范围包含查询
CREATE INDEX idx_buy_orders_token_range ON buy_orders USING GIST (contract, token_id_range)
WHERE NOT cancelled AND NOT executed;

2. 改写查询分批处理 #

不要一次性扫描全量代币，按token_id排序分批计算，每次处理100~1000条，避免中间结果爆炸：

-- 每次处理1000条代币的top_bid，调整OFFSET实现分页扫描
WITH batch_tokens AS (
  SELECT contract, token_id 
  FROM tokens 
  WHERE contract = 'foo'
  ORDER BY token_id
  LIMIT 1000 OFFSET 0
)
SELECT bt.contract, bt.token_id, COALESCE(MAX(b.price), 0) AS top_bid
FROM batch_tokens bt
LEFT JOIN buy_orders b
  ON b.contract = bt.contract
  AND b.token_id_range @> bt.token_id
  AND b.valid_between @> now()
GROUP BY bt.contract, bt.token_id;

改完后单批1000条的查询耗时可以降到100ms以内。

长期架构优化方案（适合大流量场景） #

1. 触发式增量更新代替全量重算 #

不需要每次都全量扫描所有出价计算top_bid，而是在出价状态变更时主动更新对应代币的top_bid：

• 新增类型1出价时，直接执行范围更新：UPDATE tokens SET top_bid = GREATEST(top_bid, $new_price) WHERE contract = $contract AND token_id BETWEEN $start_id AND $end_id，依托tokens表的主键索引，几十万条数据的更新仅需数百毫秒
• 出价取消/过期/成交时，仅当该出价的价格等于对应代币的当前top_bid时，才重新计算该代币的最高出价，不需要全量重算

2. 出价类型分表存储 #

不要用同一张表反范式兼容两类出价：

• 类型1区间出价单独存表，专门用range索引优化
• 类型2列表出价走现有token_lists关联逻辑
  查询时分别取两类出价的最高价格再合并，避免混查的性能损耗。

3. 异步队列处理超大范围更新 #

如果单次出价覆盖的代币数量超过10万，直接扔到后台异步队列分批更新，前端接受秒级最终一致性即可，不需要阻塞同步更新。

内容的提问来源于stack exchange，提问作者George R.