我有查询,有时真的很慢,我怎样才能加快它?
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
1条答案
按热度按时间y1aodyip1#
你得赶紧跑
这样只有索引的扫描只有很少的"堆取数",这将使所有的区别。
减小该扫描床的
autovacuum_vacuum_scale_factor
,以便系统频繁地对扫描床进行真空吸尘。