Linking Products to Users Via Categories I'm trying to match products to users where the users has the same set of categories as the product, here is the schema:

User
———
user_id
...

Users Categories 
———
user_id
category_id

Products Categories 
———
product_id
category_id

Product
———
product_id
disabled
...


As the the amount of products and users grow the matching query gets slower and slower, this is basically what we have right now:

SELECT *
FROM product
WHERE product.disabled = False
AND EXISTS (
    SELECT 1
    FROM products_categories
    WHERE products_categories.category_id = ANY(
        SELECT category_id
        FROM public.users_categories
        WHERE users_categories.user_id = 'some-user-id')
AND product.id = products_categories.product_id)


This seemed to work ok for some users but is horribly slow for others. This is the explain:

Nested Loop  (cost=4367.50..26918.74 rows=44498 width=1377) (actual 
time=694.695..2226.479 rows=87239 loops=1)
  InitPlan 1 (returns $0)
    -&gt;  HashAggregate  (cost=36.01..36.02 rows=1 width=16) (actual time=0.073..0.092 
    rows=18 loops=1)
          -&gt;  Bitmap Heap Scan on users_categories  (cost=4.49..35.99 rows=8 width=16) 
          (actual time=0.028..0.049 rows=18 loops=1)
                Recheck Cond: (user_id = 'some-user-id'::uuid)
                -&gt;  Bitmap Index Scan on ix_public_users_categories_user_id  
                (cost=0.00..4.49 rows=8 width=0) (actual time=0.014..0.014 rows=18 
                 loops=1)
                      Index Cond: (user_id = 'some-user-id'::uuid)
  -&gt;  HashAggregate  (cost=4331.06..4413.20 rows=8214 width=16) (actual 
  time=694.507..843.826 rows=121099 loops=1)
        -&gt;  Index Only Scan using products_categories_pkey on products_categories  
        (cost=0.43..4173.20 rows=63142 width=16) (actual time=0.295..387.771 
        rows=234490 loops=1)
              Index Cond: (category_id = ANY ($0))
              Heap Fetches: 117462
  -&gt;  Index Scan using product_pkey on product  (cost=0.42..2.73 rows=1 width=1377) 
  (actual time=0.007..0.008 rows=1 loops=121099)
        Index Cond: (id = products_categories.product_id)
        Filter: (NOT disabled)
        Rows Removed by Filter: 0
Total runtime: 2312.743 ms


Is there a better / faster way to perform queries like this? Im guessing I don't have my indexes set up correctly.
I'd imagine using JOINs rather than subqueries would be the first step to enhancing the speed of this thing.

Very rough example:

SELECT p.*
FROM product p
INNER JOIN products_categories pc ON p.product_id = pc.product_id
INNER JOIN public.users_categories c ON pc.category_id = c.category_id
WHERE p.disabled = False AND c.user_id = 'some-user-id'


Not gone into a lot of detail there - one of more of your JOINs might need to be an OUTER.