时间戳

首页 > 解决方案 > Postgres通过不使用子查询选择不同顺序的不同？

sql - Postgres通过不使用子查询选择不同顺序的不同？

问题描述

有很多关于如何做到这一点的答案涉及子查询，但对我们来说，子查询导致性能不足。似乎子查询的所有结果都是在外部查询执行之前确定的。

我们有一个数据库结构，其中Product包含一个Variant，其中包含一个Access（又名商店），并且在 postgres 上扩展它似乎特别烦人，因为不同的需要匹配我们（或任何在线购物的人）不想要的顺序. 该数据库甚至没有那么大，只有 2800 万行 Access' 和 15,000 个产品。如果没有子查询和适当的索引，性能非常好，在 10 毫秒内。使用子查询，它会在数千毫秒内结束（这是在 M.2 PCIe SSD 上）。

在这个例子中，我试图：

  1. 每个产品仅返回 1 行
  2. 满足 where 条件（位置，可能是其他东西）
  3. 确保我们首先展示库存产品
  4. 确保我们首先展示最昂贵的产品

该索引与此完全匹配。任何帮助是极大的赞赏。

SELECT 
  * 
FROM 
  (
    SELECT 
      DISTINCT ON ("products_displayproduct"."id") "products_displayproduct"."id", 
      "products_displayproduct"."date_created", 
      "products_displayproduct"."date_updated", 
      "products_displayproduct"."name", 
      "products_displayproduct"."sub_title", 
      "products_displayproduct"."tags", 
      "products_displayproduct"."has_multiple_variants", 
      "products_displayproduct"."placement_id", 
      "products_displayproduct"."hidden_placement_category_id", 
      "products_displayproduct"."hidden_placement_super_category_id", 
      "products_displayproduct"."brand_id", 
      "products_displayproduct"."poster_image_id", 
      "products_displayproduct"."rating", 
      "products_displayproduct"."reviews", 
      "products_displayproduct"."is_toppick", 
      "products_displayproduct"."last_amalgamation", 
      "products_displayproduct"."search_index", 
      "products_displayproductaccess"."is_instock" as "subquery_instock", 
      "products_displayproductaccess"."price" as "subquery_price" 
    FROM 
      "products_displayproduct" 
      INNER JOIN "products_displayproductvariant" ON (
        "products_displayproduct"."id" = "products_displayproductvariant"."product_id"
      ) 
      INNER JOIN "products_displayproductaccess" ON (
        "products_displayproductvariant"."id" = "products_displayproductaccess"."product_variant_id"
      ) 
    WHERE 
      (
        "products_displayproductaccess"."location_id" IN (
          608, 65, 610, 69, 999, 72, 92, 79, 81, 84, 
          1045, 89, 601, 954, 603, 1276, 605, 607
        )
      ) 
    ORDER BY 
      "products_displayproduct"."id" ASC NULLS LAST, 
      "products_displayproductaccess"."is_instock" DESC NULLS LAST, 
      "products_displayproductaccess"."price" DESC NULLS LAST
  ) as subquery 
ORDER BY 
  "subquery"."subquery_instock" DESC, 
  "subquery"."subquery_price" DESC
LIMIT 10

标签： sqlpostgresqlpostgresql-11

解决方案

我总是发现使用DISTINCT会极大地阻碍我的查询。尝试GROUP BY改用。在子查询中：

SELECT 
  * 
FROM 
  (
    SELECT 
      "products_displayproduct"."id", 
      "products_displayproduct"."date_created", 
      "products_displayproduct"."date_updated", 
      "products_displayproduct"."name", 
      "products_displayproduct"."sub_title", 
      "products_displayproduct"."tags", 
      "products_displayproduct"."has_multiple_variants", 
      "products_displayproduct"."placement_id", 
      "products_displayproduct"."hidden_placement_category_id", 
      "products_displayproduct"."hidden_placement_super_category_id", 
      "products_displayproduct"."brand_id", 
      "products_displayproduct"."poster_image_id", 
      "products_displayproduct"."rating", 
      "products_displayproduct"."reviews", 
      "products_displayproduct"."is_toppick", 
      "products_displayproduct"."last_amalgamation", 
      "products_displayproduct"."search_index", 
      "products_displayproductaccess"."is_instock" as "subquery_instock", 
      "products_displayproductaccess"."price" as "subquery_price" 
    FROM 
      "products_displayproduct" 
      INNER JOIN "products_displayproductvariant" ON (
        "products_displayproduct"."id" = "products_displayproductvariant"."product_id"
      ) 
      INNER JOIN "products_displayproductaccess" ON (
        "products_displayproductvariant"."id" = "products_displayproductaccess"."product_variant_id"
      ) 
    WHERE 
      (
        "products_displayproductaccess"."location_id" IN (
          608, 65, 610, 69, 999, 72, 92, 79, 81, 84, 
          1045, 89, 601, 954, 603, 1276, 605, 607
        )
      ) 
    GROUP BY 
      "products_displayproduct"."id"
    ORDER BY 
      "products_displayproduct"."id" ASC NULLS LAST, 
      "products_displayproductaccess"."is_instock" DESC NULLS LAST, 
      "products_displayproductaccess"."price" DESC NULLS LAST
  ) as subquery 
ORDER BY 
  "subquery"."subquery_instock" DESC, 
  "subquery"."subquery_price" DESC
LIMIT 10

或在您的主要查询中：

SELECT 
  * 
FROM 
  (
    SELECT 
      "products_displayproduct"."id" as "subquery_id", 
      "products_displayproduct"."date_created", 
      "products_displayproduct"."date_updated", 
      "products_displayproduct"."name", 
      "products_displayproduct"."sub_title", 
      "products_displayproduct"."tags", 
      "products_displayproduct"."has_multiple_variants", 
      "products_displayproduct"."placement_id", 
      "products_displayproduct"."hidden_placement_category_id", 
      "products_displayproduct"."hidden_placement_super_category_id", 
      "products_displayproduct"."brand_id", 
      "products_displayproduct"."poster_image_id", 
      "products_displayproduct"."rating", 
      "products_displayproduct"."reviews", 
      "products_displayproduct"."is_toppick", 
      "products_displayproduct"."last_amalgamation", 
      "products_displayproduct"."search_index", 
      "products_displayproductaccess"."is_instock" as "subquery_instock", 
      "products_displayproductaccess"."price" as "subquery_price" 
    FROM 
      "products_displayproduct" 
      INNER JOIN "products_displayproductvariant" ON (
        "products_displayproduct"."id" = "products_displayproductvariant"."product_id"
      ) 
      INNER JOIN "products_displayproductaccess" ON (
        "products_displayproductvariant"."id" = "products_displayproductaccess"."product_variant_id"
      ) 
    WHERE 
      (
        "products_displayproductaccess"."location_id" IN (
          608, 65, 610, 69, 999, 72, 92, 79, 81, 84, 
          1045, 89, 601, 954, 603, 1276, 605, 607
        )
      ) 
    ORDER BY 
      "products_displayproduct"."id" ASC NULLS LAST, 
      "products_displayproductaccess"."is_instock" DESC NULLS LAST, 
      "products_displayproductaccess"."price" DESC NULLS LAST
  ) as subquery 
GROUP BY 
  "subquery"."subquery_id"
ORDER BY 
  "subquery"."subquery_instock" DESC, 
  "subquery"."subquery_price" DESC
LIMIT 10

推荐阅读