gistfile1.txt

pg=# explain analyze SELECT * FROM "companies" WHERE ((lower(companies.title) LIKE 'c%') AND ("companies"."custom" = 'false')) ORDER BY title ASC LIMIT 100                                       
;
                                                                              QUERY PLAN                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=50411.91..50412.16 rows=100 width=817) (actual time=16646.708..16646.900 rows=100 loops=1)
   ->  Sort  (cost=50411.91..50413.52 rows=647 width=817) (actual time=16646.701..16646.773 rows=100 loops=1)
         Sort Key: title
         Sort Method:  top-N heapsort  Memory: 66kB
         ->  Bitmap Heap Scan on companies  (cost=7678.75..50387.18 rows=647 width=817) (actual time=1084.527..16643.838 rows=795 loops=1)
               Filter: ((NOT custom) AND (lower((title)::text) ~~ 'c%'::text))
               ->  Bitmap Index Scan on index_companies_on_lower_title  (cost=0.00..7678.59 rows=127362 width=0) (actual time=1076.445..1076.445 rows=136454 loops=1)
                     Index Cond: ((lower((title)::text) ~>=~ 'c'::text) AND (lower((title)::text) ~<~ 'd'::text))
 Total runtime: 16649.195 ms
(9 rows)

pg=# create index companies_custom on companies(custom);
CREATE INDEX
pg=# explain analyze SELECT * FROM "companies" WHERE ((lower(companies.title) LIKE 'c%') AND ("companies"."custom" = 'false')) ORDER BY title ASC LIMIT 100
;
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=995.79..996.04 rows=100 width=817) (actual time=32.227..32.423 rows=100 loops=1)
   ->  Sort  (cost=995.79..997.47 rows=669 width=817) (actual time=32.223..32.293 rows=100 loops=1)
         Sort Key: title
         Sort Method:  top-N heapsort  Memory: 66kB
         ->  Index Scan using companies_custom on companies  (cost=0.00..970.22 rows=669 width=817) (actual time=0.120..30.327 rows=795 loops=1)
               Index Cond: (custom = false)
               Filter: ((NOT custom) AND (lower((title)::text) ~~ 'c%'::text))
 Total runtime: 32.531 ms
(8 rows)