I already posted this question on stackoverflow but I thought that I might get a better answer here.
I have a table storing millions of events occurring to users:
Table "public.events"
Column | Type | Modifiers
------------+--------------------------+-----------------------------------------------------------
event_id | integer | not null default nextval('events_event_id_seq'::regclass)
user_id | bigint |
event_type | integer |
ts | timestamp with time zone |
There are 5 different values for event_type, millions of users, and varying number of events per user per event_type usually ranging from 1 to 50.
A sample of the data:
+-----------+----------+-------------+----------------------------+
| event_id | user_id | event_type | timestamp |
+-----------+----------+-------------+----------------------------+
| 1 | 1 | 1 | January, 01 2015 00:00:00 |
| 2 | 1 | 1 | January, 10 2015 00:00:00 |
| 3 | 1 | 1 | January, 20 2015 00:00:00 |
| 4 | 1 | 1 | January, 30 2015 00:00:00 |
| 5 | 1 | 1 | February, 10 2015 00:00:00 |
| 6 | 1 | 1 | February, 21 2015 00:00:00 |
| 7 | 1 | 1 | February, 22 2015 00:00:00 |
+-----------+----------+-------------+----------------------------+
I would like to get, for each event, the number of events of the same user and the same event_type
that occurred within 30 days before the event.
It should look like the following:
+-----------+----------+-------------+-----------------------------+-------+
| event_id | user_id | event_type | timestamp | count |
+-----------+----------+-------------+-----------------------------+-------+
| 1 | 1 | 1 | January, 01 2015 00:00:00 | 1 |
| 2 | 1 | 1 | January, 10 2015 00:00:00 | 2 |
| 3 | 1 | 1 | January, 20 2015 00:00:00 | 3 |
| 4 | 1 | 1 | January, 30 2015 00:00:00 | 4 |
| 5 | 1 | 1 | February, 10 2015 00:00:00 | 3 |
| 6 | 1 | 1 | February, 21 2015 00:00:00 | 3 |
| 7 | 1 | 1 | February, 22 2015 00:00:00 | 4 |
+-----------+----------+-------------+-----------------------------+-------+
So far I managed to succeed with two different queries (tests on a 1000 rows generated sample on PostgreSQL 9.4.1):
SELECT
event_id, user_id,event_type,"timestamp",
(
SELECT count(*)
FROM events
WHERE timestamp >= e.timestamp - interval '30 days'
AND timestamp <= e.timestamp
AND user_id = e.user_id
AND event_type = e.event_type
GROUP BY event_type, user_id
) as "count"
FROM events e;
It works quite well, especially since I have an index on the timestamps:
Index Scan using pk_event_id on events e (cost=0.28..12018.74 rows=1000 width=24)
SubPlan 1
-> GroupAggregate (cost=4.33..11.97 rows=1 width=20)
Group Key: events.event_type, events.user_id
-> Bitmap Heap Scan on events (cost=4.33..11.95 rows=1 width=20)
Recheck Cond: ((""timestamp"" >= (e."timestamp" - '30 days'::interval)) AND ("timestamp" <= e."timestamp"))
Filter: ((user_id = e.user_id) AND (event_type = e.event_type))
-> Bitmap Index Scan on idx_events_timestamp (cost=0.00..4.33 rows=5 width=0)
Index Cond: ((""timestamp"" >= (e."timestamp" - '30 days'::interval)) AND ("timestamp" <= e."timestamp"))
Still, it does not scale well and I thought that using window functions might improve the performance:
SELECT toto.event_id,toto.user_id,toto.event_type,toto.lv as time,COUNT(*)
FROM(
SELECT e.event_id, e.user_id,e.event_type,"timestamp",
last_value("timestamp") OVER w as lv,
unnest(array_agg(e."timestamp") OVER w) as agg
FROM events e
WINDOW w AS (PARTITION BY e.user_id,e.event_type ORDER BY e."timestamp"
ROWS UNBOUNDED PRECEDING)) AS toto
WHERE toto.agg >= toto.lv - interval '30 days'
GROUP by event_id,user_id,event_type,lv;
Since I have to use unnest and a sub-query, the performance actually gets worse:
Sort (cost=5344.41..5427.74 rows=33333 width=24)
Sort Key: toto.event_id
-> HashAggregate (cost=2506.99..2840.32 rows=33333 width=24)
Group Key: toto.event_id, toto.user_id, toto.event_type, toto.lv
-> Subquery Scan on toto (cost=67.83..2090.33 rows=33333 width=24)
Filter: (toto.agg >= (toto.lv - '30 days'::interval))
-> WindowAgg (cost=67.83..590.33 rows=100000 width=24)
-> Sort (cost=67.83..70.33 rows=1000 width=24)
Sort Key: e.user_id, e.event_type, e."timestamp"
-> Seq Scan on events e (cost=0.00..18.00 rows=1000 width=24)
I was wondering if I could modify if I could only keep my sub-query and somehow modify the window frame to keep only timestamp that are 30 days or less before the row timestamp. Do you think it is possible to scale this query for a very big table without switching to a MapReduce framework?
In a second time, I would like to exclude duplicate events, i.e. same event_type
and same timestamp.
timestamptz
in the fiddle buttimestamp
literals as data; alsotimestamp
in the question. Please post your actual table definition instead of the freehand description: what you get with\d tbl
in psql. And some info on data distribution: how many different event types, how many different users, typically how many events per(user_id, event_type)
(per month)? Consider the tag info of [postgesql-performance].event_type
isint
in the question andtext
in the fiddle. Please be consistent. Do you need the count for every row in the table or just for a particular time frame or a given user or whatever? It's rarely useful to return thousands of rows like this ... One more thing: please post the output ofEXPLAIN (BUFFERS, ANALYZE)
, not justEXPLAIN
.