2

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
   ->  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 – Evan Carroll Aug 30 '18 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 '18 at 20:49
1

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

| improve this answer | |
  • Be careful with VACUUM FULL - depending on the size of your tables it will lock for all operations. – Alex Plugaru Aug 31 '18 at 0:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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