Faster Events page

Let's squeeze a bit more performance from this page. I noticed the page was querying the Rsvps table (the largest table in terms of rows and likely to remain the largest) using where on both event_id and response, and there was no index on these two.

This commit adds an index on those two columns.

Postgres EXPLAIN ANALYZE numbers (ran 3 times before and after) show over ~20% faster actual times and roughly half the total runtime on average after this change.



BEFORE:


psql_tokyorails=# EXPLAIN ANALYZE SELECT "rsvps".* FROM "rsvps" WHERE "rsvps"."meetup_id" = '63494052' AND "rsvps"."response" = 'yes';
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Seq Scan on rsvps  (cost=0.00..15.03 rows=46 width=62) (actual time=0.191..0.224 rows=51 loops=1)
   Filter: (((meetup_id)::text = '63494052'::text) AND ((response)::text = 'yes'::text))
 Total runtime: 0.267 ms
(3 rows)

psql_tokyorails=# EXPLAIN ANALYZE SELECT "rsvps".* FROM "rsvps" WHERE "rsvps"."meetup_id" = '63494052' AND "rsvps"."response" = 'yes';
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Seq Scan on rsvps  (cost=0.00..15.03 rows=46 width=62) (actual time=0.190..0.223 rows=51 loops=1)
   Filter: (((meetup_id)::text = '63494052'::text) AND ((response)::text = 'yes'::text))
 Total runtime: 0.265 ms
(3 rows)

psql_tokyorails=# EXPLAIN ANALYZE SELECT "rsvps".* FROM "rsvps" WHERE "rsvps"."meetup_id" = '63494052' AND "rsvps"."response" = 'yes';
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Seq Scan on rsvps  (cost=0.00..15.03 rows=46 width=62) (actual time=0.188..0.220 rows=51 loops=1)
   Filter: (((meetup_id)::text = '63494052'::text) AND ((response)::text = 'yes'::text))
 Total runtime: 0.261 ms
(3 rows)





AFTER:

psql_tokyorails=# EXPLAIN ANALYZE SELECT "rsvps".* FROM "rsvps" WHERE "rsvps"."meetup_id" = '63494052' AND "rsvps"."response" = 'yes';
                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_rsvps_on_meetup_id_and_response on rsvps  (cost=0.00..8.27 rows=1 width=62) (actual time=0.043..0.070 rows=51 loops=1)
   Index Cond: (((meetup_id)::text = '63494052'::text) AND ((response)::text = 'yes'::text))
 Total runtime: 0.133 ms
(3 rows)

psql_tokyorails=# EXPLAIN ANALYZE SELECT "rsvps".* FROM "rsvps" WHERE "rsvps"."meetup_id" = '63494052' AND "rsvps"."response" = 'yes';
                                                                   QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on rsvps  (cost=4.72..12.41 rows=46 width=62) (actual time=0.054..0.065 rows=51 loops=1)
   Recheck Cond: (((meetup_id)::text = '63494052'::text) AND ((response)::text = 'yes'::text))
   ->  Bitmap Index Scan on index_rsvps_on_meetup_id_and_response  (cost=0.00..4.71 rows=46 width=0) (actual time=0.038..0.038 rows=51 loops=1)
         Index Cond: (((meetup_id)::text = '63494052'::text) AND ((response)::text = 'yes'::text))
 Total runtime: 0.131 ms
(5 rows)

psql_tokyorails=# EXPLAIN ANALYZE SELECT "rsvps".* FROM "rsvps" WHERE "rsvps"."meetup_id" = '63494052' AND "rsvps"."response" = 'yes';
                                                                   QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on rsvps  (cost=4.72..12.41 rows=46 width=62) (actual time=0.053..0.065 rows=51 loops=1)
   Recheck Cond: (((meetup_id)::text = '63494052'::text) AND ((response)::text = 'yes'::text))
   ->  Bitmap Index Scan on index_rsvps_on_meetup_id_and_response  (cost=0.00..4.71 rows=46 width=0) (actual time=0.042..0.042 rows=51 loops=1)
         Index Cond: (((meetup_id)::text = '63494052'::text) AND ((response)::text = 'yes'::text))
 Total runtime: 0.115 ms
(5 rows)