mercredi 25 février 2015

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)
-> 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