Using PostgreSQL (currently 9.6, but upgrades are possible), I currently have the following database layout where customers can order products, which are themselves sorted into categories (products may be in multiple categories):
Orders id -- PRIMARY KEY customer_id -- FOREIGN KEY (Customer - id) product_id -> FOREIGN KEY (Product - id) Products id -- PRIMARY KEY Categories id -- PRIMARY KEY Product_Categories product_id -- FOREIGN KEY (Product - id) category_id -- FOREIGN KEY (Category - id)
Now, I have a fairly large amount of orders (~30M) and a reasonable number of categories (~1K) and customers (~10K). There is around 30K Products, with an average of 3 products by category. Products may be moved from a category to another occasionally (let's say a once per month shuffle)
My problem is that I want to have the following type of query run fast: "Get all Orders for customer whose product is in Category C". That would look like:
SELECT * FROM Orders JOIN Product_Categories ON Orders.product_id = Product_Categories.product_id WHERE Orders.customer_id = X AND Product_Categories.category_id = Y
The best index I can think of is an index on
customer_id in Orders, supported by a secondary index on
Product_Categories.product_id. This leads to the following plan (not a real plan since the design I showed above is a very large simplification of the actual case):
- Index Scan on Orders using index on customer_id ---> Returns ~10K Rows - 10K Joins done by Index Lookup on the product_id index of Product_Categories (MAIN TIME CONSUMER) - 9990 Rows Filtered Out. - 10 Rows Returned
I would like to have an index over
(customer_id, category_id), but I haven't been able to find a way to do this. The best solutions I can think of is to add a column
categories_id INTEGER and then either:
- Add a GIN index using
customer_idwith the inclusion in list operator.
- Create 1000 Partial indexes on
In both cases, I would have to synchronize
categories_id with the updates in the
product association tables, which is unfortunate.
My questions are:
- Am I overthinking? Is the "filtering out 10k" rows not that bad of a problem and any solution I can think of will make the problem worse?
- Am I missing something? Can I be efficient without changing my database schema?
- Assuming I should change my database schema, what is the best way to do so?