Hi Bjorn,
I have experienced that some subqueries can be quite slow, and would suspect the NOT IN
clause. I occasionally rewrite
NOT IN (select key from unwanted_candidates)
as
IN (select key from possible_candidates except select key from unwanted_candidates)
Admittedly, I am not running latest version
Regards
Wolfgang Hamann
>> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
