CODEWITHSUNDEEP
sqlpostgresqlpostgresql-performance
Arya
Arya

Reputation: 8985

Any way to speed up this sql query?

I have the following Postgres query, the query takes 10 to 50 seconds to execute. 

SELECT m.match_id FROM match m
WHERE m.match_id NOT IN(SELECT ml.match_id FROM message_log ml)
AND m.account_id = ?

I have created an index on match_id and account_id

CREATE INDEX match_match_id_account_id_idx ON match USING btree
  (match_id COLLATE pg_catalog."default",
   account_id COLLATE pg_catalog."default");

But still the query takes a long time. What can I do to speed this up and make it efficient? My server load goes to 25 when I have a few of these queries executing.

Upvotes: 0

Views: 106

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

NOT IN (SELECT ... ) can be considerably more expensive because it has to handle NULL separately. It can also be tricky when NULL values are involved. Typically LEFT JOIN / IS NULL (or one of the other related techniques) is faster:

Applied to your query:

SELECT m.match_id
FROM   match m 
LEFT   JOIN message_log ml USING (match_id)
WHERE  ml.match_id IS NULL
AND    m.account_id = ?;

The best index would be:

CREATE INDEX match_match_id_account_id_idx ON match (account_id, match_id);

Or just on (account_id), assuming that match_id is PK in both tables. You also already have the needed index on message_log(match_id). Else create that, too.

Also COLLATE pg_catalog."default" in your index definition indicates that your ID columns are character types, which is typically inefficient. Should typically better be integer types.

My educated guess from the little you have shown so far: there are probably more issues.

Upvotes: 3

Related Questions