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:
Somehow using a CTE or variable(?) for
COALESCE(members.zone, 'Etc/UTC')
Find a different way to handle DUPLICATE KEY exceptions for entries that have the same
member_id
anddate
(i.e., replaceWHERE NOT EXISTS(...)
with something else)
I would appreciate any constructive criticisms and/or pointers.