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)
-> HashAggregate (cost=36.01..36.02 rows=1 width=16) (actual time=0.073..0.092 rows=18 loops=1)
-> 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)
-> 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)
-> HashAggregate (cost=4331.06..4413.20 rows=8214 width=16) (actual time=694.507..843.826 rows=121099 loops=1)
-> 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
-> 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.
Thanks for any help :)
Chris
Aucun commentaire:
Enregistrer un commentaire