通过关联列过滤加速排序：NFT市场核心查询的性能优化需求

针对你提到的两个核心查询性能瓶颈，结合你的表规模（1亿条转移事件、5000万代币、2亿属性），我从Schema重构、索引优化和查询改写三个方向给出具体可落地的方案：

一、核心瓶颈分析 #

先拆解你的执行计划里的问题：

1. 指定合集的转移查询：数据库先从tokens表取出目标合集的所有代币（约1万条），再逐个通过nft_transfer_events的索引查询每个代币的转移事件，最后合并所有结果排序——这种嵌套循环在代币数量多的时候，时间会线性累加（你的例子里单条代币查询就耗时15ms，1万条就是150秒）。
2. 属性过滤的转移查询：逻辑类似，只是从token_attributes取符合条件的代币，再逐个查转移事件，同样存在多轮索引扫描的冗余开销。

本质问题是：查询需要跨多表关联才能过滤出目标转移事件，无法直接在转移事件表上完成过滤和排序。

二、Schema重构：冗余关键字段到转移事件表 #

最有效的优化是给nft_transfer_events添加collection_id字段，把合集信息直接冗余到转移事件中，彻底避免关联tokens表。

1. 修改表结构 #

ALTER TABLE nft_transfer_events ADD COLUMN collection_id TEXT;
-- 可选：添加外键约束确保数据一致性
ALTER TABLE nft_transfer_events ADD CONSTRAINT fk_nte_collection FOREIGN KEY (collection_id) REFERENCES collections(id);

2. 批量填充历史数据 #

UPDATE nft_transfer_events nte
SET collection_id = t.collection_id
FROM tokens t
WHERE nte.address = t.contract AND nte.token_id = t.token_id;

3. 自动维护新增数据的一致性 #

通过触发器自动填充collection_id，避免每次插入转移事件时手动关联：

CREATE OR REPLACE FUNCTION fill_nte_collection_id()
RETURNS TRIGGER AS $$
BEGIN
  SELECT collection_id INTO NEW.collection_id
  FROM tokens
  WHERE contract = NEW.address AND token_id = NEW.token_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_nte_collection_id
BEFORE INSERT ON nft_transfer_events
FOR EACH ROW EXECUTE FUNCTION fill_nte_collection_id();

三、索引优化 #

基于重构后的Schema，针对性添加索引：

1. 针对「指定合集的最新转移事件」查询 #

创建覆盖索引，让查询完全走索引无需回表：

CREATE INDEX idx_nte_collection_block_desc ON nft_transfer_events (collection_id, block DESC) INCLUDE (address, token_id, id);

此时你的第一个查询可以简化为：

SELECT * FROM nft_transfer_events
WHERE collection_id = '0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d'
ORDER BY block DESC LIMIT 20;

这个查询会直接扫描索引，返回结果的时间会降到毫秒级。

2. 针对「带属性过滤的合集转移事件」查询 #

保留token_attributes现有索引(collection_id, key, value, contract, token_id)，同时给nft_transfer_events添加索引(contract, token_id, block DESC)（如果还没有的话）。

然后通过查询改写减少关联的数据集：

-- 先从转移事件表取目标合集的最新N条（比如100条，确保能拿到20条符合属性的），再关联属性表过滤
SELECT nte.*
FROM (
  SELECT * 
  FROM nft_transfer_events
  WHERE collection_id = '0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d'
  ORDER BY block DESC 
  LIMIT 100
) nte
JOIN token_attributes ta 
  ON nte.address = ta.contract 
  AND nte.token_id = ta.token_id
WHERE ta.key = 'Fur' AND ta.value = 'Tan'
ORDER BY nte.block DESC 
LIMIT 20;

这种方式避免了扫描所有符合属性的代币的转移事件，只处理最新的一批转移记录，性能会大幅提升。

如果需要支持多属性过滤（比如同时过滤Fur=Tan和Eyes=Blue），可以复用现有索引多次关联：

SELECT nte.*
FROM (
  SELECT * 
  FROM nft_transfer_events
  WHERE collection_id = '0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d'
  ORDER BY block DESC 
  LIMIT 200
) nte
JOIN token_attributes ta1 
  ON nte.address = ta1.contract 
  AND nte.token_id = ta1.token_id
JOIN token_attributes ta2 
  ON nte.address = ta2.contract 
  AND nte.token_id = ta2.token_id
WHERE ta1.key = 'Fur' AND ta1.value = 'Tan'
  AND ta2.key = 'Eyes' AND ta2.value = 'Blue'
ORDER BY nte.block DESC 
LIMIT 20;

四、进阶优化：分区表 #

如果nft_transfer_events规模持续增长（超过1亿条），可以按block字段做范围分区，比如每100万区块分一个分区。这样查询最新转移事件时，数据库只会扫描最新的几个分区，进一步减少数据扫描范围：

-- 创建分区表
CREATE TABLE nft_transfer_events_partitioned (
  id BIGSERIAL,
  block INT NOT NULL,
  address BYTEA NOT NULL,
  token_id NUMERIC NOT NULL,
  collection_id TEXT
) PARTITION BY RANGE (block);

-- 创建初始分区（示例）
CREATE TABLE nft_transfer_events_block_0_1000000 PARTITION OF nft_transfer_events_partitioned FOR VALUES FROM (0) TO (1000000);
CREATE TABLE nft_transfer_events_block_1000000_2000000 PARTITION OF nft_transfer_events_partitioned FOR VALUES FROM (1000000) TO (2000000);
-- 后续可以用脚本自动创建新分区

五、其他小建议 #

• 定期清理旧数据：如果业务只需要最近的转移事件，可以归档旧分区，减少查询的数据量。
• 调整内存参数：增大PostgreSQL的work_mem（用于排序的内存），避免磁盘排序，你的执行计划里用了top-N heapsort，内存足够的话会更快。

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