Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > create table a > ( id int not null, > x int > ) ; > > <pre> > ✓ > </pre> <!-- --> > create index a_ix on a (id, x) ; > > <pre> > ✓ > </pre> <!-- --> > create table b > ( id int not null, > x int > ) ; > > <pre> > ✓ > </pre> <!-- --> > create index b_ix on b (id, x) ; > > <pre> > ✓ > </pre> <!-- --> > 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) ; > > <pre> 11 rows affected > </pre> <!-- --> > insert into b (id, x) > values > (1, 1), > > (3, 3), > (3, 3), > (3, 3), > > (4, 4), > (4, 4) ; > > <pre> 6 rows affected > </pre> <!-- --> > select * from a > except > select * from b ; > > <pre> > id | x > -: | -: > </pre> <!-- --> > explain (analyze,buffers) > select * from a > except > select * from b ; > > <pre> > | 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 | > | -&gt; Append (cost=0.00..110.40 rows=4520 width=12) (actual time=0.004..0.013 rows=17 loops=1) | > | Buffers: shared hit=2 | > | -&gt; Subquery Scan on &quot;*SELECT* 1&quot; (cost=0.00..55.20 rows=2260 width=12) (actual time=0.003..0.007 rows=11 loops=1) | > | Buffers: shared hit=1 | > | -&gt; 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 | > | -&gt; Subquery Scan on &quot;*SELECT* 2&quot; (cost=0.00..55.20 rows=2260 width=12) (actual time=0.001..0.003 rows=6 loops=1) | > | Buffers: shared hit=1 | > | -&gt; 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 | > </pre> <!-- --> > select * from a > except all > select * from b ; > > <pre> > id | x > -: | -: > 1 | 1 > 1 | 1 > 4 | 4 > 4 | 4 > 4 | 4 > 4 | 4 > </pre> <!-- --> > explain (analyze,buffers) > select * from a > except all > select * from b ; > > <pre> > | 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 | > | -&gt; Append (cost=0.00..110.40 rows=4520 width=12) (actual time=0.003..0.012 rows=17 loops=1) | > | Buffers: shared hit=2 | > | -&gt; Subquery Scan on &quot;*SELECT* 1&quot; (cost=0.00..55.20 rows=2260 width=12) (actual time=0.003..0.007 rows=11 loops=1) | > | Buffers: shared hit=1 | > | -&gt; 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 | > | -&gt; Subquery Scan on &quot;*SELECT* 2&quot; (cost=0.00..55.20 rows=2260 width=12) (actual time=0.001..0.003 rows=6 loops=1) | > | Buffers: shared hit=1 | > | -&gt; 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 | > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_10&fiddle=8712c8438523033310ffff16ef2b03e2)*
back to fiddle