PostgreSQL exclude records crossing other table values

Question

Consider two PostgreSQL tables :

Table #1

id INT
secret_id INT
title VARCHAR

Table #2

id INT
secret_id INT

I need to select all records from Table #1, but exclude Table #2 crossing secret_id values.

The following query is very slow with 1 000 000 records in Table #1 and 500 000 in Table #2 :

select * from table_1 where secret_id not in (select secret_id from table_2);

What is the best way to achieve this ?

Answer 1

FWIW, I tested Daniel Lyons and Craig Ringer's suggestions made in comments above. Here are the results on my particular case (~500k rows per table), order by efficiency (the most efficient first).

ANTI-JOIN :

> EXPLAIN ANALYZE SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.secret_id=t2.secret_id WHERE t2.secret_id IS NULL;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Hash Anti Join  (cost=19720.19..56129.91 rows=21 width=28) (actual time=139.868..459.991 rows=142993 loops=1)
   Hash Cond: (t1.secret_id = t2.secret_id)
   ->  Seq Scan ON table1 t1  (cost=0.00..13049.06 rows=622606 width=14) (actual time=0.005..61.913 rows=622338 loops=1)
   ->  Hash  (cost=10849.75..10849.75 rows=510275 width=14) (actual time=138.176..138.176 rows=510275 loops=1)
         Buckets: 4096  Batches: 32  Memory Usage: 777kB
         ->  Seq Scan ON table2 t2  (cost=0.00..10849.75 rows=510275 width=14) (actual time=0.018..47.005 rows=510275 loops=1)
 Total runtime: 466.748 ms
(7 lignes)

NOT EXISTS :

> EXPLAIN ANALYZE SELECT * FROM table1 t1 WHERE NOT EXISTS (SELECT secret_id FROM table2 t2 WHERE t2.secret_id=t1.secret_id);
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Hash Anti Join  (cost=19222.19..55133.91 rows=21 width=14) (actual time=181.881..517.632 rows=142993 loops=1)
   Hash Cond: (t1.secret_id = t2.secret_id)
   ->  Seq Scan ON table1 t1  (cost=0.00..13049.06 rows=622606 width=14) (actual time=0.005..70.478 rows=622338 loops=1)
   ->  Hash  (cost=10849.75..10849.75 rows=510275 width=4) (actual time=179.665..179.665 rows=510275 loops=1)
         Buckets: 4096  Batches: 32  Memory Usage: 592kB
         ->  Seq Scan ON table2 t2  (cost=0.00..10849.75 rows=510275 width=4) (actual time=0.019..78.074 rows=510275 loops=1)
 Total runtime: 524.300 ms
(7 lignes)

EXCEPT :

> EXPLAIN ANALYZE SELECT * FROM table1 EXCEPT (SELECT t1.* FROM table1 t1 join table2 t2 ON t1.secret_id=t2.secret_id);
                                                                           QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 SetOp Except  (cost=1524985.53..1619119.03 rows=62261 width=14) (actual time=16926.056..19850.915 rows=142993 loops=1)
   ->  Sort  (cost=1524985.53..1543812.23 rows=7530680 width=14) (actual time=16925.010..18596.860 rows=6491408 loops=1)
         Sort Key: "*SELECT* 1".secret_id, "*SELECT* 1".jeu, "*SELECT* 1".combinaison, "*SELECT* 1".gains
         Sort Method: external merge  Disk: 185232kB
         ->  Append  (cost=0.00..278722.63 rows=7530680 width=14) (actual time=0.007..2951.920 rows=6491408 loops=1)
               ->  Subquery Scan ON "*SELECT* 1"  (cost=0.00..19275.12 rows=622606 width=14) (actual time=0.007..176.892 rows=622338 loops=1)
                     ->  Seq Scan ON table1  (cost=0.00..13049.06 rows=622606 width=14) (actual time=0.005..69.842 rows=622338 loops=1)
               ->  Subquery Scan ON "*SELECT* 2"  (cost=19222.19..259447.51 rows=6908074 width=14) (actual time=168.529..2228.335 rows=5869070 loops=1)
                     ->  Hash Join  (cost=19222.19..190366.77 rows=6908074 width=14) (actual time=168.528..1450.663 rows=5869070 loops=1)
                           Hash Cond: (t1.secret_id = t2.secret_id)
                           ->  Seq Scan ON table1 t1  (cost=0.00..13049.06 rows=622606 width=14) (actual time=0.002..64.554 rows=622338 loops=1)
                           ->  Hash  (cost=10849.75..10849.75 rows=510275 width=4) (actual time=168.329..168.329 rows=510275 loops=1)
                                 Buckets: 4096  Batches: 32  Memory Usage: 592kB
                                 ->  Seq Scan ON table2 t2  (cost=0.00..10849.75 rows=510275 width=4) (actual time=0.017..72.702 rows=510275 loops=1)
 Total runtime: 19896.445 ms
(15 lignes)

NOT IN :

> EXPLAIN SELECT * FROM table1 WHERE secret_id NOT IN (SELECT secret_id FROM table2);
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Seq Scan ON table1  (cost=0.00..5189688549.26 rows=311303 width=14)
   Filter: (NOT (SubPlan 1))
   SubPlan 1
     ->  Materialize  (cost=0.00..15395.12 rows=510275 width=4)
           ->  Seq Scan ON table2  (cost=0.00..10849.75 rows=510275 width=4)
(5 lignes)

I did not analyze the latter because it took ages.