Possible SQL optimisation getting posts, index miss

slow-sql

(Dean Taylor) #1

Noted the following query took a long time (51.6 ms)

Executing action: show
T+67.1 ms
Reader
51.6 ms
lib/topic_view.rb:71:in `initialize'
app/controllers/topics_controller.rb:59:in `new'
app/controllers/topics_controller.rb:59:in `show'
lib/middleware/anonymous_cache.rb:129:in `call'
config/initializers/100-quiet_logger.rb:10:in `call_with_quiet_assets'
config/initializers/100-silence_logger.rb:26:in `call'
lib/middleware/request_tracker.rb:73:in `call'
lib/scheduler/defer.rb:85:in `process_client'
lib/middleware/unicorn_oobgc.rb:95:in `process_client'
SELECT "posts".* FROM "posts" WHERE "posts"."topic_id" = 67458 AND "posts"."id" IN (452571, 452627, 452652, 452721, 452777, 452804, 452847, 452902, 452960, 452993, 453027, 453071, 453107, 453146, 453183, 453219, 453247, 453328, 453395, 453611) AND (user_id = 1 OR post_type IN (1,2,3,4))  ORDER BY sort_order

Ran an “EXPLAIN” to find that index optimisation looks a little “off”:

 Sort  (cost=98.84..98.84 rows=1 width=836) (actual time=1.314..1.340 rows=20 loops=1)
   Sort Key: sort_order
   Sort Method: quicksort  Memory: 30kB
   ->  Bitmap Heap Scan on posts  (cost=94.78..98.83 rows=1 width=836) (actual time=0.682..1.147 rows=20 loops=1)
         Recheck Cond: ((topic_id = 67458) AND (id = ANY ('{452571,452627,452652,452721,452777,452804,452847,452902,452960,452993,453027,453071,453107,453146,453183,453219,453247,453328,453395,453611}'::integer[])))
         Filter: ((user_id = 1) OR (post_type = ANY ('{1,2,3,4}'::integer[])))
         ->  BitmapAnd  (cost=94.78..94.78 rows=1 width=0) (actual time=0.601..0.601 rows=0 loops=1)
               ->  Bitmap Index Scan on index_posts_on_topic_id_and_post_number  (cost=0.00..5.91 rows=198 width=0) (actual time=0.231..0.231 rows=244 loops=1)
                     Index Cond: (topic_id = 67458)
               ->  Bitmap Index Scan on posts_pkey  (cost=0.00..88.62 rows=20 width=0) (actual time=0.285..0.285 rows=20 loops=1)
                     Index Cond: (id = ANY ('{452571,452627,452652,452721,452777,452804,452847,452902,452960,452993,453027,453071,453107,453146,453183,453219,453247,453328,453395,453611}'::integer[]))
 Total runtime: 1.789 ms
(12 rows)

Simple removal of the topic_id check would allow an single index hit:

EXPLAIN ANALYZE SELECT "posts".* FROM "posts" WHERE "posts"."id" IN (452571, 452627, 452652, 452721, 452777, 452804, 452847, 452902, 452960, 452993, 453027, 453071, 453107, 453146, 453183, 453219, 453247, 453328, 453395, 453611) AND (user_id = 1 OR post_type IN (1,2,3,4))  ORDER BY sort_order;
 Sort  (cost=169.30..169.35 rows=20 width=836) (actual time=0.790..0.803 rows=20 loops=1)
   Sort Key: sort_order
   Sort Method: quicksort  Memory: 30kB
   ->  Index Scan using posts_pkey on posts  (cost=0.45..168.87 rows=20 width=836) (actual time=0.172..0.695 rows=20 loops=1)
         Index Cond: (id = ANY ('{452571,452627,452652,452721,452777,452804,452847,452902,452960,452993,453027,453071,453107,453146,453183,453219,453247,453328,453395,453611}'::integer[]))
         Filter: ((user_id = 1) OR (post_type = ANY ('{1,2,3,4}'::integer[])))
 Total runtime: 0.922 ms
(7 rows)

Obviously I’m unsure of the exact code path or requirement for the index check, or indeed if a index change is required here.

Possible SQL optimisation getting posts?