lundi 20 avril 2015

How can I optimize this group wise max sql query?

This is what I want. It basically grabs all records from the daily_statistics table and groups by user_id. At the same time, it does the following:

  1. The values of the user are grouped by most recent
  2. attachment_ids are represented as an array, so I can determine how many attachments the user has

The result:

 user_id | country_id |       time_at       | assumed_gender |    attachment_ids
---------+------------+---------------------+----------------+----------------------
   21581 |        172 | 2015-04-18 17:55:00 |                | [5942]
   21610 |        140 | 2015-04-18 19:55:00 | male           | [5940]
   22044 |        174 | 2015-04-18 21:55:00 | female         | [12312313, 12312313]

   21353 |        174 | 2015-04-18 20:59:00 | male           | [5938]
   21573 |        246 | 2015-04-18 21:57:00 | male           | [5936]
(5 rows)

The follwoing query executes slow. Something like 17 seconds.

  SELECT
    ds.user_id,
    max(case when id=maxid then country_id end) AS country_id,
    max(case when id=maxid then time_at end) AS time_at,
    max(case when id=maxid then properties->'assumed_gender' end) AS assumed_gender,
    json_agg(to_json(attachment_id)) AS attachment_ids
  FROM daily_statistics ds JOIN (
      SELECT u.id as user_id, (
        SELECT ds2.id FROM daily_statistics ds2 WHERE ds2.user_id=u.id AND ds2.metric = 'participation' AND ds2.status = 'active' AND ds2.campaign_id = 39
        ORDER BY ds2.id DESC LIMIT 1
      ) AS maxid FROM users u
      WHERE u.properties -> 'provider' IN ('twitter')
  ) mu ON (ds.user_id=mu.user_id)
  WHERE ds.campaign_id = 39 AND ds.metric = 'participation' AND ds.status = 'active'
  GROUP BY ds.user_id;

The issue is with the group wise max statement. Is there a way to optimize this query and get the same output? I was thinking of using some kind of lateral join? But then I wouldn't be able to get the number of attachment_id's per user.

Thoughts would be appreciated.

Aucun commentaire:

Enregistrer un commentaire