test=# create table settings as select s, 'setting ' || s::text from generate_series(1, 10000000) s;
SELECT 10000000
test=*# create table blacklist as select (random()*1000000)::int r from generate_series(1, 10000) s;
SELECT 10000
test=*# analyse blacklist;
ANALYZE
test=*# analyse settings;
ANALYZE
test=*# explain analyse select * from settings where s not in (select r from blacklist);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Seq Scan on settings (cost=170.00..188863.10 rows=4999964 width=19) (actual time=12.157..2678.397 rows=9990055 loops=1)
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 9945
SubPlan 1
-> Seq Scan on blacklist (cost=0.00..145.00 rows=10000 width=4) (actual time=0.020..3.968 rows=10000 loops=1)
Planning time: 0.176 ms
Execution time: 3338.145 ms
(7 Zeilen)
test=*# explain analyse select * from settings left join blacklist on settings.s=blacklist.r where blacklist.r is null;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Hash Anti Join (cost=270.00..290148.97 rows=9989983 width=23) (actual time=7.900..3802.351 rows=9990055 loops=1)
Hash Cond: (settings.s = blacklist.r)
-> Seq Scan on settings (cost=0.00..163693.28 rows=9999928 width=19) (actual time=0.054..1397.238 rows=10000000 loops=1)
-> Hash (cost=145.00..145.00 rows=10000 width=4) (actual time=7.797..7.797 rows=10000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 480kB
-> Seq Scan on blacklist (cost=0.00..145.00 rows=10000 width=4) (actual time=0.046..3.547 rows=10000 loops=1)
Planning time: 0.195 ms
Execution time: 4458.254 ms
(8 Zeilen)
test=*#