An Item has many ItemDetails. An ItemDetail has fields of "type", "value" and "item_id".
I need to find all Items if and only if item has exact ItemDetails which are restricted by some changeable conditions. For example I need to find all Items with ItemDetails of (type=10, value=1000) and (type=20 and value=2000)
My first solution was like this:
select p.*
from item p
where not exists
(
select c.id from item_detail c
where c.item_id=p.id
and (c.type<>10 or c.value<>1000)
and (c.type<>20 or c.value<>2000)
);
-- Execution Time: 17.819 ms
But I realised that it was fetching items with only one ItemDetail(type=10, value=1000). Then I found this question and changed query like below.
select p.*
from item p
where not exists
(
select c.id from item_detail c
where c.item_id=p.id
and (c.type<>10 or c.value<>1000)
and (c.type<>20 or c.value<>2000)
)
and 2 = (
select count(c.item_id) from item_detail c
where c.item_id=p.id);
-- Execution Time: 2426.596 ms
But second subquery is causing performance problem. Execution time for first query is 5 ms, but for second it is 800 ms. Is there a better way of doing this?
I am using PostgreSQL 9.5.
Here is the fiddle for it.
Edit-1: Here is fiddle example for those who cannot reach it:
CREATE TABLE public.item
(
id integer NOT NULL,
name character varying(10) NOT NULL,
CONSTRAINT item_pkey PRIMARY KEY (id)
);
CREATE TABLE public.item_detail
(
id bigint NOT NULL,
item_id integer NOT NULL,
type integer NOT NULL,
value integer NOT NULL,
CONSTRAINT item_detail_pkey PRIMARY KEY (id),
CONSTRAINT fk_item_id FOREIGN KEY (item_id)
REFERENCES item (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT uq_item_type_value UNIQUE (item_id, type, value)
);
INSERT INTO public.item VALUES (1, 'Item1'),(2, 'Item2'),(3, 'Item3'),(4, 'Item4'),(5, 'Item5');
INSERT INTO public.item_detail
VALUES
(1,1,10,1000),
(2,1,20,2000),
(3,2,10,1000),
(4,3,10,1000),
(5,3,20,2000),
(6,3,30,3000),
(7,4,10,1000),
(8,4,10,1500);
Edit-2: I came up with a solution like this by choosing the best option for me from this source given by ypercubeᵀᴹ.
SELECT p.* FROM item p
WHERE EXISTS (SELECT item_id FROM item_detail
WHERE item_id = p.id AND (type, value) = (10, 1000))
AND EXISTS (SELECT item_id FROM item_detail
WHERE item_id = p.id AND (type, value) = (20, 2000))
AND NOT EXISTS (SELECT item_id FROM item_detail
WHERE item_id = p.id AND (type, value) NOT IN ((10, 1000), (20,2000)));
-- Execution Time: 0.984 ms