2

In relation to another post of mine regarding slow performance for an averaging query on a table with ~30 million rows, I tried creating a summary table to store the averages.

Firstly, I have a table of activity_scores and members. Below is an example of the activity_scores table:

 id | member_id | score |         created_at         |         updated_at         
----+-----------+-------+----------------------------+----------------------------
  1 |         8 |    73 | 2016-11-04 05:32:55.564235 | 2016-11-04 05:32:55.564235
  2 |        10 |    20 | 2016-11-04 05:22:55.564235 | 2016-11-04 05:22:55.564235

The scores are inserted every 10 minutes over time. In response to ypercube, I created a summary table with the constraint unique(member_id, date) for quicker access during a graphing process.

Then, I wrote the following query to store the average of scores for 'yesterday' per member into a summary table.

INSERT INTO activity_score_daily_averages (member_id, date, average_score)
  SELECT 
     activity_scores.member_id, TO_CHAR((activity_scores.created_at AT TIME ZONE COALESCE(members.zone, 'Etc/UTC')), 'YYYY/MM/DD') AS date,
     AVG(activity_scores.score) AS average_score FROM activity_scores INNER JOIN members ON members.id = activity_scores.member_id
     WHERE 
       NOT EXISTS(
         SELECT 1 FROM activity_score_daily_averages
         WHERE activity_score_daily_averages.member_id = activity_scores.member_id
         AND activity_score_daily_averages.date = date
       )
       AND activity_scores.created_at AT TIME ZONE COALESCE(members.zone, 'Etc/UTC') > (current_date-1) AT TIME ZONE COALESCE(members.zone, 'Etc/UTC')
       AND activity_scores.created_at AT TIME ZONE COALESCE(members.zone, 'Etc/UTC') < (current_date) AT TIME ZONE COALESCE(members.zone, 'Etc/UTC')
     GROUP BY member_id, date

Yet, running the above query took ~5 minutes. I feel as though there is a lot of room for improvement considering that the table I am working with isn't even considered that large for some of the DBMS masters out there...

Some things I was considering for optimizing this query were:

  1. Somehow using a CTE or variable(?) for COALESCE(members.zone, 'Etc/UTC')

  2. Find a different way to handle DUPLICATE KEY exceptions for entries that have the same member_id and date (i.e., replace WHERE NOT EXISTS(...) with something else)

I would appreciate any constructive criticisms and/or pointers.

4
  • So, if I understand correctly, every member has a different 'yesterday' which prevents you from having a simple date criterion.
    – mendosi
    Commented Nov 8, 2016 at 7:17
  • 3
    Maybe not a direct answer, but instead of storing AVG, consider storing both SUM(score) and COUNT(*), giving you total_score and score_count. Those are then aggregatable and an average can then be calculated from them with sum(total_score) / sum(score_count), so you have a more flexible solution that can provide averages at different granularities. Commented Nov 8, 2016 at 8:57
  • @mendosi precisely yes, due to the different time zones. Commented Nov 8, 2016 at 16:28
  • @DavidAldridge thanks, good point. I'll implement that. Other than that, do you guys see anything else that could be changed to make things run a little faster? Commented Nov 8, 2016 at 16:28

1 Answer 1

2

Here is a possible scheme:

  1. Add a column to the activity_scores table is_processed which flags whether the row has been summarized into the summary table
  2. Add a partial index to the activity_scores table for only the unprocessed rows, this could include all columns
  3. Now when you go to load yesterday's data you are only looking through rows which you know have not been processed
  4. Identify yesterday's rows as part of the load process and insert them into a temp table, then insert the summary of the rows into the summary table
  5. Join the temp table to the source table on id and mark the rows as processed (should be quick as you have the ids and aren't doing a scan)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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