0

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Arya
  • 8,473
  • 27
  • 105
  • 175

1 Answers1

3

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.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • What is the difference between your index and the one OP use with `USING btree`? or what make take that chooice? – Juan Carlos Oropeza Oct 02 '15 at 21:29
  • @Juan: Btree is the default, `USING btree` is just noise, no difference. As is `COLLATE pg_catalog."default"` for character types. The difference is the order of columns. The condition is on `account_id`, so it should be first: http://dba.stackexchange.com/a/27493/3684 – Erwin Brandstetter Oct 03 '15 at 01:10