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

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

StackExchange Database Administrators Q#161433, answer score: 4