Re: Dealing with complex queries

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=28723 loops=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=1591 loops=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) (actual time=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.59 rows=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=6946 loops=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.22 rows=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.24 rows=207341 loops=1)
Total runtime: 1018638.45 msec