IN vs = - Mailing list 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.