优化聚合选择的Postgres计数

trnvg8h3 于 2022-09-21 发布在 PostgreSQL

关注(0)|答案(1)|浏览(158)

我有一个旨在检索每个分组产品的计数的查询，如下所示

SELECT
  product_name,
  product_color,
  (array_agg("product_distributor"))[1] AS "product_distributor",
  (array_agg("product_release"))[1] AS "product_release",
  COUNT(*) AS "count"
FROM
  product
WHERE
  product.id IN (
    SELECT
      id
    FROM
      product
    WHERE
      (product_name ilike "%red%"
       OR product_color ilike "%red%")
      AND product_type = 1)
GROUP BY
  product_name, product_color
LIMIT
  1000
OFFSET
  0

此查询针对下表运行

Column        |           Type           | Collation | Nullable | Default 
---------------------+--------------------------+-----------+----------+---------
 product_type        | integer                  |           | not null | 
 id                  | integer                  |           | not null | 
 product_name        | citext                   |           | not null | 
 product_color       | character varying(255)   |           |          |
 product_distributor | integer                  |           |          | 
 product_release     | timestamp with time zone |           |          | 
 created_at          | timestamp with time zone |           | not null | 
 updated_at          | timestamp with time zone |           | not null |
Indexes:
    "product_pkey" PRIMARY KEY, btree (id)
    "product_distributer_index" btree (product_distributor)
    "product_product_type_name_color" UNIQUE, btree (product_type, name, color)
    "product_product_type_index" btree (product_type)
    "product_name_color_index" btree (name, color)
Foreign-key constraints:
    "product_product_type_fkey" FOREIGN KEY (product_type) REFERENCES product_type(id) ON UPDATE CASCADE ON DELETE CASCADE
    "product_product_distributor_id" FOREIGN KEY (product_distributor) REFERENCES product_distributor(id)

我如何改进这个查询的性能，特别是count(*)部分，它在被删除时会改进查询，但这是必需的？

postgresql

来源：https://stackoverflow.com/questions/73790098/optimizing-postgres-count-for-aggregated-select

1条答案

按热度按时间

mgdq6dx11#

您可以尝试使用INNER JOIN代替WHERE ... IN子句。

WITH selected_products AS (
    SELECT id
    FROM product
    WHERE (product_name ilike "%red%" OR product_color ilike "%red%")
      AND product_type = 1
)
SELECT product_name,
       product_color,
       (ARRAY_AGG("product_distributor"))[1] AS "product_distributor",
       (ARRAY_AGG("product_release"))[1]     AS "product_release",
       COUNT(*) AS "count"
FROM       product p
INNER JOIN selected_products sp
        ON p.id = sp.id
GROUP BY product_name, 
         product_color
LIMIT 1000 
OFFSET 0

然后在“Product.id”字段上创建索引，如下所示：

CREATE INDEX product_ids_idx ON product USING HASH (id);

赞(0）回复(0）举报 2022-09-21

我来回答

优化聚合选择的Postgres计数

1条答案

相关问题

热门标签

最新问答