I am trying to move a small system from Oracle to PostgreSQL and I have come upon a sql that runs really slow compared to on 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=6474 loops=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.003 rows=3 loops=1)
-> Index Scan using idx_seduttak_avlsnr on sed_uttak s (cost=0.42..2246.53 rows=1 width=14) (actual time=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) (actual time=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.589 rows=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) (actual time=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=310 loops=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=279120 loops=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@xxxxxxxxxx
-----------------------------------------------------------------------------------------------
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"
-----------------------------------------------------------------------------------------------
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
