Speed up SELECT query with JOIN from table with 2.2M rows in PostgreSQL? There are two tables in PostgreSQL:



//has 2.2 millions rows 
create table account (
    account_id text primary key,
    email text not null,
    created_at timestamp not null
);

//has 1.8 millions rows
create table account_intercom (
    account_id text primary key references account(account_id),
    intercom_id text not null
);


It's easy to notice ~400k rows difference between these tables.
I want to select these 400k account_id and perform some operations with them in order to eliminate this "gap".

I tried this query:



SELECT account_id, email
  FROM
    account NATURAL LEFT JOIN
    account_intercom
  WHERE intercom_id IS NULL;


But it runs extremely slow. 

What is the way to speed up this query?

QUERY PLAN                                                                
---------------------------------------------------
 Hash Left Join  (cost=75252.40..207091.91 rows=1 width=46) (actual time=30056.787..87284.881 rows=417195 loops=1)
   Hash Cond: (account.account_id = account_intercom.account_id)
   Filter: (account_intercom.intercom_id IS NULL)
   Rows Removed by Filter: 1799858
   -&gt;  Seq Scan on account  (cost=0.00..47840.67 rows=2221867 width=46) (actual time=0.010..13484.228 rows=2217053 loops=1)
   -&gt;  Hash  (cost=36919.51..36919.51 rows=1800551 width=48) (actual time=30018.683..30018.683 rows=1799858 loops=1)
         Buckets: 65536  Batches: 64  Memory Usage: 2722kB
         -&gt;  Seq Scan on account_intercom  (cost=0.00..36919.51 rows=1800551 width=48) (actual time=0.008..9801.883 rows=1799858 loops=1)
 Planning time: 0.333 ms
 Execution time: 88794.797 ms
You should write it like this with a NOT EXISTS statement

SELECT account_id, email
FROM account AS a
WHERE NOT EXISTS (
  SELECT 1
  FROM account_intercom AS i
  WHERE a.account_id = i.account_id
);


Make sure your stats are uptodate and your table clean,

VACUUM FULL account;
VACUUM FULL account_intercom;


Paste the updated EXPLAIN ANALYZE