with / without trigram indexes

gistfile1.txt

no trigram index:

cluster2.cluster2=> explain analyze SELECT * FROM "discussion_topics" WHERE ((((LOWER(discussion_topics.title) LIKE '%data%')) AND ("discussion_topics"."type" IS NULL)) AND ((((LOWER(discussion_topics.title) LIKE '%data%')) AND ("discussion_topics"."type" IS NULL)) AND ("discussion_topics".context_id = 245134 AND "discussion_topics".context_type = 'Course' AND (discussion_topics.workflow_state != 'deleted')))) ORDER BY discussion_topics.position DESC, discussion_topics.created_at DESC, discussion_topics.position DESC, discussion_topics.created_at DESC LIMIT 10 OFFSET 0;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                                                                             QUERY PLAN                                                                                              
 Limit  (cost=70.57..70.58 rows=1 width=741) (actual time=0.070..0.076 rows=1 loops=1)
   ->  Sort  (cost=70.57..70.58 rows=1 width=741) (actual time=0.065..0.067 rows=1 loops=1)
         Sort Key: "position", created_at
         Sort Method: quicksort  Memory: 25kB
         ->  Index Scan using index_discussion_topics_unique_subtopic_per_context on discussion_topics  (cost=0.00..70.56 rows=1 width=741) (actual time=0.047..0.052 rows=1 loops=1)
               Index Cond: ((context_id = 245134) AND ((context_type)::text = 'Course'::text))
               Filter: ((type IS NULL) AND (type IS NULL) AND ((workflow_state)::text <> 'deleted'::text) AND (lower((title)::text) ~~ '%data%'::text) AND (lower((title)::text) ~~ '%data%'::text))
 Total runtime: 0.147 ms
(8 rows)


trigram index:

cluster2.cluster2=> explain analyze SELECT * FROM "discussion_topics" WHERE ((((LOWER(discussion_topics.title) LIKE '%data%')) AND ("discussion_topics"."type" IS NULL)) AND ((((LOWER(discussion_topics.title) LIKE '%data%')) AND ("discussion_topics"."type" IS NULL)) AND ("discussion_topics".context_id = 245134 AND "discussion_topics".context_type = 'Course' AND (discussion_topics.workflow_state != 'deleted')))) ORDER BY discussion_topics.position DESC, discussion_topics.created_at DESC, discussion_topics.position DESC, discussion_topics.created_at DESC LIMIT 10 OFFSET 0;
                                                                                  QUERY PLAN                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=14.09..14.09 rows=1 width=741) (actual time=124.138..124.144 rows=1 loops=1)
   ->  Sort  (cost=14.09..14.09 rows=1 width=741) (actual time=124.133..124.135 rows=1 loops=1)
         Sort Key: "position", created_at
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on discussion_topics  (cost=10.05..14.08 rows=1 width=741) (actual time=124.115..124.117 rows=1 loops=1)
               Recheck Cond: ((context_id = 245134) AND (lower((title)::text) ~~ '%data%'::text))
               Filter: ((type IS NULL) AND (type IS NULL) AND ((workflow_state)::text <> 'deleted'::text) AND ((context_type)::text = 'Course'::text))
               ->  BitmapAnd  (cost=10.05..10.05 rows=1 width=0) (actual time=124.076..124.076 rows=0 loops=1)
                     ->  Bitmap Index Scan on index_discussion_topics_on_context_id_and_position  (cost=0.00..4.59 rows=26 width=0) (actual time=0.020..0.020 rows=7 loops=1)
                           Index Cond: (context_id = 245134)
                     ->  Bitmap Index Scan on index_trgm_discussion_topics_title  (cost=0.00..5.21 rows=46 width=0) (actual time=124.041..124.041 rows=1799 loops=1)
                           Index Cond: ((lower((title)::text) ~~ '%data%'::text) AND (lower((title)::text) ~~ '%data%'::text))
 Total runtime: 124.223 ms
(13 rows)