Re: Help speeding up this query - maybe need another index?

Pat Maddox wrote:
> Here's my SQL query. I don't think it's too gigantic, but it is kind
> of beastly:
>
> 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;
>
> When I EXPLAIN it, I get:
>
> Aggregate (cost=18.12..18.13 rows=1 width=32)
> -> Nested Loop (cost=0.00..18.12 rows=1 width=32)
> -> Nested Loop (cost=0.00..12.28 rows=1 width=40)
> Join Filter: (("outer"."action")::text =
> ("inner".correct_action)::text)
> -> Seq Scan on trainer_hand_results r
> (cost=0.00..6.56 rows=1 width=181)
> Filter: (user_id = 1)
> -> Index Scan using
> trainer_scenario_stats_trainer_scenario_id_index on
> trainer_scenario_stats stats (cost=0.00..5.71 rows=1 width=149)
> Index Cond: (stats.trainer_scenario_id =
> "outer".trainer_scenario_id)
> -> Index Scan using trainer_scenarios_pkey on
> trainer_scenarios s (cost=0.00..5.82 rows=1 width=4)
> Index Cond: ("outer".trainer_scenario_id = s.id)
> (10 rows)
>
> I don't have a lot of experience with getting queries to go faster.
> The things that jump out at me though are two nested loops and a
> sequential scan. What could I do to speed this up?

Have you analyzed the tables in question?

Post the result of 'explain analyze' rather than just explain.

--
Postgresql & php tutorials
http://www.designmagick.com/