Slow SQL?

I am trying to move a small system from Oracle to PostgreSQL and I have come upon a sql that runs really slow compared
toon the Oracle database and 
I am not able to interpret why this is slow.

The SQL looks like this:


Select a.status, a.plass, a.navn, a.avlsnr,
           date_part('day',(now() - s.dato)) dato_diff, v.tekst, COALESCE(a.avlsverdi,0)
From   sed_uttak s, sem_avlsverdi a, semin_vare v
where a.aktiv = 1
And    s.dato  = (Select Max(y.dato)
                          From sed_uttak y
                          Where y.avlsnr = s.avlsnr)
And    a.avlsnr = s.avlsnr
And    s.sedtype  = v.tallkode
And     a.avlsnr Not In (Select avlsnr
                                    From dyr_pause_mot)
Union
Select a.status, a.plass, a.navn, a.avlsnr,
           date_part('day',(now() - s.dato)) dato_diff, 'Tappe pause', COALESCE(a.avlsverdi,0)
From   sed_uttak s, sem_avlsverdi a,  dyr_pause_mot p
Where s.dato = (Select Max(x.dato)
                          From sed_uttak x
                          Where x.avlsnr = s.avlsnr)
And      a.avlsnr = s.avlsnr
And      a.avlsnr = p.avlsnr
Union
Select a.status, a.plass, a.navn, a.avlsnr, null dato_diff, 'IKKE TAPPET', COALESCE(a.avlsverdi,0)
From   sem_avlsverdi a
Where a.aktiv = 1
And   a.avlsnr Not In (Select avlsnr From sed_uttak)
And   a.avlsnr Not In (Select avlsnr From dyr_pause_mot)



And the explain result looks like this:

HashAggregate  (cost=7288068.92..7288092.10 rows=2318 width=36) (actual time=10740.366..10741.879 rows=6475 loops=1)
                                                       
  Group Key: a.status, a.plass, a.navn, a.avlsnr, (date_part('day'::text, (now() - (s.dato)::timestamp with time
zone))),v.tekst, (COALESCE((a.avlsverdi)::integer, 0))        
  ->  Append  (cost=1.46..7288028.35 rows=2318 width=36) (actual time=0.203..10730.906 rows=8915 loops=1)
                                                       
        ->  Nested Loop  (cost=1.46..7274678.41 rows=698 width=82) (actual time=0.203..10638.870 rows=8602 loops=1)
                                                       
              Join Filter: (s.sedtype = v.tallkode)
                                                       
              Rows Removed by Join Filter: 127006
                                                       
              ->  Nested Loop  (cost=1.46..7274438.07 rows=698 width=26) (actual time=0.189..10607.509 rows=6164
loops=1)                                                      
                    ->  Seq Scan on sem_avlsverdi a  (cost=1.04..153.19 rows=3238 width=16) (actual time=0.024..4.027
rows=6474loops=1)                                        
                          Filter: ((NOT (hashed SubPlan 5)) AND (aktiv = 1))
                                                       
                          Rows Removed by Filter: 3
                                                       
                          SubPlan 5
                                                       
                            ->  Seq Scan on dyr_pause_mot dyr_pause_mot_1  (cost=0.00..1.03 rows=3 width=4) (actual
time=0.002..0.003rows=3 loops=1)                           
                    ->  Index Scan using idx_seduttak_avlsnr on sed_uttak s  (cost=0.42..2246.53 rows=1 width=14)
(actualtime=1.404..1.637 rows=1 loops=6474)                  
                          Index Cond: (avlsnr = a.avlsnr)
                                                       
                          Filter: (dato = (SubPlan 4))
                                                       
                          Rows Removed by Filter: 42
                                                       
                          SubPlan 4
                                                       
                            ->  Aggregate  (cost=43.09..43.10 rows=1 width=8) (actual time=0.037..0.037 rows=1
loops=279035)                                                   
                                  ->  Index Scan using idx_seduttak_avlsnr on sed_uttak y  (cost=0.42..42.96 rows=52
width=8)(actual time=0.003..0.029 rows=76 loops=279035)   
                                        Index Cond: (avlsnr = s.avlsnr)
                                                       
              ->  Materialize  (cost=0.00..1.33 rows=22 width=60) (actual time=0.000..0.001 rows=22 loops=6164)
                                                       
                    ->  Seq Scan on semin_vare v  (cost=0.00..1.22 rows=22 width=60) (actual time=0.002..0.005 rows=22
loops=1)                                                
        ->  Nested Loop  (cost=0.70..6761.33 rows=1 width=24) (actual time=0.342..1.801 rows=3 loops=1)
                                                       
              ->  Nested Loop  (cost=0.28..25.96 rows=3 width=20) (actual time=0.008..0.015 rows=3 loops=1)
                                                       
                    ->  Seq Scan on dyr_pause_mot p  (cost=0.00..1.03 rows=3 width=4) (actual time=0.002..0.002 rows=3
loops=1)                                                
                    ->  Index Scan using idx_avlsverdi_avlsnr on sem_avlsverdi a_1  (cost=0.28..8.30 rows=1 width=16)
(actualtime=0.003..0.003 rows=1 loops=3)                 
                          Index Cond: (avlsnr = p.avlsnr)
                                                       
              ->  Index Scan using idx_seduttak_avlsnr on sed_uttak s_1  (cost=0.42..2245.11 rows=1 width=12) (actual
time=0.377..0.589rows=1 loops=3)                         
                    Index Cond: (avlsnr = a_1.avlsnr)
                                                       
                    Filter: (dato = (SubPlan 3))
                                                       
                    Rows Removed by Filter: 27
                                                       
                    SubPlan 3
                                                       
                      ->  Aggregate  (cost=43.09..43.10 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=85)
                                                       
                            ->  Index Scan using idx_seduttak_avlsnr on sed_uttak x  (cost=0.42..42.96 rows=52 width=8)
(actualtime=0.002..0.015 rows=37 loops=85)             
                                  Index Cond: (avlsnr = s_1.avlsnr)
                                                       
        ->  Seq Scan on sem_avlsverdi a_2  (cost=6393.04..6565.43 rows=1619 width=16) (actual time=87.448..89.059
rows=310loops=1)                                             
              Filter: ((NOT (hashed SubPlan 1)) AND (NOT (hashed SubPlan 2)) AND (aktiv = 1))
                                                       
              Rows Removed by Filter: 6167
                                                       
              SubPlan 1
                                                       
                ->  Seq Scan on sed_uttak  (cost=0.00..5694.20 rows=279120 width=4) (actual time=0.005..36.384
rows=279120loops=1)                                             
              SubPlan 2
                                                       
                ->  Seq Scan on dyr_pause_mot  (cost=0.00..1.03 rows=3 width=4) (actual time=0.002..0.003 rows=3
loops=1)                                                      
Planning time: 0.927 ms
                                                       
Execution time: 10742.300ms
                                                      


Appreciate any pointers on where to look... :)


Regards,

BTJ

--
-----------------------------------------------------------------------------------------------
Bjørn T Johansen

btj@havleik.no
-----------------------------------------------------------------------------------------------
Someone wrote:
"I understand that if you play a Windows CD backwards you hear strange Satanic messages"
To which someone replied:
"It's even worse than that; play it forwards and it installs Windows"
-----------------------------------------------------------------------------------------------