clear markdown compare help donate comments/suggestions/bugs a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith?
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 363594 distinct fiddles created so far.

create table test ( id integer, pid integer, name varchar(2), val integer ); create index ix_test__pid on test(pid); create index ix_test__name_val on test(name,val); insert into test (id, pid, name, val) values ('1', '1', 'aa', '10'), ('2', '1', 'bb', '20'), ('3', '1', 'cc', '30'), ('4', '2', 'aa', '10'), ('5', '2', 'bb', '20'), ('6', '2', 'cc', '30'), ('7', '3', 'aa', '10'), ('8', '3', 'bb', '20'), ('9', '3', 'cc', '999');
9 rows affected
 hidden batch(es)


explain (analyze,buffers) select distinct pid from test where pid in (select pid from test where (name,val) = ('aa',10)) and pid in (select pid from test where (name,val) = ('bb',20)) and pid in (select pid from test where (name,val) = ('cc',30));
QUERY PLAN
HashAggregate (cost=49.13..51.09 rows=196 width=4) (actual time=0.215..0.216 rows=2 loops=1)
Group Key: test.pid
Buffers: shared hit=12
-> Hash Semi Join (cost=28.76..48.64 rows=196 width=4) (actual time=0.173..0.208 rows=6 loops=1)
Hash Cond: (test.pid = test_3.pid)
Buffers: shared hit=12
-> Hash Semi Join (cost=20.57..37.24 rows=392 width=12) (actual time=0.107..0.138 rows=9 loops=1)
Hash Cond: (test.pid = test_2.pid)
Buffers: shared hit=10
-> Nested Loop (cost=12.39..22.64 rows=785 width=8) (actual time=0.045..0.071 rows=9 loops=1)
Buffers: shared hit=8
-> HashAggregate (cost=8.17..8.18 rows=1 width=4) (actual time=0.021..0.022 rows=3 loops=1)
Group Key: test_1.pid
Buffers: shared hit=2
-> Index Scan using ix_test__name_val on test test_1 (cost=0.15..8.17 rows=1 width=4) (actual time=0.014..0.015 rows=3 loops=1)
Index Cond: (((name)::text = 'aa'::text) AND (val = 10))
Buffers: shared hit=2
-> Bitmap Heap Scan on test (cost=4.21..14.37 rows=8 width=4) (actual time=0.009..0.010 rows=3 loops=3)
Recheck Cond: (pid = test_1.pid)
Heap Blocks: exact=3
Buffers: shared hit=6
-> Bitmap Index Scan on ix_test__pid (cost=0.00..4.21 rows=8 width=0) (actual time=0.004..0.004 rows=3 loops=3)
Index Cond: (pid = test_1.pid)
Buffers: shared hit=3
-> Hash (cost=8.17..8.17 rows=1 width=4) (actual time=0.033..0.033 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=2
-> Index Scan using ix_test__name_val on test test_2 (cost=0.15..8.17 rows=1 width=4) (actual time=0.020..0.023 rows=3 loops=1)
Index Cond: (((name)::text = 'bb'::text) AND (val = 20))
Buffers: shared hit=2
-> Hash (cost=8.17..8.17 rows=1 width=4) (actual time=0.036..0.036 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=2
-> Index Scan using ix_test__name_val on test test_3 (cost=0.15..8.17 rows=1 width=4) (actual time=0.023..0.026 rows=2 loops=1)
Index Cond: (((name)::text = 'cc'::text) AND (val = 30))
Buffers: shared hit=2
Planning Time: 1.305 ms
Execution Time: 0.494 ms
 hidden batch(es)


-- works on all RDBMS explain (analyze,buffers) select pid from test where (name,val) = ('aa',10) and pid in ( select pid from test where (name,val) = ('bb',20) and pid in ( select pid from test where (name,val) = ('cc',30) ) );
QUERY PLAN
Nested Loop Semi Join (cost=0.46..24.54 rows=1 width=4) (actual time=0.089..0.104 rows=2 loops=1)
Join Filter: (test.pid = test_1.pid)
Rows Removed by Join Filter: 3
Buffers: shared hit=24
-> Index Scan using ix_test__name_val on test (cost=0.15..8.17 rows=1 width=4) (actual time=0.018..0.019 rows=3 loops=1)
Index Cond: (((name)::text = 'aa'::text) AND (val = 10))
Buffers: shared hit=2
-> Nested Loop Semi Join (cost=0.30..16.36 rows=1 width=8) (actual time=0.024..0.026 rows=2 loops=3)
Join Filter: (test_1.pid = test_2.pid)
Rows Removed by Join Filter: 3
Buffers: shared hit=22
-> Index Scan using ix_test__name_val on test test_1 (cost=0.15..8.17 rows=1 width=4) (actual time=0.006..0.007 rows=3 loops=3)
Index Cond: (((name)::text = 'bb'::text) AND (val = 20))
Buffers: shared hit=6
-> Index Scan using ix_test__name_val on test test_2 (cost=0.15..8.17 rows=1 width=4) (actual time=0.004..0.005 rows=2 loops=8)
Index Cond: (((name)::text = 'cc'::text) AND (val = 30))
Buffers: shared hit=16
Planning Time: 0.428 ms
Execution Time: 0.154 ms
 hidden batch(es)


-- works on most RDBMS, MySQL has no INTERSECT explain (analyze,buffers) select pid from test where (name,val) = ('aa',10) intersect select pid from test where (name,val) = ('bb',20) intersect select pid from test where (name,val) = ('cc',30);
QUERY PLAN
HashSetOp Intersect (cost=0.15..24.59 rows=1 width=8) (actual time=0.057..0.057 rows=2 loops=1)
Buffers: shared hit=6
-> Append (cost=0.15..24.58 rows=2 width=8) (actual time=0.033..0.047 rows=5 loops=1)
Buffers: shared hit=6
-> Result (cost=0.15..16.39 rows=1 width=8) (actual time=0.033..0.034 rows=3 loops=1)
Buffers: shared hit=4
-> HashSetOp Intersect (cost=0.15..16.38 rows=1 width=8) (actual time=0.031..0.032 rows=3 loops=1)
Buffers: shared hit=4
-> Append (cost=0.15..16.38 rows=2 width=8) (actual time=0.015..0.026 rows=6 loops=1)
Buffers: shared hit=4
-> Subquery Scan on "*SELECT* 1" (cost=0.15..8.18 rows=1 width=8) (actual time=0.014..0.017 rows=3 loops=1)
Buffers: shared hit=2
-> Index Scan using ix_test__name_val on test (cost=0.15..8.17 rows=1 width=4) (actual time=0.013..0.015 rows=3 loops=1)
Index Cond: (((name)::text = 'aa'::text) AND (val = 10))
Buffers: shared hit=2
-> Subquery Scan on "*SELECT* 2" (cost=0.15..8.18 rows=1 width=8) (actual time=0.005..0.007 rows=3 loops=1)
Buffers: shared hit=2
-> Index Scan using ix_test__name_val on test test_1 (cost=0.15..8.17 rows=1 width=4) (actual time=0.005..0.006 rows=3 loops=1)
Index Cond: (((name)::text = 'bb'::text) AND (val = 20))
Buffers: shared hit=2
-> Subquery Scan on "*SELECT* 3" (cost=0.15..8.18 rows=1 width=8) (actual time=0.009..0.010 rows=2 loops=1)
Buffers: shared hit=2
-> Index Scan using ix_test__name_val on test test_2 (cost=0.15..8.17 rows=1 width=4) (actual time=0.008..0.009 rows=2 loops=1)
Index Cond: (((name)::text = 'cc'::text) AND (val = 30))
Buffers: shared hit=2
Planning Time: 0.270 ms
Execution Time: 0.109 ms
 hidden batch(es)


-- works on all RDBMS explain (analyze,buffers) select a.pid from test a, test b, test c where (a.name,a.val) = ('aa',10) and (b.name,b.val) = ('bb',20) and (c.name,c.val) = ('cc',30) and (a.pid = b.pid and b.pid = c.pid);
QUERY PLAN
Nested Loop (cost=0.46..24.54 rows=1 width=4) (actual time=0.037..0.048 rows=2 loops=1)
Join Filter: (a.pid = c.pid)
Rows Removed by Join Filter: 4
Buffers: shared hit=14
-> Nested Loop (cost=0.30..16.36 rows=1 width=8) (actual time=0.021..0.032 rows=3 loops=1)
Join Filter: (a.pid = b.pid)
Rows Removed by Join Filter: 6
Buffers: shared hit=8
-> Index Scan using ix_test__name_val on test a (cost=0.15..8.17 rows=1 width=4) (actual time=0.013..0.014 rows=3 loops=1)
Index Cond: (((name)::text = 'aa'::text) AND (val = 10))
Buffers: shared hit=2
-> Index Scan using ix_test__name_val on test b (cost=0.15..8.17 rows=1 width=4) (actual time=0.003..0.004 rows=3 loops=3)
Index Cond: (((name)::text = 'bb'::text) AND (val = 20))
Buffers: shared hit=6
-> Index Scan using ix_test__name_val on test c (cost=0.15..8.17 rows=1 width=4) (actual time=0.003..0.004 rows=2 loops=3)
Index Cond: (((name)::text = 'cc'::text) AND (val = 30))
Buffers: shared hit=6
Planning Time: 0.347 ms
Execution Time: 0.083 ms
 hidden batch(es)


-- same as above. for JOIN purists explain (analyze,buffers) select a.pid from test a cross join test b cross join test c where (a.name,a.val) = ('aa',10) and (b.name,b.val) = ('bb',20) and (c.name,c.val) = ('cc',30) and (a.pid = b.pid and b.pid = c.pid);
QUERY PLAN
Nested Loop (cost=0.46..24.54 rows=1 width=4) (actual time=0.033..0.044 rows=2 loops=1)
Join Filter: (a.pid = c.pid)
Rows Removed by Join Filter: 4
Buffers: shared hit=14
-> Nested Loop (cost=0.30..16.36 rows=1 width=8) (actual time=0.019..0.030 rows=3 loops=1)
Join Filter: (a.pid = b.pid)
Rows Removed by Join Filter: 6
Buffers: shared hit=8
-> Index Scan using ix_test__name_val on test a (cost=0.15..8.17 rows=1 width=4) (actual time=0.012..0.013 rows=3 loops=1)
Index Cond: (((name)::text = 'aa'::text) AND (val = 10))
Buffers: shared hit=2
-> Index Scan using ix_test__name_val on test b (cost=0.15..8.17 rows=1 width=4) (actual time=0.003..0.004 rows=3 loops=3)
Index Cond: (((name)::text = 'bb'::text) AND (val = 20))
Buffers: shared hit=6
-> Index Scan using ix_test__name_val on test c (cost=0.15..8.17 rows=1 width=4) (actual time=0.003..0.003 rows=2 loops=3)
Index Cond: (((name)::text = 'cc'::text) AND (val = 30))
Buffers: shared hit=6
Planning Time: 0.323 ms
Execution Time: 0.089 ms
 hidden batch(es)


-- just count explain (analyze,buffers) select t.pid from test t where (t.name, t.val) in ( ('aa', '10'), ('bb', '20'), ('cc', '30') ) group by t.pid having count(*) = 3;
QUERY PLAN
GroupAggregate (cost=16.52..16.55 rows=1 width=4) (actual time=0.043..0.046 rows=2 loops=1)
Group Key: pid
Filter: (count(*) = 3)
Rows Removed by Filter: 1
Buffers: shared hit=4
-> Sort (cost=16.52..16.53 rows=1 width=4) (actual time=0.036..0.038 rows=8 loops=1)
Sort Key: pid
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=4
-> Bitmap Heap Scan on test t (cost=12.49..16.51 rows=1 width=4) (actual time=0.019..0.021 rows=8 loops=1)
Recheck Cond: ((((name)::text = 'aa'::text) AND (val = 10)) OR (((name)::text = 'bb'::text) AND (val = 20)) OR (((name)::text = 'cc'::text) AND (val = 30)))
Heap Blocks: exact=1
Buffers: shared hit=4
-> BitmapOr (cost=12.49..12.49 rows=1 width=0) (actual time=0.014..0.015 rows=0 loops=1)
Buffers: shared hit=3
-> Bitmap Index Scan on ix_test__name_val (cost=0.00..4.16 rows=1 width=0) (actual time=0.008..0.008 rows=3 loops=1)
Index Cond: (((name)::text = 'aa'::text) AND (val = 10))
Buffers: shared hit=1
-> Bitmap Index Scan on ix_test__name_val (cost=0.00..4.16 rows=1 width=0) (actual time=0.003..0.003 rows=3 loops=1)
Index Cond: (((name)::text = 'bb'::text) AND (val = 20))
Buffers: shared hit=1
-> Bitmap Index Scan on ix_test__name_val (cost=0.00..4.16 rows=1 width=0) (actual time=0.002..0.002 rows=2 loops=1)
Index Cond: (((name)::text = 'cc'::text) AND (val = 30))
Buffers: shared hit=1
Planning Time: 0.133 ms
Execution Time: 0.131 ms
 hidden batch(es)


explain (analyze,buffers) select t.pid from test t group by t.pid having sum( ((t.name, t.val) in ( ('aa', '10'), ('bb', '20'), ('cc', '30') ))::int ) = 3
QUERY PLAN
HashAggregate (cost=61.03..63.53 rows=1 width=4) (actual time=0.025..0.026 rows=2 loops=1)
Group Key: pid
Filter: (sum((((((name)::text = 'aa'::text) AND (val = 10)) OR (((name)::text = 'bb'::text) AND (val = 20)) OR (((name)::text = 'cc'::text) AND (val = 30))))::integer) = 3)
Rows Removed by Filter: 1
Buffers: shared hit=1
-> Seq Scan on test t (cost=0.00..25.70 rows=1570 width=20) (actual time=0.008..0.010 rows=9 loops=1)
Buffers: shared hit=1
Planning Time: 0.106 ms
Execution Time: 0.063 ms
 hidden batch(es)