CODEWITHSUNDEEP
sqlpostgresql
Arya
Arya

Reputation: 8985

How many indexes for this complex query?

I have the following query

select m.* 
 from message_log m
where m.from_id <> ?
  and m.to_id = ?
  and m.unix_timestamp = (select max(unix_timestamp)
                            from message_log
                           where match_id = m.match_id
                           group by match_id)

It takes about 2 minutes to complete. As far as I know the only way to improve performance is have indexes or better hardware. I tried adding "one" index for the following columns 

CREATE INDEX message_log_from_id_to_id_match_id_unix_timestamp_idx
  ON message_log
  USING btree
  (from_id COLLATE pg_catalog."default", to_id COLLATE pg_catalog."default", match_id COLLATE pg_catalog."default", unix_timestamp);

It did not improve performance. Am I creating the index on the wrong columns? Please recommend what I can do to improve performance. I am using Postgresql 9.1

Upvotes: 0

Views: 55

Answers (1)

sstan
sstan

Reputation: 36503

For optimal results, I would define the following 2 indexes, with these specific column orders:

  • to_id, unix_timestamp, from_id (for the main part of the query)
  • match_id, unix_timestamp (for the subquery)

It's hard to say exactly, because we don't know the cardinality of your different columns.

Upvotes: 2

Related Questions