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
  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
   ->  Seq Scan on account  (cost=0.00..47840.67 rows=2221867 width=46) (actual time=0.010..13484.228 rows=2217053 loops=1)
   ->  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
         ->  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 never use NATURAL JOINS Aug 30, 2018 at 20:31
  • What version of PostgreSQL, and on what hardware? I can run this query in less than 5 seconds on a low-end laptop.
    – jjanes
    Aug 30, 2018 at 20:49

1 Answer 1


You should write it like this with a NOT EXISTS statement

SELECT account_id, email
FROM account AS a
  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

  • Be careful with VACUUM FULL - depending on the size of your tables it will lock for all operations. Aug 31, 2018 at 0:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.