I end up calling this query quite often, so I need a way to speed it
up. Don't know if I can take advantage of indexes or better joins or
something (I don't even know if there are better joins), I'm really a
newbie to DB stuff. Hoping somebody can help me make this much
quicker.
EXPLAIN ANALYZE SELECT count(r) FROM trainer_hand_results r,
trainer_scenarios s, trainer_scenario_stats stats WHERE r.user_id=1
and r.trainer_scenario_id=s.id AND s.id=stats.trainer_scenario_id AND
r.action=stats.correct_action AND r.created_at >= '2006-06-07
11:21:22' AND s.hero_position='BB';
Aggregate (cost=36.29..36.30 rows=1 width=32) (actual
time=545.484..545.485 rows=1 loops=1)
-> Nested Loop (cost=0.00..36.29 rows=1 width=32) (actual
time=544.177..545.292 rows=6 loops=1)
-> Merge Join (cost=0.00..30.23 rows=2 width=40) (actual
time=424.720..471.695 rows=60 loops=1)
Merge Cond: ("outer".trainer_scenario_id =
"inner".trainer_scenario_id)
Join Filter: (("outer"."action")::text =
("inner".correct_action)::text)
-> Index Scan using
trainer_hand_results_trainer_scenario_id_index on trainer_hand_results
r (cost=0.00..19.12 rows=570 width=44) (actual time=415.677..420.197
rows=578 loops=1)
Filter: ((user_id = 1) AND (created_at >=
'2006-06-07 11:21:22'::timestamp without time zone))
-> Index Scan using
trainer_scenario_stats_trainer_scenario_id_index on
trainer_scenario_stats stats (cost=0.00..26.58 rows=117 width=12)
(actual time=8.835..49.954 rows=185 loops=1)
-> Index Scan using trainer_scenarios_pkey on
trainer_scenarios s (cost=0.00..3.02 rows=1 width=4) (actual
time=1.222..1.222 rows=0 loops=60)
Index Cond: ("outer".trainer_scenario_id = s.id)
Filter: ((hero_position)::text = 'BB'::text)
Total runtime: 546.082 ms
(12 rows)