优化IP-域名关联PostgreSQL数据库及domain后缀模糊查询性能

这套查询的600-700ms耗时属于可快速优化的异常表现，核心问题并非数据规模过大，而是索引选型与查询遍历逻辑和当前业务场景不匹配。

核心性能瓶颈定位 #

当前查询94%的耗时来自domain字段上的gin_trgrm_ops GIN索引扫描：这类三元组模糊索引是为无固定边界的任意片段模糊匹配设计的，但你的查询需求是固定后缀的精确匹配，用通用模糊索引处理固定边界匹配，会产生大量无意义的随机IO，把本应亚毫秒级完成的索引扫描成本拉高了两个数量级。

可落地的优化方案 #

• 替换适配场景的索引选型，消除模糊索引的额外开销
  固定后缀匹配本质是反向前缀匹配，可以对domain字段做反向转换：生成存储/虚拟列存储reverse(domain)的反序域名值，为该列建立普通B-tree索引。原来的后缀匹配条件d.domain like '%.ford.com'可以等价改写为d.domain_reverse LIKE 'moc.drof.%'，B-tree索引对这种左锚定的前缀查询扫描效率远高于trigram GIN索引，仅这一项调整就可以把模糊匹配步骤的耗时压缩到原有水平的10%以内。
• 调整遍历顺序，适配keyset分页的早停特性
  当前查询是先全量扫描所有符合ford.com后缀的域名记录，再回表关联ips、按id倒序排序取前100条，相当于必须遍历完全部匹配数据才能得到分页结果。结合你的关联关系（domains通过ip_id关联ips.id），可以反过来从ips表按id倒序逐块扫描，通过domains表上已有的ip_id关联索引，快速判断每个扫描到的IP是否存在绑定ford.com后缀域名的记录，累计找到100个符合条件的IP就立刻终止扫描，完全不需要遍历全量3900万条域名数据。
  同时当前查询使用INNER JOIN匹配关联关系，会因为同一个IP绑定多个符合条件的域名产生冗余的重复结果，改用EXISTS做存在性判断，既可以避免结果去重的开销，也能让查询优化器更顺畅地选择早停的执行路径。
• 设计覆盖索引，消除回表随机IO
  调整后的查询可以把所有需要的列都纳入索引范围，避免扫描过程中回读原表数据：比如为domains表建立(domain_reverse, ip_id)的联合B-tree索引，同时覆盖匹配条件与关联键；为ips表建立(id, ip)的联合B-tree索引，同时覆盖排序键与需要返回的IP字段值，整个查询全程走索引的顺序扫描，无随机IO开销。

注意事项：替换索引选型时不要直接删除原有的trigram索引，先建好适配场景的新索引、验证查询性能达标后，再根据实际业务需要评估原有索引的留存价值，避免影响其他依赖模糊搜索的业务逻辑。

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