Performance problem...

Hello...

Our company is going to change SQL engine from MySQL to PSQL. Of course some
performance problems occured. Our server is Dual Xeon 3.0GHz + 8GB RAM +
RAID1(software - two 146GB SCSI 15k) for sql data + RAID1(software - two
146GB SCSI 15k) for pg_xlog. Postgres.conf parameters are as follows:

max_connections = 150
shared_buffers = 50000 # min 16, at least max_connections*2, 8KB each
work_mem = 2048 # min 64, size in KB
maintenance_work_mem = 524288 # min 1024, size in KB
checkpoint_segments = 32 # in logfile segments, min 1, 16MB each
archive_command = 'cp "%p" /mnt/logs/"%f"' # command to use to
archive a logfile segment

effective_cache_size = 655360 # typically 8KB each
random_page_cost = 1.2 # units are one sequential page fetch cost
stats_start_collector = true
stats_row_level = true

Of course our system is Debian Sarge with Shared memory size = 1GB

Here is an example:

.... I know you don't have our schemas/tables etc. but I also attached QUERY
PLAN for such query. Maybe there is something wrong with this query maybe it
should be changed? or so?

SELECT DISTINCT t1.Id, t1.IdTypNazwa, t1.IdFirma, t1.Nazwa, t1.NazwaAscii,
t1.MskNazwa, t3.Id, t3.numer, t3.MskNumer, t4.Id, t4.numer, t4.MskNumer,
t5.Id, t5.numer, t5.MskNumer, t6.Id, t6.numer, t6.MskNumer, t7.Id, t7.numer,
t7.MskNumer, t8.Id, t8.numer, t8.MskNumer, t9.Id, t9.numer, t9.MskNumer,
t10.Id, t10.IdTypFormaPrawna, t10.MskFormaPrawna, t11.slowo FROM firmy.adres
AS n INNER JOIN firmy.nazwa AS t1 ON t1.IdFirma = n.IdPodmiot AND
t1.IdTypNazwa = 153 AND t1.Historia = 0 LEFT JOIN firmy.numer AS t3 ON
t3.IdPodmiot = t1.IdFirma AND t3.IdTypNumer = 156 AND t3.Historia = 0 LEFT
JOIN firmy.numer AS t4 ON t4.IdPodmiot = t1.IdFirma AND t4.IdTypNumer = 160
AND t4.Historia = 0 LEFT JOIN firmy.numer AS t5 ON t5.IdPodmiot = t1.IdFirma
AND t5.IdTypNumer = 155 AND t5.Historia = 0 LEFT JOIN firmy.numer AS t6 ON
t6.IdPodmiot = t1.IdFirma AND t6.IdTypNumer = 627 AND t6.Historia = 0 LEFT
JOIN firmy.numer AS t7 ON t7.IdPodmiot = t1.IdFirma AND t7.IdTypNumer = 848
AND t7.Historia = 0 LEFT JOIN firmy.numer AS t8 ON t8.IdPodmiot = t1.IdFirma
AND t8.IdTypNumer = 763 AND t8.Historia = 0 LEFT JOIN firmy.numer AS t9 ON
t9.IdPodmiot = t1.IdFirma AND t9.IdTypNumer = 762 AND t9.Historia = 0 LEFT
JOIN firmy.formaprawna AS t10 ON t10.IdFirma = t1.IdFirma LEFT JOIN
slowniki.tslownik AS t11 ON t11.idslownik = t10.IdTypFormaPrawna AND
t11.idjezyktyp = 2 WHERE n.Ulica ILIKE 'pu%' AND n.IdKraj = 190 LIMIT 25

