posts_read7EA

[postgres@sofa ~]$ sql_tdwtf posts_read7EA
# http://what.thedailywtf.com/t/discourse-sql-1-minute-query/3730/36 - second
WITH exclusions AS ( /* Which categories to exclude from counters */
        SELECT user_id, id, topic_id, post_number
        FROM posts
        WHERE raw LIKE '%[UUID removed to prevent this thread accidentally being marked as excluded]%' AND
        user_id IN  (
                SELECT gu.user_id
                FROM group_users gu
                WHERE group_id IN(
                        SELECT g.id
                        FROM groups g
                        WHERE g.name IN ('admins')
                )
        )
),
posts_read AS (
        SELECT u.username, u.id AS user_id, agg.count, agg.ln2, agg.ln3
        FROM (
                SELECT pt.user_id, count(*), log(2, count(*)) AS ln2, log(3.0, count(*)) AS ln3
                FROM post_timings pt
                LEFT JOIN exclusions e on e.topic_id = pt.topic_id
                        WHERE e.topic_id IS NULL
                GROUP BY pt.user_id
        ) agg
        JOIN users u ON u.id = agg.user_id
)
SELECT * from posts_read
WHERE ln3 >= 10 and ln3 <11
ORDER BY ln3 DESC


                                                                                     QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=479394.37..479394.38 rows=3 width=124) (actual time=67552.522..67552.583 rows=52 loops=1)
   Sort Key: posts_read.ln3
   Sort Method: quicksort  Memory: 20kB
   CTE exclusions
     ->  Nested Loop Semi Join  (cost=0.28..26573.92 rows=20 width=16) (actual time=7.106..489.010 rows=7 loops=1)
           ->  Seq Scan on posts  (cost=0.00..26354.56 rows=23 width=16) (actual time=3.608..488.466 rows=12 loops=1)
                 Filter: (raw ~~ '%[UUID removed to prevent this thread accidentally being marked as excluded]%'::text)
                 Rows Removed by Filter: 230753
           ->  Nested Loop  (cost=0.28..9.53 rows=1 width=4) (actual time=0.032..0.032 rows=1 loops=12)
                 ->  Seq Scan on groups g  (cost=0.00..1.21 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=12)
                       Filter: ((name)::text = 'admins'::text)
                       Rows Removed by Filter: 12
                 ->  Index Only Scan using index_group_users_on_group_id_and_user_id on group_users gu  (cost=0.28..8.30 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=12)
                       Index Cond: ((group_id = g.id) AND (user_id = posts.user_id))
                       Heap Fetches: 7
   CTE posts_read
     ->  Hash Join  (cost=452772.12..452803.17 rows=690 width=85) (actual time=67092.538..67548.084 rows=1341 loops=1)
           Hash Cond: (pt.user_id = u.id)
           ->  HashAggregate  (cost=452665.45..452679.25 rows=690 width=4) (actual time=67081.573..67532.473 rows=1341 loops=1)
                 ->  Hash Anti Join  (cost=0.65..334109.22 rows=11855623 width=4) (actual time=489.241..50252.156 rows=10125370 loops=1)
                       Hash Cond: (pt.topic_id = e.topic_id)
                       ->  Seq Scan on post_timings pt  (cost=0.00..184080.85 rows=11949085 width=8) (actual time=0.121..17731.345 rows=11949028 loops=1)
                       ->  Hash  (cost=0.40..0.40 rows=20 width=4) (actual time=489.091..489.091 rows=7 loops=1)
                             Buckets: 1024  Batches: 1  Memory Usage: 1kB
                             ->  CTE Scan on exclusions e  (cost=0.00..0.40 rows=20 width=4) (actual time=7.114..489.064 rows=7 loops=1)
           ->  Hash  (cost=84.63..84.63 rows=1763 width=13) (actual time=10.950..10.950 rows=1763 loops=1)
                 Buckets: 1024  Batches: 1  Memory Usage: 60kB
                 ->  Seq Scan on users u  (cost=0.00..84.63 rows=1763 width=13) (actual time=0.011..8.307 rows=1763 loops=1)
   ->  CTE Scan on posts_read  (cost=0.00..17.25 rows=3 width=124) (actual time=67094.022..67552.244 rows=52 loops=1)
         Filter: ((ln3 >= 10::numeric) AND (ln3 < 11::numeric))
         Rows Removed by Filter: 1289
 Total runtime: 67552.923 ms
(32 rows)

Elapsed: 67.574s
Backup taken:  2015-02-14 03:59:29.758072
[postgres@sofa ~]$