Thread: How can I speed up this query?
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)
On Wed, Jun 28, 2006 at 01:52:10AM -0600, Pat Maddox wrote: > 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. <snip> > -> 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) That's an awful long time to find a few rows in a table via an index. How big is this table/index? What version is this? If you have a older version perhaps you're suffering from index bloat and you need to REINDEX that table/index. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.