# Greedily select rows that match against rules for each group

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"?