IN vs =

From: "Lukas" <lukas(at)fmf(dot)vtu(dot)lt>
To: pgsql-novice(at)postgresql(dot)org
Subject: IN vs =
Date: 2009-01-26 20:48:08
Message-ID: 45587.213.226.190.190.1233002888.squirrel@fmf.vgtu.lt
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello,

I would like to ask, what is the main difference between operators IN and
'='.
Then I use operator IN in JOIN it gives me much worse time (in my example
~3000ms) at the same time '=' gives 30ms!
But the most interesting think is that at the begging (when DB was
smaller) worked at the same speed as '=', why?

--
Lukas
UAB nSoft
http://www.nsoft.lt
Lukas at nsoft.lt
+370 655 10 655

Here is my SQL and Analyse results:

Query with '=':
SELECT
mok_id,
COALESCE(pard_preke, prek_pavadinimas) AS preke,
mok_suma_bazine/pard_kiekis,
mok_suma - COALESCE(sum(ms_suma), 0)*(case when (pard_tipas=1) then 1
when (pard_tipas=2) then -1 else 0 end) ,
pard_tipas, pard_spausdinta
FROM b_mokejimai
LEFT JOIN b_pardavimai ON (pard_id=mok_pardavimas)
LEFT JOIN b_preke ON (pard_prekes_id=prek_id)
LEFT JOIN b_pvm ON (pard_pvm=pvm_id)
LEFT JOIN b_mokejimo_budas ON (mok_budas=mb_id)
LEFT JOIN b_mokejimu_sudengimai ON (mok_id = ms_mokejimas OR mok_id =
ms_padengimas)
WHERE mok_cekis=122970 and pard_tipas IN (1,2)
GROUP BY mok_id, pard_preke, prek_pavadinimas, pard_kiekis, mok_suma,
mok_suma_bazine, pvm_kodas, prek_id, pard_id, mb_kodas, pard_tipas,
pard_spausdinta
ORDER BY pard_tipas, preke;

Query with 'IN':
SELECT
mok_id,
COALESCE(pard_preke, prek_pavadinimas) AS preke,
mok_suma_bazine/pard_kiekis,
mok_suma - COALESCE(sum(ms_suma), 0)*(case when (pard_tipas=1) then 1
when (pard_tipas=2) then -1 else 0 end) ,
pard_tipas,
pard_spausdinta
FROM b_mokejimai
LEFT JOIN b_pardavimai ON (pard_id=mok_pardavimas)
LEFT JOIN b_preke ON (pard_prekes_id=prek_id)
LEFT JOIN b_pvm ON (pard_pvm=pvm_id)
LEFT JOIN b_mokejimo_budas ON (mok_budas=mb_id)
LEFT JOIN b_mokejimu_sudengimai ON (mok_id IN (ms_mokejimas, ms_padengimas))
WHERE mok_cekis=122970 and pard_tipas IN (1,2)
GROUP BY mok_id, pard_preke, prek_pavadinimas, pard_kiekis, mok_suma,
mok_suma_bazine, pvm_kodas, prek_id, pard_id, mb_kodas, pard_tipas,
pard_spausdinta
ORDER BY pard_tipas, preke;

As you can undestand problem is with:
LEFT JOIN b_mokejimu_sudengimai ON (mok_id IN (ms_mokejimas, ms_padengimas))

Here is explain analyse plan:
QUERY PLAN
Sort (cost=139348.10..139348.19 rows=34 width=99) (actual
time=3708.084..3708.092 rows=17 loops=1)
Sort Key: b_pardavimai.pard_tipas, (COALESCE(b_pardavimai.pard_preke,
b_preke.prek_pavadinimas))
Sort Method: quicksort Memory: 20kB
-> HashAggregate (cost=139346.22..139347.24 rows=34 width=99) (actual
time=3707.715..3707.863 rows=17 loops=1)
-> Nested Loop Left Join (cost=3246.97..139345.11 rows=34
width=99) (actual time=442.818..3707.282 rows=32 loops=1)
-> Hash Left Join (cost=3246.97..139335.55 rows=34
width=68) (actual time=442.807..3706.618 rows=32 loops=1)
Hash Cond: (b_pardavimai.pard_pvm = b_pvm.pvm_id)
-> Hash Left Join (cost=3245.90..139334.07 rows=34
width=70) (actual time=442.782..3706.509 rows=32
loops=1)
Hash Cond: (b_mokejimai.mok_budas =
b_mokejimo_budas.mb_id)
-> Nested Loop Left Join
(cost=3244.66..139332.36 rows=34 width=72)
(actual time=442.723..3706.319 rows=32 loops=1)
Join Filter: (b_mokejimai.mok_id = ANY
(ARRAY[b_mokejimu_sudengimai.ms_mokejimas,
b_mokejimu_sudengimai.ms_padengimas]))
-> Nested Loop (cost=0.00..352.65
rows=34 width=65) (actual
time=0.053..0.570 rows=17 loops=1)
-> Index Scan using
fki_mokejimo_cekis on b_mokejimai
(cost=0.00..18.49 rows=40 width=26)
(actual time=0.026..0.082 rows=17
loops=1)
Index Cond: (mok_cekis = 122970)
-> Index Scan using
pk_b_pardavimai_id on b_pardavimai
(cost=0.00..8.34 rows=1 width=43)
(actual time=0.018..0.022 rows=1
loops=17)
Index Cond:
(b_pardavimai.pard_id =
b_mokejimai.mok_pardavimas)
Filter:
(b_pardavimai.pard_tipas = ANY
('{1,2}'::integer[]))
-> Materialize (cost=3244.66..5414.53
rows=145787 width=15) (actual
time=0.021..92.274 rows=141135 loops=17)
-> Seq Scan on
b_mokejimu_sudengimai
(cost=0.00..2386.87 rows=145787
width=15) (actual time=0.009..96.607
rows=141135 loops=1)
-> Hash (cost=1.11..1.11 rows=11 width=6)
(actual time=0.023..0.023 rows=11 loops=1)
-> Seq Scan on b_mokejimo_budas
(cost=0.00..1.11 rows=11 width=6) (actual
time=0.005..0.012 rows=11 loops=1)
-> Hash (cost=1.03..1.03 rows=3 width=6) (actual
time=0.011..0.011 rows=3 loops=1)
-> Seq Scan on b_pvm (cost=0.00..1.03 rows=3
width=6) (actual time=0.004..0.006 rows=3
loops=1)
-> Index Scan using b_preke_pkey on b_preke
(cost=0.00..0.27 rows=1 width=35) (actual time=0.014..0.015
rows=1 loops=32)
Index Cond: (b_pardavimai.pard_prekes_id =
b_preke.prek_id)
Total runtime: 3710.591 ms

--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2009-01-26 21:33:25 Re: IN vs =
Previous Message Daniel Staal 2009-01-26 19:18:20 Re: SQL Question: Averages of intervals.