create table a(a int not null);
insert into a(a) select generate_series(1,6000000);
create table b as select * from a where a%10 <> 0;
create index fooa on a(a);
alter table b alter column a set not null;
create index foob on b(a);
vacuum analyze verbose;
gj=# explain select a.a from a where a not in (select a from b); QUERY PLAN
------------------------------------------------------------------------- Seq Scan on a
(cost=99035.00..257874197565.00rows=3000000 width=4) Filter: (NOT (subplan)) SubPlan -> Materialize
(cost=99035.00..171493.00rows=5400000 width=4) -> Seq Scan on b (cost=0.00..75177.00 rows=5400000
width=4)
(5 rows)
that's absolutely humongous cost, and it really does take ages before
this thing finishes (had to kill it after an hour).
For change, same type of query (should return same thing)
gj=# explain analyze select a.a from a left join b on a.a=b.a where
b.a is null; QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
MergeAnti Join (cost=0.00..350302.50 rows=600000 width=4) (actual
time=0.534..10851.995 rows=600000 loops=1) Merge Cond: (a.a = b.a) -> Index Scan using fooa on a
(cost=0.00..166209.78rows=6000000
width=4) (actual time=0.232..3128.438 rows=6000000 loops=1) -> Index Scan using foob on b (cost=0.00..149592.72
rows=5400000
width=4) (actual time=0.161..2778.569 rows=5400000 loops=1) Total runtime: 10938.592 ms
(5 rows)
Time: 10939,414 ms