In the application I'm building, the user is able to define 'types' where each 'type' has a set of 'attributes'.
The user is able to create instances of products by defining a value for each attribute the product's type has.
A pic of the schema:
I'm creating the query where the user specifies the attributes values and the product type and with that I should return all the product id's that meets the query.
The problem I see in my query is that I'm performing a whole
select * from attributes_products ... for each attribute that the product's type has.
Is there a way to optimize this? If I create an index in the column attributes_products.product_id would this query be actually optimal?
Example of a query where I'm looking for a product whose type has 3 attributes:
select p.id from Products as p where exists( select * from attributes_products where product_id = p.id AND attribute_id = 27 AND value = 'some_value' ) AND exists( select * from attributes_products where product_id = p.id AND attribute_id = 28 AND value = 'other_value' ) AND exists( select * from attributes_products where product_id = p.id AND attribute_id = 29 AND value = 'oother_value' )
So, Gareth Rees (selected answer) proposed another solution which involves multiple Joins. Here is the explanation of its query (done by PGAdmin):
This is the explanation of the original query:
I believe that the selected answer is slightly faster, but consumes a lot more memory (because of the triple join). I believe that my original query is slightly slower (very slightly, since there's an index on the attributes_products table) but a lot more efficient in memory.