perf(search): 浏览路径部分复合索引 (0012) #27

2026-06-24T13:33:47+08:00

lixu commented

2026-06-24 13:33:47 +08:00

Summary

可扩展性路线图「阶段0」首个落地项:为最常见的浏览检索路径补部分复合索引(新增迁移 0012,纯附加,不改 API/代码/schema 结构)。

搜索查询恒带 WHERE status='active';无关键词时 ORDER BY quality_score DESC, name。此前没有能同时满足"过滤 active + 按该顺序"的索引,深翻页(大 OFFSET)需对全部 active 行做 Seq Scan + Sort。新增:

CREATE INDEX idx_product_active_quality
  ON product (quality_score DESC, name) WHERE status='active';
CREATE INDEX idx_product_active_category
  ON product (category_id, quality_score DESC) WHERE status='active';

部分索引只覆盖 active 行(体积更小)。规划器在数据量大时改走 索引顺序扫描 + 对 LIMIT 行 nested-loop join brand/category 主键,省掉全表排序——这正是深翻页的瓶颈。

验证:本地起 postgres:16,顺序跑完 0001→0012 全部 up;0012 down/re-up 可逆;EXPLAIN 确认浏览查询命中 idx_product_active_quality(排序代价高时走 Index Scan + Nested Loop,小数据量仍按代价回退 seqscan,符合预期)。

## Summary 可扩展性路线图「阶段0」首个落地项:为最常见的**浏览检索路径**补部分复合索引(新增迁移 0012,纯附加,不改 API/代码/schema 结构)。搜索查询恒带 `WHERE status='active'`;无关键词时 `ORDER BY quality_score DESC, name`。此前没有能同时满足"过滤 active + 按该顺序"的索引,深翻页(大 OFFSET)需对全部 active 行做 Seq Scan + Sort。新增: ```sql CREATE INDEX idx_product_active_quality ON product (quality_score DESC, name) WHERE status='active'; CREATE INDEX idx_product_active_category ON product (category_id, quality_score DESC) WHERE status='active'; ``` 部分索引只覆盖 active 行(体积更小)。规划器在数据量大时改走 **索引顺序扫描 + 对 LIMIT 行 nested-loop join brand/category 主键**,省掉全表排序——这正是深翻页的瓶颈。验证:本地起 postgres:16,顺序跑完 0001→0012 全部 up;0012 down/re-up 可逆;`EXPLAIN` 确认浏览查询命中 `idx_product_active_quality`(排序代价高时走 Index Scan + Nested Loop,小数据量仍按代价回退 seqscan,符合预期)。

lixu added 1 commit 2026-06-24 13:33:47 +08:00

perf(search): 为浏览路径补部分复合索引 (0012)

CI / Go (api) (pull_request) Successful in 58s

Details

CI / Python (ingestion) (pull_request) Failing after 21s

Details

CI / Migrations (postgres) (pull_request) Successful in 32s

Details

ab7a964934

Co-Authored-By: Devin AI <158243242+devin-ai-integration[bot]@users.noreply.github.com>

lixu merged commit 936828abae into main

2026-06-24 13:35:51 +08:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: lixu/goods#27