PostgreSQL 12慢查询优化：需创建何种索引提升查询效率？

一、针对性索引创建建议 #

结合查询逻辑和执行计划分析，以下索引可直接解决查询偶尔卡顿的问题：

1. Products表索引
   针对active = TRUE过滤条件和ORDER BY BRAND排序需求，创建复合覆盖索引，让数据库直接按品牌有序读取活跃产品，避免全表扫描后再排序：

   CREATE INDEX idx_products_active_brand_id ON PUBLIC."Products" ("active", "BRAND", "ID");
   

   若想进一步减少回表开销，可扩展为包含索引，覆盖SELECT中需要的所有字段：

   CREATE INDEX idx_products_active_brand_include ON PUBLIC."Products" ("active", "BRAND")
   INCLUDE ("ID", "OEM", "NAME", "minShippingRate", "externalId", "ARTICLE", "categoryId");
   

2. Prices表索引
   针对accountId = 13576、productId关联及价格范围过滤，创建复合索引快速定位符合条件的价格记录：

   CREATE INDEX idx_prices_account_product_price ON PUBLIC."Prices" ("accountId", "productId", "price");
   

3. RegionalWarehouseStockBalances表索引
   之前创建的索引未生效，核心原因是索引结构未匹配查询条件。针对warehouseId = 1、productId关联及stockBalance > 0过滤，创建部分复合索引（仅包含库存大于0的记录，索引体积更小、查询更快）：

   CREATE INDEX idx_rwsb_warehouse_product_stock ON PUBLIC."RegionalWarehouseStockBalances" ("warehouseId", "productId")
   WHERE "stockBalance" > 0;
   

4. SpecialProductGroups表索引
   针对accountId = 13576、productGroupId关联及finishedAt > NOW()过滤，创建复合索引：

   CREATE INDEX idx_spg_account_group_finished ON PUBLIC."SpecialProductGroups" ("accountId", "productGroupId", "finishedAt", "id");
   

二、查询语句优化调整 #

1. 明确排序字段归属
   将ORDER BY BRAND改为ORDER BY PRODUCTS."BRAND"，避免字段歧义，帮助优化器更精准识别可用索引：

   SELECT PRODUCTS.ID,
       SPECIALPRODUCTGROUPS."id" AS "isProductGroup",
       PRODUCTS."OEM",
       PRODUCTS.NAME,
       MAIN."stockBalance" AS STOCKBALANCE,
       PRODUCTS."minShippingRate",
       PRODUCTS."externalId",
       ARTICLE,
       "categoryId",
       BRAND,
       PRICES."price" AS "price"
   FROM PUBLIC."Products" AS PRODUCTS
   INNER JOIN PUBLIC."Prices" AS PRICES ON PRODUCTS.ID = PRICES."productId"
   AND PRICES."accountId" = 13576
   AND PRICES."price" BETWEEN 0 AND 337802
   INNER JOIN PUBLIC."RegionalWarehouseStockBalances" AS MAIN ON PRODUCTS.ID = MAIN."productId"
   AND MAIN."warehouseId" = 1
   AND MAIN."stockBalance" > 0
   LEFT JOIN PUBLIC."SpecialProductGroups" AS SPECIALPRODUCTGROUPS ON PRODUCTS."productGroupId" = SPECIALPRODUCTGROUPS."productGroupId"
   AND SPECIALPRODUCTGROUPS."accountId" = 13576
   AND NOW() < SPECIALPRODUCTGROUPS."finishedAt"
   WHERE PRODUCTS."active" = TRUE
   ORDER BY PRODUCTS."BRAND" ASC
   LIMIT 50;
   

2. 更新统计信息
   若数据库统计信息过时，优化器可能生成低效执行计划，运行以下命令更新：

   ANALYZE PUBLIC."Products";
   ANALYZE PUBLIC."Prices";
   ANALYZE PUBLIC."RegionalWarehouseStockBalances";
   ANALYZE PUBLIC."SpecialProductGroups";
   

三、效果验证 #

创建索引后重新执行查询，查看执行计划确认：

• Products表使用idx_products_active_brand_id索引进行有序扫描（而非全表扫描）
• 各Join操作均利用对应索引快速匹配记录
• Sort操作的代价大幅降低或消失（因已从索引获取有序数据）

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