Except All

名称

差集算子

介绍

与 Except 算子唯一不同的是，Except All算子不对数据进行去重处理

举例

--清理环境
DROP TABLE t1 CASCADE;
DROP TABLE t2 CASCADE;

--创建环境
CREATE TABLE t1(a int);
INSERT INTO t1 SELECT generate_series(1,10);
INSERT INTO t1 SELECT generate_series(1,10);
ANALYZE t1;

CREATE TABLE t2(a int);
INSERT INTO t2 SELECT generate_series(1,5);
ANALYZE t2;

EXPLAIN ANALYZE SELECT * FROM t1 EXCEPT ALL SELECT * FROM t2;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 SetOp Except All  (cost=14.83..14.96 rows=3 width=4) (actual time=0.07..0.09 rows=15 loops=1)
   ->  Sort  (cost=14.83..14.89 rows=25 width=4) (actual time=0.07..0.07 rows=25 loops=1)
         Sort Key: A
         ->  Append  (cost=0.00..14.25 rows=25 width=4) (actual time=0.02..0.05 rows=25 loops=1)
               ->  Subquery Scan "*SELECT* 1"  (cost=0.00..7.20 rows=20 width=4) (actual time=0.02..0.03 rows=20 loops=1)
                     ->  Seq Scan on T1  (cost=0.00..7.20 rows=20 width=4) (actual time=0.02..0.03 rows=20 loops=1)
               ->  Subquery Scan "*SELECT* 2"  (cost=0.00..7.05 rows=5 width=4) (actual time=0.01..0.02 rows=5 loops=1)
                     ->  Seq Scan on T2  (cost=0.00..7.05 rows=5 width=4) (actual time=0.01..0.01 rows=5 loops=1)
 Planning Time: 0.35 msec
 Execution Time: 0.41 msec
(10 rows)