1

Suppose there's a product table with the following fields

id (pk), category (index), name, picture, stock

and we want to find ONE product in each of these category (1, 2, 3) that match at least one of the predefined rules in a way that, if there're products that match the first rule, we should pick one of them; otherwise we match against the second rule, and so on.

For instance, suppose we have rules

  1. picture IS NOT NULL AND stock > 0
  2. stock > 0
  3. picture IS NOT NULL
  4. otherwise

Currently I've figured out two solutions

1.

SELECT DISTINCT ON (category) id, category, name, picture, stock,
  CASE
    WHEN (picture IS NOT NULL AND stock > 0) THEN 0
    WHEN (stock > 0) THEN 1
    WHEN (picture IS NOT NULL) THEN 2
    ELSE 3
  END AS score
FROM product
WHERE category = ANY('{1, 2, 3}') AND deleted_at IS NULL
ORDER BY category, score
                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=10.98..11.00 rows=4 width=84)
   ->  Sort  (cost=10.98..10.99 rows=4 width=84)
         Sort Key: category, (CASE WHEN (picture IS NOT NULL AND stock > 0) THEN 0 WHEN (stock > 0) THEN 1 WHEN (picture IS NOT NULL) THEN 2 ELSE 3 END)
         ->  Seq Scan on product  (cost=0.00..10.94 rows=4 width=84)
               Filter: ((deleted_at IS NULL) AND (category = ANY ('{1,2,3}'::bigint[])))

(I believe Seq Scan is used here because there're too few records in the table)

2.

WITH cte (category) AS (
  SELECT * FROM unnest('{1, 2, 3}'::BIGINT[])
)
SELECT * FROM cte
JOIN LATERAL (
  SELECT id, category, name, picture, stock,
    CASE
      WHEN (picture IS NOT NULL AND stock > 0) THEN 0
      WHEN (stock > 0) THEN 1
      WHEN (picture IS NOT NULL) THEN 2
      ELSE 3
    END AS score
  FROM product
  WHERE category = cte.category
  ORDER BY score
  LIMIT 1
) p ON true
                                                                                          QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=9.88..39.63 rows=4 width=92)
   ->  Function Scan on unnest  (cost=0.00..0.04 rows=4 width=8)
   ->  Limit  (cost=9.87..9.88 rows=1 width=84)
         ->  Sort  (cost=9.87..9.88 rows=3 width=84)
               Sort Key: (CASE WHEN (picture IS NOT NULL AND stock > 0) THEN 0 WHEN (stock > 0) THEN 1 WHEN (picture IS NOT NULL) THEN 2 ELSE 3 END)
               ->  Bitmap Heap Scan on product  (cost=4.17..9.86 rows=3 width=84)
                     Recheck Cond: (category = unnest.unnest)
                     Filter: (deleted_at IS NULL)
                     ->  Bitmap Index Scan on product_category_idx  (cost=0.00..4.17 rows=3 width=0)
                           Index Cond: (category = unnest.unnest)

My questions are

  1. Is the second solution faster than the first one (based on my understanding, the second solution sorts rows locally for each group, so it would be faster than the first one that sorts globally).
  2. Currently, we encode rules into the integer space, so that we must first fetch all rows in those categories before sorting. How do we improve the query performance with the concept that "if we find a record that matches the first rule, we don't need to continue the search"?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.