postgresql 我需要创建什么索引?

gstyhher  于 2023-02-12  发布在  PostgreSQL
关注(0)|答案(1)|浏览(109)

我有查询,有时真的很慢,我怎样才能加快它?

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" >= 0
AND PRICES."price" <= 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 BRAND ASC
LIMIT 50

此查询Explain有解释说明
我不能在文本中添加解释,因为stackoverflow会抱怨代码量
添加了解释https://explain.depesz.com/s/4UAg
我尝试在RegionalWarehouseStockBalances上创建索引,但我的所有变体都没有帮助我我使用的是PostgreSQL 12

y1aodyip

y1aodyip1#

你得赶紧跑

VACUUM prices;

这样只有索引的扫描只有很少的"堆取数",这将使所有的区别。
减小该扫描床的autovacuum_vacuum_scale_factor,以便系统频繁地对扫描床进行真空吸尘。

相关问题