The following sql is expanded from an example from Tonkuma to my
complete application. It runs for over 6 minutes when my separate sql
statements wrapped with some PHP code can do the same thing in less than
3 seconds. For reasons which I need not go into here, I need to do this
is on sql statement. I take the performance hit because of the 7 nested
full table scans even though cow_bhid is the first primary key column of
taa, tba and tca;  (bhid, herd_owner) is the whole primary key of
animals_priv (tab, tbb, tcb). What I would like to know is how to
improve the performance of this query.

select t1.animal_id, t1.locname, t1.bhid,
       ta.herd_id, ta.datex, ta.time_code,
       ta.datex+283 days as date_due,
       tb.herd_id, tb.datex, tb.time_code,
       tb.datex+283 days as date_due,
       tc.herd_id, tc.in_date, tc.out_end_date,
       tc.in_date+282 days as start_date_due,
       tc.out_end_date+283 days as end_date_due
  from is3.animals2                             t1
  left outer join
       (select taa.*, tab.herd_id,
               ROWNUMBER() OVER(PARTITION BY taa.cow_bhid) rn
          from is3.service_ai           taa
          left outer
          join is3.animals_priv         tab
                on tab.herd_owner_id=1
               and tab.bhid=taa.bull_bhid
//         where taa.cow_bhid=t1.bhid
         order by taa.datex)                    ta
  full outer join
       (select tba.*, tbb.herd_id,
               ROWNUMBER() OVER(PARTITION BY tba.cow_bhid) rn
          from is3.service_ai           tba
          left outer
          join is3.animals_priv         tbb
                on tbb.herd_owner_id=1
               and tbb.bhid=tba.bull_bhid
//         where tba.cow_bhid=t1.bhid
         order by tba.datex)                    tb
         on tb.cow_bhid=ta.cow_bhid
        and tb.rn=ta.rn
  full outer join
       (select tca.*,tcb.herd_id,
               ROWNUMBER() OVER(PARTITION BY tca.cow_bhid) rn
          from is3.service_pasture      tca
          left outer
          join is3.animals_priv         tcb
                on tcb.herd_owner_id=1
               and tcb.bhid=tca.bull_bhid
//          where tca.cow.bhid=t1.bhid
          order by tca.in_date)                tc
          on tc.cow_bhid=coalesce(ta.cow_bhid, tb.cow_bhid)
         and tc.rn=coalesce(ta.rn,tb.rn)
          on t1.bhid=coalesce(ta.cow_bhid, tb.cow_bhid, tc.cow_bhid)
 where t1.bhid in
        (select bhid
           from is3.animal_sets
          where set_name='Bred_Cows'
            and userid='jhough')
 order by t1.animal_id