I seem to be getting very slow queries on a medium sized RDS box (db.m3.medium, 3.7gb ram).
This is across a table of 4,152,928 rows..
select sum(some_field) c from pages where pages.some_id=123 and pages.first_action_at > '2014-01-01 00:00:00 +1000'
Total runtime: 45031 ms.
Locally, I have around 1.1million rows, and the same query takes about 450ms..
Here's the query plan, from explain:
Aggregate (cost=475640.59..475640.60 rows=1 width=4) -> Seq Scan on pages (cost=0.00..475266.07 rows=149809 width=4) Filter: ((first_action_at > '2014-01-01 00:00:00'::timestamp without time zone) AND (some_id = 447))
Here's the response from explain analyze:
Aggregate (cost=475641.74..475641.76 rows=1 width=4) (actual time=42419.717..42419.718 rows=1 loops=1) -> Seq Scan on pages (cost=0.00..475267.22 rows=149810 width=4) (actual time=0.013..42265.908 rows=141559 loops=1) Filter: ((first_action_at > '2014-01-01 00:00:00'::timestamp without time zone) AND (some_id = 447)) Rows Removed by Filter: 4011369
Total runtime: 42419.772 ms
For reference, 141559 rows are part of the sum().
The current indexes I have are:
:some_id :some_id, :first_action_at
work_mem was previously set to 1 mb (RDS default). I've just changed this to 18 mb.
Edit: Seems to be resolved by upping
work_mem as well as added the second index above, speed is now around 800 ms.