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) ;
insert into b (id, x)
values
(1, 1),
(3, 3),
(3, 3),
(3, 3),
(4, 4),
(4, 4) ;
select * from a
except
select * from b ;
| id | x |
|---|---|
| … | |
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 |
| … |