tokyorails/tokyorails — pull request #34 by lloydmeta (2012-05-29 00:19:34+00:00)
Title: More performance increase for 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) indicate over ~20% actual time increase and roughly half the total runtime on average.

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)