Postgres 10

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 a
( id int not null,
  x int
) ;
create index a_ix on a (id, x) ;
create table b
( id int not null,
  x int
) ;
create index b_ix on b (id, x) ;
insert into a (id, x)
values
  (1, 1),
  (1, 1),
  (1, 1),
  
  (3, 3),
  (3, 3),
  
  (4, 4),
  (4, 4),
  (4, 4),
  (4, 4),
  (4, 4),
  (4, 4) ;
11 rows affected

insert into b (id, x)
values
  (1, 1),

  (3, 3),
  (3, 3),
  (3, 3),
  
  (4, 4),
  (4, 4) ;
6 rows affected

select * from a
except 
select * from b ;
explain (analyze,buffers)
select * from a
except 
select * from b ;


QUERY PLAN


HashSetOp Except  (cost=0.00..133.00 rows=226 width=12) (actual time=0.018..0.018 rows=0 loops=1)

  Buffers: shared hit=2

  ->  Append  (cost=0.00..110.40 rows=4520 width=12) (actual time=0.004..0.013 rows=17 loops=1)

        Buffers: shared hit=2

        ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..55.20 rows=2260 width=12) (actual time=0.003..0.007 rows=11 loops=1)

              Buffers: shared hit=1

              ->  Seq Scan on a  (cost=0.00..32.60 rows=2260 width=8) (actual time=0.002..0.004 rows=11 loops=1)

                    Buffers: shared hit=1

        ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..55.20 rows=2260 width=12) (actual time=0.001..0.003 rows=6 loops=1)

              Buffers: shared hit=1

              ->  Seq Scan on b  (cost=0.00..32.60 rows=2260 width=8) (actual time=0.001..0.001 rows=6 loops=1)

                    Buffers: shared hit=1

Planning time: 0.041 ms

Execution time: 0.041 ms


select * from a
except all
select * from b ;


id
x


1
1

1
1

4
4

4
4

4
4

4
4


explain (analyze,buffers)
select * from a
except all
select * from b ;


QUERY PLAN


HashSetOp Except All  (cost=0.00..133.00 rows=2260 width=12) (actual time=0.017..0.018 rows=6 loops=1)

  Buffers: shared hit=2

  ->  Append  (cost=0.00..110.40 rows=4520 width=12) (actual time=0.003..0.012 rows=17 loops=1)

        Buffers: shared hit=2

        ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..55.20 rows=2260 width=12) (actual time=0.003..0.007 rows=11 loops=1)

              Buffers: shared hit=1

              ->  Seq Scan on a  (cost=0.00..32.60 rows=2260 width=8) (actual time=0.003..0.004 rows=11 loops=1)

                    Buffers: shared hit=1

        ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..55.20 rows=2260 width=12) (actual time=0.001..0.003 rows=6 loops=1)

              Buffers: shared hit=1

              ->  Seq Scan on b  (cost=0.00..32.60 rows=2260 width=8) (actual time=0.001..0.001 rows=6 loops=1)

                    Buffers: shared hit=1

Planning time: 0.034 ms

Execution time: 0.034 ms