pl=# explain analyze SELECT DISTINCT t1.Id, t1.IdTypNazwa, t1.IdFirma,
t1.Nazwa, t1.NazwaAscii,
pl-# t1.MskNazwa, t3.Id, t3.numer, t3.MskNumer, t4.Id, t4.numer, t4.MskNumer,
pl-# t5.Id, t5.numer, t5.MskNumer, t6.Id, t6.numer, t6.MskNumer, t7.Id,
t7.numer,
pl-# t7.MskNumer, t8.Id, t8.numer, t8.MskNumer, t9.Id, t9.numer, t9.MskNumer,
pl-# t10.Id, t10.IdTypFormaPrawna, t10.MskFormaPrawna, t11.slowo FROM
firmy.adres
pl-# AS n INNER JOIN firmy.nazwa AS t1 ON t1.IdFirma = n.IdPodmiot AND
pl-# t1.IdTypNazwa = 153 AND t1.Historia = 0 LEFT JOIN firmy.numer AS t3 ON
pl-# t3.IdPodmiot = t1.IdFirma AND t3.IdTypNumer = 156 AND t3.Historia = 0
LEFT
pl-# JOIN firmy.numer AS t4 ON t4.IdPodmiot = t1.IdFirma AND t4.IdTypNumer =
160
pl-# AND t4.Historia = 0 LEFT JOIN firmy.numer AS t5 ON t5.IdPodmiot =
t1.IdFirma
pl-# AND t5.IdTypNumer = 155 AND t5.Historia = 0 LEFT JOIN firmy.numer AS t6
ON
pl-# t6.IdPodmiot = t1.IdFirma AND t6.IdTypNumer = 627 AND t6.Historia = 0
LEFT
pl-# JOIN firmy.numer AS t7 ON t7.IdPodmiot = t1.IdFirma AND t7.IdTypNumer =
848
pl-# AND t7.Historia = 0 LEFT JOIN firmy.numer AS t8 ON t8.IdPodmiot =
t1.IdFirma
pl-# AND t8.IdTypNumer = 763 AND t8.Historia = 0 LEFT JOIN firmy.numer AS t9
ON
pl-# t9.IdPodmiot = t1.IdFirma AND t9.IdTypNumer = 762 AND t9.Historia = 0
LEFT
pl-# JOIN firmy.formaprawna AS t10 ON t10.IdFirma = t1.IdFirma LEFT JOIN
pl-# slowniki.tslownik AS t11 ON t11.idslownik = t10.IdTypFormaPrawna AND
pl-# t11.idjezyktyp = 2 WHERE n.Ulica ILIKE 'pu%' AND n.IdKraj = 190 LIMIT 25;

QUERY PLAN

