SQL query to find all parent records where child records are a specific set 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&lt;&gt;10 or c.value&lt;&gt;1000)
      and (c.type&lt;&gt;20 or c.value&lt;&gt;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&lt;&gt;10 or c.value&lt;&gt;1000)
      and (c.type&lt;&gt;20 or c.value&lt;&gt;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
The problem we are trying to solve is called exact relational division. It requires an extra condition from the relational division and there are still a lot (too many actually) ways to solve this kind of problem.

Check this related SO question where you will find more than 10 different ways to do this in Postgres and performance tests. The problem discussed there is (simple, not exact) relational division so you'll have to adjust the answers:

How to filter SQL results in a has-many-through relation

Here is another one:

select p.*
from item p 
  join 
    ( select item_id from item_detail 
      where (type, value) = (10, 1000)
      intersect
      select item_id from item_detail 
      where (type, value) = (20, 2000)
      except
      select item_id from item_detail 
      where (type, value) not in ((10, 1000), (20, 2000))
    ) as c
  on c.item_id = p.id ;


I suggest an index on (type, value, item_id).