mail list traffic

Gregory Stark wrote:

> I would be curious to see the average lifespan of threads over time.

I happen to have the mail archives stored in a database, so I've
expressed this in SQL and below are some results for hackers and
general, 2007-2008. count is the number of distinct threads whose
oldest message is in the specified month. A thread is started as
soon as a message has an In-Reply-To field pointing to an
existing Message-Id.

Results for pgsql-hackers:

month | avg span | median span | count
---------+------------------+-----------------+-------
2007-01 | 7 days 10:00:00 | 1 day 04:18:00 | 211
2007-02 | 7 days 10:00:00 | 1 day 00:23:48 | 186
2007-03 | 16 days 30:00:00 | 1 day 05:45:37 | 171
2007-04 | 13 days 26:00:00 | 19:07:00 | 142
2007-05 | 19 days 30:00:00 | 1 day 04:46:36 | 122
2007-06 | 15 days 19:00:00 | 23:38:13 | 111
2007-07 | 19 days 25:00:00 | 21:04:04 | 106
2007-08 | 13 days 30:00:00 | 20:26:39 | 133
2007-09 | 21 days 32:00:00 | 1 day 16:43:10 | 121
2007-10 | 13 days 19:00:00 | 17:23:24 | 148
2007-11 | 16 days 15:00:00 | 16:23:00 | 140
2007-12 | 17 days 16:00:00 | 1 day 07:28:05 | 81
2008-01 | 13 days 12:00:00 | 23:02:33 | 127
2008-02 | 9 days 11:00:00 | 12:44:28 | 130
2008-03 | 10 days 14:00:00 | 22:57:18 | 140
2008-04 | 10 days 14:00:00 | 1 day 00:32:34 | 132
2008-05 | 13 days 09:00:00 | 1 day 20:57:57 | 113
2008-06 | 7 days 27:00:00 | 1 day 05:42:46 | 102
2008-07 | 13 days 26:00:00 | 2 days 07:43:34 | 133
2008-08 | 9 days 33:00:00 | 1 day 07:47:09 | 121
2008-09 | 7 days 25:00:00 | 1 day 19:00:50 | 125
2008-10 | 6 days 14:00:00 | 1 day 10:31:01 | 178

Results for pgsql-general:

month | avg span | median span | count
---------+-----------------+-------------+-------
2007-01 | 1 day 25:00:00 | 10:57:11 | 329
2007-02 | 2 days 28:00:00 | 10:50:38 | 295
2007-03 | 3 days 08:00:00 | 14:54:08 | 310
2007-04 | 6 days 18:00:00 | 17:40:55 | 244
2007-05 | 3 days 22:00:00 | 16:43:54 | 287
2007-06 | 2 days 13:00:00 | 11:26:46 | 297
2007-07 | 2 days 19:00:00 | 11:59:40 | 263
2007-08 | 3 days 14:00:00 | 16:35:16 | 335
2007-09 | 3 days 14:00:00 | 13:23:09 | 245
2007-10 | 2 days 16:00:00 | 08:46:09 | 302
2007-11 | 3 days 07:00:00 | 08:28:06 | 294
2007-12 | 2 days 31:00:00 | 10:25:14 | 255
2008-01 | 2 days 14:00:00 | 13:23:12 | 248
2008-02 | 2 days 14:00:00 | 10:02:16 | 257
2008-03 | 1 day 25:00:00 | 13:20:06 | 245
2008-04 | 1 day 30:00:00 | 08:26:06 | 238
2008-05 | 3 days 22:00:00 | 18:58:27 | 211
2008-06 | 2 days 24:00:00 | 14:46:02 | 191
2008-07 | 1 day 29:00:00 | 10:37:17 | 221
2008-08 | 1 day 22:00:00 | 14:14:45 | 205
2008-09 | 1 day 24:00:00 | 14:26:26 | 202
2008-10 | 1 day 19:00:00 | 12:32:56 | 219

"median span" is the median computed with the pl/R median function
applied to intervals as a number of seconds and then cast back to
intervals for display. I believe the median is good to mitigate the
contribution of messages with wrong dates and posters that reply to
very old messages. And median span appears to differs a lot from the
average span.

If people feel like playing with the database to build other queries,
feel free to bug me off-list about it. I can arrange to make a dump
available or share the scripts to build it yourself from the
mailboxes archives.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org