Performance comparison between sql and redis queries to build a topic detail page.

Profile Forum History Page.

WITH REDIS
Executed SQL
EXPLAIN SELECT "shout_post"."id", "shout_post"."body", "shout_post"."date_added", "shout_post"."date_last_updated", "shout_post"."user_profile_id", "shout_post"."status", "shout_post"."points", "shout_post"."content_type_id", "shout_post"."object_id", "shout_post"."section" FROM "shout_post" WHERE "shout_post"."id" IN (2882288, 2882276, 2882274, 637787, 637651, 637513, 637478, 637447, 637439, 637351, 636576, 636569, 635341, 635252, 635213, 635206, 635194, 635155, 635095, 635084) ORDER BY "shout_post"."id" DESC
Time
1.73 ms
QUERY PLAN
Sort  (cost=165.88..165.93 rows=20 width=363)
  Sort Key: id
  ->  Bitmap Heap Scan on shout_post  (cost=85.48..165.45 rows=20 width=363)
        Recheck Cond: (id = ANY ('{2882288,2882276,2882274,637787,637651,637513,637478,637447,637439,637351,636576,636569,635341,635252,635213,635206,635194,635155,635095,635084}'::integer[]))
        ->  Bitmap Index Scan on shout_post_pkey  (cost=0.00..85.48 rows=20 width=0)
              Index Cond: (id = ANY ('{2882288,2882276,2882274,637787,637651,637513,637478,637447,637439,637351,636576,636569,635341,635252,635213,635206,635194,635155,635095,635084}'::integer[]))
              
------------------------------------------
WITHOUT REDIS
Executed SQL
EXPLAIN SELECT COUNT(*) FROM "shout_post" WHERE ("shout_post"."object_id" IN (SELECT U0."id" FROM "shout_topic" U0 WHERE U0."content_type_id" = 37 ) AND "shout_post"."content_type_id" = 35 AND "shout_post"."user_profile_id" = 3 )
Time
4739.89 ms
QUERY PLAN
Aggregate  (cost=7725.42..7725.43 rows=1 width=0)
  ->  Nested Loop  (cost=1526.67..7723.62 rows=721 width=0)
        ->  HashAggregate  (cost=1526.67..1533.88 rows=721 width=4)
              ->  Bitmap Heap Scan on shout_topic u0  (cost=17.85..1524.87 rows=721 width=4)
                    Recheck Cond: (content_type_id = 37)
                    ->  Bitmap Index Scan on shout_topic_content_type_id  (cost=0.00..17.67 rows=721 width=0)
                          Index Cond: (content_type_id = 37)
        ->  Index Scan using shout_post_user_profile_id_7d1874932ed012ba on shout_post  (cost=0.00..8.57 rows=1 width=4)
              Index Cond: ((shout_post.user_profile_id = 3) AND (shout_post.content_type_id = 35) AND (shout_post.object_id = u0.id))


Executed SQL
EXPLAIN SELECT "shout_post"."id", "shout_post"."body", "shout_post"."date_added", "shout_post"."date_last_updated", "shout_post"."user_profile_id", "shout_post"."status", "shout_post"."points", "shout_post"."content_type_id", "shout_post"."object_id", "shout_post"."section" FROM "shout_post" WHERE ("shout_post"."object_id" IN (SELECT U0."id" FROM "shout_topic" U0 WHERE U0."content_type_id" = 37 ) AND "shout_post"."content_type_id" = 35 AND "shout_post"."user_profile_id" = 3 ) ORDER BY "shout_post"."id" DESC LIMIT 20
Time
61.17 ms
QUERY PLAN
Limit  (cost=0.00..4206.47 rows=20 width=363)
  ->  Nested Loop Semi Join  (cost=0.00..151643.10 rows=721 width=363)
        ->  Index Scan using shout_post_user_profile_id_34077bed on shout_post  (cost=0.00..53282.43 rows=13417 width=363)
              Index Cond: (user_profile_id = 3)
              Filter: (content_type_id = 35)
        ->  Index Scan using shout_topic_pkey on shout_topic u0  (cost=0.00..7.33 rows=1 width=4)
              Index Cond: (u0.id = shout_post.object_id)
              Filter: (u0.content_type_id = 37)

