查询效率

Question

这个看似简单的查询(1 join)需要许多小时才能运行，即使该表包含的行少于150万行.

我有Product项，它与RetailerProduct项有一对多的关系，我希望找到所有与RetailerProducts相关的RetailerProducts不包含任何retailer_id=1实例的Product项。

产品中大约有150万行，在RetailerProduct中有大约110万行retailer_id=1 (在RetailerProduct中总共有290万行)

型号：

class Product(models.Model):
    ...
    upc = models.CharField(max_length=96, unique=True)
    ...

class RetailerProduct(models.Model):
    ...
    product = models.ForeignKey('project.Product',
                                related_name='retailer_offerings',
                                on_delete=models.CASCADE,
                                null=True)
    ...

    class Meta:
        unique_together = (("retailer", "retailer_product_id", "retailer_sku"),)

查询：

Product.objects.exclude(
   retailer_offerings__retailer_id=1).values_list('upc', flat=True)

生成的SQL：

SELECT "project_product"."upc" FROM "project_product" 
 WHERE NOT ("project_product"."id" IN 
  (SELECT U1."product_id" AS Col1 FROM "project_retailerproduct" U1 
    WHERE (U1."retailer_id" = 1 AND U1."product_id" IS NOT NULL))
  )

运行该查询需要几个小时。psql shell中的解释呈现：

 QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Seq Scan on project_product  (cost=0.00..287784596160.17 rows=725892 width=13)
   Filter: (NOT (SubPlan 1))
   SubPlan 1
     ->  Materialize  (cost=0.00..393961.19 rows=998211 width=4)
           ->  Seq Scan on project_retailerproduct u1  (cost=0.00..385070.14 rows=998211 width=4)
                 Filter: ((product_id IS NOT NULL) AND (retailer_id = 1))
(6 rows)

我想发解释分析，但它还在运行。

为什么Seq Scan on project_product的成本这么高？有优化建议吗？

Answer 1

RetailerProduct中有110万行，retailer_id=1为290万行(RetailerProduct为290万行)

在290万行中选择110万行。即使您在retailer_id上有一个索引，它在这里也不会有多大用处。你看的是这张桌子的一半。这需要一个完整的表格扫描。

然后，让我们回顾一下，非IN类型查询通常比较慢。在您的示例中，您将product_id列与110万行进行比较。这样做之后，您实际上正在获取行，这可能相当于几十万行。您可能需要考虑一个限制，但即使这样，查询速度也不会快得多。

因此，这不是一个可以轻松优化的查询。您可能需要使用完全不同的查询。下面是一个原始查询示例

SELECT "project_product"."upc" FROM "project_product" LEFT JOIN
 (SELECT product_id FROM "project_retailerproduct" 
  WHERE retailer_id = 1)
AS retailer 
ON project_product.id = retailer.product_id WHERE
WHERE retailer.product_id IS NULL