Optimizing Postgres Count For Aggregated Select

Question

I have a query that is intended to retrieve the counts of each grouped product like so

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

This query is run on the following table

       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)

How can I improve the performance of this query, specifically the COUNT(*) portion, which when removed improves the query but is requisite?

userQZpQ · Answer

You may try using an INNER JOIN in place of a WHERE ... IN clause.

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

Then create an index on the "product.id" field as follows:

CREATE INDEX product_ids_idx ON product USING HASH (id);

Optimizing Postgres Count For Aggregated Select

1 Answers