Postgres Pro
Downloads
Home   >   mailing lists

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

Help speeding up this query - maybe need another index?

From
"Pat Maddox"
Date:
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?

Pat

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

From
Chris
Date:
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/

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

From
"Pat Maddox"
Date:
On 6/13/06, Chris <dmagick@gmail.com> wrote:
> 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/
>

Here is the result from EXPLAIN ANALYZE on that query:

Aggregate  (cost=18.12..18.13 rows=1 width=32) (actual
time=4.924..4.925 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..18.12 rows=1 width=32) (actual
time=0.243..4.878 rows=15 loops=1)
         ->  Nested Loop  (cost=0.00..12.28 rows=1 width=40) (actual
time=0.222..4.613 rows=15 loops=1)
               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) (actual time=0.039..1.302 rows=285
loops=1)
                     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)
(actual time=0.009..0.009 rows=0 loops=285)
                     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) (actual
time=0.012..0.014 rows=1 loops=15)
               Index Cond: ("outer".trainer_scenario_id = s.id)
 Total runtime: 5.494 ms
(11 rows)

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

From
"Florian G. Pflug"
Date:
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;

The only indices that can help here are
trainer_hand_results: (user_id), (trainer_scenario_id)
trainer_scenarios: (id)
trainer_scenario_stats: (trainer_scenario_id), (correct_action)

Which of those help depends on the size of your tables.

greetings, Florian Pflug