Limit (cost=84757.05..84759.05 rows=25 width=264) (actual
time=3153.752..3154.418 rows=25 loops=1)
-> Unique (cost=84757.05..88861.61 rows=51307 width=264) (actual
time=3153.748..3154.391 rows=25 loops=1)
-> Sort (cost=84757.05..84885.32 rows=51307 width=264) (actual
time=3153.745..3153.768 rows=44 loops=1)
Sort Key: t1.id, t1.idtypnazwa, t1.idfirma, t1.nazwa,
t1.nazwaascii, t1.msknazwa, t3.id, t3.numer, t3.msknumer, t4.id, t4.numer,
t4.msknumer,
t5.id, t5.numer, t5.msknumer, t6.id, t6.numer, t6.msknumer, t7.id, t7.numer,
t7.msknumer, t8.id, t8.numer, t8.msknumer, t9.id, t9.numer, t9.msknumer,
t10.i
d, t10.idtypformaprawna, t10.mskformaprawna, t11.slowo
-> Hash Left Join (cost=18104.92..77085.08 rows=51307
width=264) (actual time=643.240..3131.874 rows=1128 loops=1)
Hash Cond: ("outer".idtypformaprawna = "inner".idslownik)
-> Merge Left Join (cost=17680.10..73498.20 rows=50457
width=227) (actual time=626.711..3100.239 rows=1128 loops=1)
Merge Cond: ("outer".idfirma = "inner".idfirma)
-> Merge Left Join (cost=17680.10..71408.80
rows=50457 width=215) (actual time=626.209..2930.366 rows=1128 loops=1)
Merge Cond: ("outer".idfirma =
"inner".idpodmiot)
-> Merge Left Join (cost=17101.31..70698.83
rows=50457 width=195) (actual time=623.431..2915.149 rows=1128 loops=1)
Merge Cond: ("outer".idfirma =
"inner".idpodmiot)
-> Merge Left Join
(cost=15446.22..68901.47 rows=50457 width=175) (actual time=614.432..2892.178
rows=1128 loops=1)
Merge Cond: ("outer".idfirma =
"inner".idpodmiot)
-> Merge Left Join
(cost=7301.66..60235.12 rows=50457 width=155) (actual time=260.861..2454.992
rows=1128 loo
ps=1)
Merge Cond:
("outer".idfirma = "inner".idpodmiot)
-> Merge Left Join
(cost=7301.66..49786.89 rows=50457 width=135) (actual time=258.841..2054.790
rows=11
28 loops=1)
Merge Cond:
("outer".idfirma = "inner".idpodmiot)
-> Merge Left Join
(cost=0.00..42050.02 rows=50457 width=115) (actual time=5.759..1735.173
rows=1
128 loops=1)
Merge Cond:
("outer".idfirma = "inner".idpodmiot)
-> Merge Left
Join (cost=0.00..31611.72 rows=50457 width=95) (actual time=4.530..1337.763
r
ows=1128 loops=1)
Merge
Cond: ("outer".idfirma = "inner".idpodmiot)
-> Merge
Join (cost=0.00..21021.26 rows=50457 width=75) (actual time=2.709..813.394 r
ows=1128 loops=1)
Merge
Cond: ("outer".idpodmiot = "inner".idfirma)
->
Index Scan using firmy_adres_idpodmiot on adres n (cost=0.00..12596.46 rows=
42837 width=4) (actual time=1.261..337.163 rows=1128 loops=1)
Filter:
(((ulica)::text ~~* 'pu%'::text) AND (idkraj = 190))
->
Index Scan using firmy_nazwa_idfirma on nazwa t1 (cost=0.00..7539.00 rows=11
0134 width=75) (actual time=0.023..392.591 rows=109085 loops=1)
Filter:
((idtypnazwa = 153) AND (historia = 0))
-> Index
Scan using firmy_numer_idpodmiot on numer t3 (cost=0.00..9869.42 rows=75337
width=24) (actual time=0.018..463.952 rows=77155 loops=1)
Filter:
((idtypnumer = 156) AND (historia = 0))
-> Index Scan
using firmy_numer_idpodmiot on numer t4 (cost=0.00..9869.42 rows=56067
width=
24) (actual time=0.012..335.900 rows=57050 loops=1)
Filter:
((idtypnumer = 160) AND (historia = 0))
-> Sort
(cost=7301.66..7375.98 rows=29728 width=24) (actual time=252.882..288.512
rows=28192 loop
s=1)
Sort Key:
t5.idpodmiot
-> Index Scan
using firmy_numer_idtypnumer on numer t5 (cost=0.00..5092.94 rows=29728
width
=24) (actual time=0.029..66.937 rows=27904 loops=1)
Index
Cond: ((idtypnumer = 155) AND (historia = 0))
-> Index Scan using
firmy_numer_idpodmiot on numer t6 (cost=0.00..9869.42 rows=57326 width=24)
(actual
time=0.049..340.456 rows=59336 loops=1)
Filter: ((idtypnumer
= 627) AND (historia = 0))
-> Sort (cost=8144.56..8239.70
rows=38056 width=24) (actual time=353.474..395.087 rows=37693 loops=1)
Sort Key: t7.idpodmiot
-> Index Scan using
firmy_numer_idtypnumer on numer t7 (cost=0.00..5249.29 rows=38056 width=24)
(actual
time=0.032..90.333 rows=37549 loops=1)
Index Cond:
((idtypnumer = 848) AND (historia = 0))
-> Sort (cost=1655.10..1658.97
rows=1550 width=24) (actual time=8.884..9.971 rows=1699 loops=1)
Sort Key: t8.idpodmiot
-> Index Scan using
firmy_numer_idtypnumer on numer t8 (cost=0.00..1572.96 rows=1550 width=24)
(actual time=0
.053..5.287 rows=1690 loops=1)
Index Cond: ((idtypnumer =
763) AND (historia = 0))
-> Sort (cost=578.79..580.00 rows=485
width=24) (actual time=2.698..3.077 rows=509 loops=1)
Sort Key: t9.idpodmiot
-> Index Scan using
firmy_numer_idtypnumer on numer t9 (cost=0.00..557.15 rows=485 width=24)
(actual time=0.032..1.
757 rows=508 loops=1)
Index Cond: ((idtypnumer = 762)
AND (historia = 0))
-> Index Scan using formaprawna_idfirma_key on
formaprawna t10 (cost=0.00..1500.26 rows=58650 width=16) (actual
time=0.013..103
.667 rows=59116 loops=1)
-> Hash (cost=415.86..415.86 rows=3583 width=41)
(actual time=16.463..16.463 rows=0 loops=1)
-> Seq Scan on tslownik t11 (cost=0.00..415.86
rows=3583 width=41) (actual time=0.020..12.802 rows=3595 loops=1)
Filter: (idjezyktyp = 2)
Total runtime: 3159.199 ms
(55 rows)

pl=#

I now .... query plan is not very readable but .... as you can see only one
seq scan occured for field "ulica" in table "adres" .... on the rest Index
scan were used. I'm rather new and really don't know how to interpret and
what are they mean: Merge Left Join? How to read actual time in each row ow
query plan.... and why this query takes about 3 second to receive 25 records
when on MySQL it takes only 0.14sec.? All indexes are made - the same indexes
on mysql and postgresql. What is the cause of such big difference?

Many thanks for response....

Marcin Giedz