Re: Dealing with complex queries - Mailing list pgsql-general

On Mon, 3 Feb 2003, Francisco Reyes wrote:

Follow up to my question.
An explain analyze of the query (sames as before except upped limit to
200)

One thing which I don't understand and seems likely to be a big problem,
is some of the query elementes seem to be doing thousand of loops (ie 28K
loops)

> As an example of the query and explain output:
> select  jc.type, jc.id,
>         jc.last, jc.first,
>         jc.track, jc.date,
>         jc.race, jc.day,
>         ppl.carried_as, pe.jt_id
> from    jc_people jc, hraces hr,
>         rkeys rk, pplkeys ppl,
>         people pe
> where   jc.type = 'j' and
>         jc.track = rk.track and
>         jc.date  = rk.date  and
>         jc.race  = rk.race  and
>         hr.race_key = rk.race_key and
>         ppl.ppl_key = hr.jockey_key and
>         substring(ppl.carried_as from 1 for 3)
>                 = substring(jc.last from 1 for 3) and
>         pe.type = 'j' and
>         ppl.type= 'j' and
>         pe.jt_id = 0 and
>         pe.ppl_key = ppl.ppl_key
> limit 10;


 Limit  (cost=0.00..30224.00 rows=1 width=141) (actual
time=356090.83..1018637.83 rows=44 loops=1)
   ->  Merge Join  (cost=0.00..30224.00 rows=1 width=141) (actual time=356090.81..1018637.26 rows=44 loops=1)
         Merge Cond: ("outer".ppl_key = "inner".jockey_key)
         Join Filter: ("inner".race_key = "outer".race_key)
         ->  Nested Loop  (cost=0.00..22384540.45 rows=1833 width=133) (actual time=532.06..1014419.54 rows=21951
loops=1)
               Join Filter: ("outer".race = "inner".race)
               ->  Nested Loop  (cost=0.00..22266406.22 rows=15301 width=116) (actual time=531.78..1005708.99
rows=28723loops=1) 
                     Join Filter: ("substring"(("outer".carried_as)::text, 1, 3) = "substring"(("inner".last)::text, 1,
3))
                     ->  Nested Loop  (cost=0.00..21773591.67 rows=456 width=51) (actual time=463.34..737215.23
rows=1591loops=1) 
                           Join Filter: ("outer".ppl_key = "inner".ppl_key)
                           ->  Index Scan using people_pplkey on people pe  (cost=0.00..2991.02 rows=2234 width=8)
(actualtime=0.19..397.73 rows=1591 loops=1) 
                                 Filter: (("type" = 'j'::bpchar) AND (jt_id = 0))
                           ->  Seq Scan on pplkeys ppl  (cost=0.00..8929.70 rows=65324 width=43) (actual
time=0.06..421.59rows=6770 loops=1591) 
                                 Filter: ("type" = 'j'::bpchar)
                     ->  Seq Scan on jc_people jc  (cost=0.00..963.96 rows=6716 width=65) (actual time=0.18..113.88
rows=6946loops=1591 
                           Filter: ("type" = 'j'::bpchar)
               ->  Index Scan using rk_track_date_eve_race on rkeys rk (cost=0.00..7.70 rows=1 width=17) (actual
time=0.11..0.22rows=8 loops=28723) 
                     Index Cond: (("outer".track = rk.track) AND ("outer".date = rk.date))
         ->  Index Scan using hr_jockey_key on hraces hr  (cost=0.00..26712.29 rows=492390 width=8) (actual
time=0.22..2569.24rows=207341 loops=1) 
 Total runtime: 1018638.45 msec