Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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

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

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

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

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

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

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

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