gpt4 book ai didi

sql - 使用多个连接、排序和小限制优化 PostgreSQL 查询

转载 作者:行者123 更新时间:2023-11-29 11:30:06 25 4
gpt4 key购买 nike

我需要一些技巧来优化从大表中提取的查询。

在这个例子中我有 5 个表:

Brands
- id_brand
- b_name

Products
- id_product
- p_name
- ean
...
- fk_brand

Prod_attributes
- id_prod_att
- size_basic
...
- fk_product

Stores
- id_store
- s_name
...

Stocks
- id_stock
- stock_amount
- fk_prod_att
- fk_store

我需要一个有限的有序股票列表查询,所以这是我使用的一般方法:

SELECT stores.s_name, stocks.stock_amount, prod_attributes.size_basic, 
products.p_name, products.ean, brands.b_name

FROM (stocks
INNER JOIN stores
ON stocks.fk_store = stores.id_store)
INNER JOIN (prod_attributes
INNER JOIN (products
INNER JOIN brands
ON products.fk_brand = brands.id_brand)
ON prod_attributes.fk_product = products.id_product)
ON stocks.fk_prod_att = prod_attributes.id_prod_att

ORDER BY s_name, p_name, size_basic

LIMIT 25 OFFSET 0

这在小表上运行很快,但是当表变大时查询变得非常昂贵。 Stocks 中有 350 万行,Prod_attributes 中有 300K 行,Prod_attributes 中有 25K 行,它的执行时间超过 8800 毫秒,这对我来说是无法接受的。

所有 forgein 键都有索引,最近对 DB 进行了真空分析。

我知道问题出在 ORDER BY 部分,因为它查询不使用索引并进行顺序扫描。如果我删除排序,那么查询会非常快。

为了解决这个问题,我知道我可以删除 ORDER BY,但这对我来说不是可行的选择。数据库或物化 View 的非规范化在这里也有帮助——我再次希望尽可能避免这种情况。

我还能做些什么来加快查询速度?

解释分析:
- 订单慢:http://explain.depesz.com/s/AHO
- 快速无订单:http://explain.depesz.com/s/NRxr

最佳答案

一种可能的方法是从连接中删除 stores。相反,您可以:

  • 在存储过程或源代码中遍历 stores(按 s_name 排序),并针对每个商店在 上执行连接过滤股票.fk_store。只要获得足够数量的记录,就可以中断循环。

  • 如果可能,使用 fk_store 键对 stocks 进行分区,以大量减少连接中的元组数量。

这样你应该会有很好的 yield 。

关于sql - 使用多个连接、排序和小限制优化 PostgreSQL 查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13270848/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com