Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl (
  name text
, value int
, id int
);

INSERT INTO tbl VALUES
  ('value1', 1, 100),
  ('value2', 2, 100),
  ('value1', 1, 200),
  ('value2', 3, 200),
  ('value1', 1, 300),
  ('value2', 4, 300);
  
CREATE INDEX foo ON tbl(name, value, id);

SET enable_seqscan = FALSE; -- to prefer index if at all possible
6 rows affected

-- Erwin's query
SELECT id
FROM  (SELECT id FROM tbl WHERE name = 'value1' AND value = 1) t1
JOIN  (SELECT id FROM tbl WHERE name = 'value2' AND value = 2) t2 USING (id);

id

100

-- equivalent (results in identical query plan)
SELECT id
FROM   tbl t1
JOIN   tbl t2 USING (id)
WHERE  t1.name = 'value1' AND t1.value = 1
AND    t2.name = 'value2' AND t2.value = 2;

id

100

EXPLAIN
SELECT id
FROM  (SELECT id FROM tbl WHERE name = 'value1' AND value = 1) t1
JOIN  (SELECT id FROM tbl WHERE name = 'value2' AND value = 2) t2 USING (id);

QUERY PLAN

Nested Loop  (cost=0.27..16.32 rows=1 width=4)

  Join Filter: (tbl.id = tbl_1.id)

  ->  Index Only Scan using foo on tbl  (cost=0.13..8.15 rows=1 width=4)

        Index Cond: ((name = 'value1'::text) AND (value = 1))

  ->  Index Only Scan using foo on tbl tbl_1  (cost=0.13..8.15 rows=1 width=4)

        Index Cond: ((name = 'value2'::text) AND (value = 2))

EXPLAIN
SELECT id
FROM   tbl t1
JOIN   tbl t2 USING (id)
WHERE  t1.name = 'value1' AND t1.value = 1
AND    t2.name = 'value2' AND t2.value = 2;

QUERY PLAN

Nested Loop  (cost=0.27..16.32 rows=1 width=4)

  Join Filter: (t1.id = t2.id)

  ->  Index Only Scan using foo on tbl t1  (cost=0.13..8.15 rows=1 width=4)

        Index Cond: ((name = 'value1'::text) AND (value = 1))

  ->  Index Only Scan using foo on tbl t2  (cost=0.13..8.15 rows=1 width=4)

        Index Cond: ((name = 'value2'::text) AND (value = 2))

-- Gordon's query
select id
from tbl
group by id
having count(*) filter (where name = 'value1' and value = 1) = 1 and
       count(*) filter (where name = 'value2' and value = 2) = 1 ;

id

100

EXPLAIN
select id
from tbl
group by id
having count(*) filter (where name = 'value1' and value = 1) = 1 and
       count(*) filter (where name = 'value2' and value = 2) = 1 ;

QUERY PLAN

GroupAggregate  (cost=12.30..12.51 rows=1 width=4)

  Group Key: id

  Filter: ((count(*) FILTER (WHERE ((name = 'value1'::text) AND (value = 1))) = 1) AND (count(*) FILTER (WHERE ((name = 'value2'::text) AND (value = 2))) = 1))

  ->  Sort  (cost=12.30..12.32 rows=6 width=40)

        Sort Key: id

        ->  Index Only Scan using foo on tbl  (cost=0.13..12.22 rows=6 width=40)

-- forpas' query
select id
from tbl
where (name, value) in (('value1', 1), ('value2', 2))
group by id
having count(distinct value) = 2 

id

100

EXPLAIN
select id
from tbl
where (name, value) in (('value1', 1), ('value2', 2))
group by id
having count(distinct value) = 2 

QUERY PLAN

GroupAggregate  (cost=12.29..12.31 rows=1 width=4)

  Group Key: id

  Filter: (count(DISTINCT value) = 2)

  ->  Sort  (cost=12.29..12.30 rows=1 width=8)

        Sort Key: id

        ->  Index Only Scan using foo on tbl  (cost=0.13..12.28 rows=1 width=8)

              Filter: (((name = 'value1'::text) AND (value = 1)) OR ((name = 'value2'::text) AND (value = 2)))