Topic Detail Page.

WITH REDIS
Executed SQL
EXPLAIN SELECT "shout_post"."id", "shout_post"."body", "shout_post"."date_added", "shout_post"."date_last_updated", "shout_post"."user_profile_id", "shout_post"."status", "shout_post"."points", "shout_post"."content_type_id", "shout_post"."object_id", "shout_post"."section" FROM "shout_post" WHERE "shout_post"."id" IN (2882274, 2882275, 2882276, 2882277, 2882278, 2882280, 2882289, 2882290, 2882291, 2882292, 2882293, 2882294, 2882295, 2882296, 2882297, 2882298, 2882299, 2882300, 2882301, 2882302) ORDER BY "shout_post"."id" ASC
Time
1.38 ms
QUERY PLAN
Sort  (cost=165.88..165.93 rows=20 width=363)
  Sort Key: id
  ->  Bitmap Heap Scan on shout_post  (cost=85.48..165.45 rows=20 width=363)
        Recheck Cond: (id = ANY ('{2882274,2882275,2882276,2882277,2882278,2882280,2882289,2882290,2882291,2882292,2882293,2882294,2882295,2882296,2882297,2882298,2882299,2882300,2882301,2882302}'::integer[]))
        ->  Bitmap Index Scan on shout_post_pkey  (cost=0.00..85.48 rows=20 width=0)
              Index Cond: (id = ANY ('{2882274,2882275,2882276,2882277,2882278,2882280,2882289,2882290,2882291,2882292,2882293,2882294,2882295,2882296,2882297,2882298,2882299,2882300,2882301,2882302}'::integer[]))
              
              
------------------------------------------
WITHOUT REDIS

Executed SQL
SELECT COUNT(*) FROM "shout_post" WHERE ("shout_post"."object_id" = 435655 AND "shout_post"."content_type_id" = 35 )
Time
0.69 ms
QUERY PLAN
Aggregate  (cost=144.86..144.87 rows=1 width=0)
  ->  Index Scan using shout_post_object_id_776ded99 on shout_post  (cost=0.00..144.71 rows=57 width=0)
        Index Cond: ((object_id = 435655) AND (content_type_id = 35))
        
Executed SQL
SELECT "shout_post"."id", "shout_post"."body", "shout_post"."date_added", "shout_post"."date_last_updated", "shout_post"."user_profile_id", "shout_post"."status", "shout_post"."points", "shout_post"."content_type_id", "shout_post"."object_id", "shout_post"."section" FROM "shout_post" WHERE ("shout_post"."object_id" = 435655 AND "shout_post"."content_type_id" = 35 ) ORDER BY "shout_post"."id" ASC LIMIT 1 OFFSET 1
Time
0.76 ms

Executed SQL
SELECT "shout_post"."id", "shout_post"."body", "shout_post"."date_added", "shout_post"."date_last_updated", "shout_post"."user_profile_id", "shout_post"."status", "shout_post"."points", "shout_post"."content_type_id", "shout_post"."object_id", "shout_post"."section" FROM "shout_post" WHERE ("shout_post"."object_id" = 435655 AND "shout_post"."content_type_id" = 35 ) ORDER BY "shout_post"."id" ASC LIMIT 20
Time
1.35 ms
QUERY PLAN
Limit  (cost=146.23..146.28 rows=20 width=363)
  ->  Sort  (cost=146.23..146.37 rows=57 width=363)
        Sort Key: id
        ->  Index Scan using shout_post_object_id_776ded99 on shout_post  (cost=0.00..144.71 rows=57 width=363)
              Index Cond: ((object_id = 435655) AND (content_type_id = 35))