clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
≡
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1926618 fiddles created (21854 in the last week).
create table a ( id int not null, x int ) ;
✓
 hidden batch(es)
create index a_ix on a (id, x) ;
✓
 hidden batch(es)
create table b ( id int not null, x int ) ;
✓
 hidden batch(es)
create index b_ix on b (id, x) ;
✓
 hidden batch(es)
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
 hidden batch(es)
insert into b (id, x) values (1, 1), (3, 3), (3, 3), (3, 3), (4, 4), (4, 4) ;
6 rows affected
 hidden batch(es)
select * from a except select * from b ;
id x
…
 hidden batch(es)
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
…
 hidden batch(es)
select * from a except all select * from b ;
id x
1 1
1 1
4 4
4 4
4 4
4 4
…
 hidden batch(es)
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
…
 hidden batch(es)