Precalculate values in (postgres) table

user_6237339 0
Viewed 204

I have a large table for which I need to calculate some statistics about the last 7 days.

A very reduced example of what I have would be a table of "events" caused by some entity "who" and having a certain number "num". In this reduced example I want to calulate the sum of "num" of the last 3 days (including today).

Not every entity creates an event every day, so some days might be missing and some entitys might be missing on days where I have events.

Example:

INFO_DATE WHO NUM
2020-01-01 A 1
2021-01-01 B 1
2021-01-02 A 2
2021-01-02 C 1
2021-01-04 B 2
2021-01-04 C 2
2021-01-05 B 1

Or, As SQL:

CREATE TABLE events AS
    SELECT date('2021-01-01') AS INFO_DATE, 'A' AS WHO, 1 AS NUM
UNION
    SELECT date('2021-01-01') AS INFO_DATE, 'B' AS WHO, 1 AS NUM
UNION
    SELECT date('2021-01-02') AS INFO_DATE, 'A' AS WHO, 2 AS NUM
UNION
    SELECT date('2021-01-02') AS INFO_DATE, 'C' AS WHO, 1 AS NUM
UNION
    SELECT date('2021-01-04') AS INFO_DATE, 'B' AS WHO, 2 AS NUM
UNION
    SELECT date('2021-01-04') AS INFO_DATE, 'C' AS WHO, 2 AS NUM
UNION
    SELECT date('2021-01-05') AS INFO_DATE, 'B' AS WHO, 1 AS NUM
;

It's now quite easy to come up with a query to calulate the "sum3" for any given date (2021-01-5 here):

SELECT DATE('2021-01-05') AS INFO_DATE, WHO, SUM(NUM) AS SUM3 FROM (
    SELECT INFO_DATE, WHO, NUM
    FROM events
    WHERE INFO_DATE <= DATE('2021-01-05')
    AND INFO_DATE > (DATE('2021-01-05') - INTERVAL '3 DAY')
) LAST3
GROUP BY WHO

But since my real table is quite huge and the entity is not just one column but a combination of 3 columns, calculating my sum is too time consuming. As a result the web application retrieving these values has to wait too long.

My idea is now to precalulate the sums into a new column of the same table, but I'm a bit clueless how to achieve this.

The resulting table would look like this:

INFO_DATE WHO NUM SUM3
2020-01-01 A 1 1
2021-01-01 B 1 1
2021-01-02 A 2 3
2021-01-02 B 0 1
2021-01-02 C 1 1
2021-01-03 A 0 3
2021-01-03 B 0 1
2021-01-03 C 0 1
2021-01-04 A 0 2
2021-01-04 B 2 2
2021-01-04 C 2 3
2021-01-05 B 0 2
2021-01-05 C 0 2

I think I would need to alter the table first to get the new column.

Then I need to go through all the dates from my lowest to highest date and calulate my sums for these dates.

My existing events table entries have to either be updated with the sums or new entries have to be created (those with "NUM" == 0 in the table above)

I need some hints please how to achieve this.

Or maybe some ideas for a better approach.

Maybe it's a better idea to have a new table for the statistic values? Even though it will have an almost identical index as my original table an will duplicate memory for this?

postgresql
3 Answers

In your case, because the calculation is not expensive, I would suggest using table partitioning.

I could try to describe the whole process here, but, to be honest, I think it would be more useful just to leave some links here (1, 2).

In general, the idea is that you divide your logical table physically by some criteria, in your case that would be INFO_DATE (for example you could create new partition for every month), so when INFO_DATE appears in WHERE clause the engine doesn't have to search through the whole table.

It requires some investigation, but you can benifit from it. Hope this makes sense.

user_128854680

A before trigger may help you populate a sum3 column:

create or replace function insert_sum3_trg() returns trigger as
$$
begin

  new.sum3 := (
    select new.num + coalesce(sum(NUM),0) from events
      where who = new.who
      and info_date <= new.info_date
      and info_date > new.info_date - '3 days'::interval
      and info_date != new.info_date);
  return new;
end
$$ language plpgsql;

create trigger insert_sum3 before insert or update on events for each row execute procedure insert_sum3_trg();

Some pretty big caveats here, but maybe this answer, even though not suitable, could potentially lead you to the correct one.

Caveats:

  1. I'm assuming that WHO is unique for the day.

  2. You'll need to backfill all the existing sum3s that are null, something like

update events e set sum3=(select coalesce(sum(num), 0) from events e2
      where e2.who = e.who
      and sum3 is null
      and e2.info_date > e.info_date - '3 days'::interval
      and e2.info_date <= e.info_date);

but do this before inserting the trigger otherwise it will run twice.

  1. (A biggie) This trigger assumes that the inserts are done in time sequential ordering. Going back and inserting earlier than other rows will require a recalculation of sum3.

  2. Finally, you'll need to play around with the trigger because if there's an UPDATE you risk double counting. I didn't want to spend too long on a working example as I don't know if you're just running INSERTs, but there's a hint of trying to accommodate this with the info_date != new.info_date, again assuming that who is unique for a day.

user_11457230

Why don't you write your query like that ?

SELECT DATE('2021-01-05') AS INFO_DATE, WHO, sum(NUM)
FROM events
WHERE INFO_DATE <= DATE('2021-01-05')
AND INFO_DATE > (DATE('2021-01-05') - INTERVAL '3 DAY')
group by DATE('2021-01-05'),WHO

Create an index on info_date,who

user_148710180
Related