Query problem - Mailing list pgsql-performance

Hi ,

I am working to migrate a oracle application to pg.
I am using pg 7.3.3 on Dual PIII, 2 GB RAM,linux RedHat 7.3.

One of my selects is working much slower as in oracle.
In this example I am using not many rows in tables.
For all the joins I have indexes.
All IDS or IDS_xxx are name.

Pls if it is possible poit me how to fix this problem.

I send the query and the explai analyze.
I have ran vacuum analyze full on db.

Many thanks,
ivan.

 explain analyze select O.IDS as oids,O.IDS_MDL_MDF_VOL as
ids_mmv,M.MNAME AS MODDELNAME,M.KOD  AS MODELKOD,O.IDS_COLOR,COL.MNAME
AS COLORNAME,COL.KOD AS COLORKOD, TT.IDS AS LOCIDS,TT.MNAME AS LOCNAME,
TT.KOD AS LOC_KOD ,O.IDS_DOSTAV,DST.MNAME AS DOSTAVNAME,
O.IDS_PROIZV,PRZ.MNAME as
PROIZVNAME,O.CHASSI,O.CHASSI_ACC,O.DVIGATEL,O.ORDER_NUM,O.ORDER_DATE,O.DOG_OR_FREE,
O.NALICHEN,O.DATE_PROIZV, O.DATE_IN,O.ALI,O.DATE_ALI,
O.PRICE_PAY,O.PRICE_PAY_VAL,
O.START_DATE,O.DAYS,O.END_DATE,O.COMENTAR,O.IDS_AUTOVOZ,AWT.MNAME AS
AUTOVNAME,
O.SVERKA,O.NEW_OLD,O.KM,O.START_DATE_REZ,O.END_DATE_REZ,O.IDS_SLUJITEL,SLU.KOD,NULL
AS CT_IDS, NULL AS C_NUM, O.DATE_ALI2, NULL AS C_STATE,  0 AS DAMAGE,
O.REG_NUMBER AS CARREGNUMBER,O.DATE_REG AS CARREGDATE,O.GARTYPE,2002 AS
GODINA,O.COMENTAR1,   O.IDS_COMBOPT,CB.KOD AS
IDS_COMBOPT_KOD,O.REF_BG,O.DAM,O.OBEM, O.IDS_TAPICERII,TAP.KOD AS
IDS_TAPICERII_KOD,TAP.MNAME AS
IDS_TAPICERII_NAME,O.PAPKA_N,O.CEDMICAPR,  O.RADIO_KOD AS
RADIO_KOD,O.KEY_KOD AS KEY_KOD,O.ALARM_KOD AS ALARM_KOD,O.BOLT_KOD AS
BOLT_KOD,M.MOST_PS,  NULL AS IDS_KLIENT , NULL AS KlientName ,O.TALON_N
AS talonN,O.STATEMODIFY AS STATEMOD,O.MESTA AS MESTA,O.CENA_COLOR AS
CENA_COL,O.CENA_TAP AS CENA_TAP,M.CENA_PROD AS
MCENA_PROD,M.CENA_PROD_VAL AS

MCENA_PROD_VAL,O.CENA_MDL,O.MESTA_MDL,O.CENA_COLOR_VAL,O.CENA_TAP_VAL,O.CENA_MDL_VAL,O.VIRTUALEN,M.IDS_GRUPA,COL.MNAME_1
AS COLMNAME1,O.DATE_PLAN_P,O.KM_PLAN_P  from A_COLORS COL, A_MDL_MDF_VOL
M ,A_LOCATIONS TT, A_CARS O  left outer join A_SLUJITELI SLU
ON(O.IDS_SLUJITEL=SLU.IDS)   left outer join A_AUTOVOZ AWT
ON(O.IDS_AUTOVOZ=AWT.IDS)  left outer join A_COMBOPT CB
ON(O.IDS_COMBOPT=CB.IDS)   left outer join A_TAPICERII TAP
ON(O.IDS_TAPICERII=TAP.IDS)   left outer join A_KLIENTI DST ON(
O.IDS_DOSTAV=DST.IDS) left outer join A_KLIENTI PRZ ON( O.IDS_PROIZV =
PRZ.IDS) ,A_CH_CAR CHT  WHERE O.IDS_LOCATION=TT.IDS AND
O.IDS_MDL_MDF_VOL=M.IDS  AND O.IDS_COLOR=COL.IDS  AND CHT.IDS=O.IDS AND
CHT.INSTIME=1059300812726 AND CHT.SES=1059300377005  and O.DOG_OR_FREE
IN(0,2,3)  ;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Hash Join  (cost=138.54..142.57 rows=2 width=2051) (actual
time=286.17..286.29 rows=2 loops=1)
   Hash Cond: ("outer".ids_location = "inner".ids)
   ->  Hash Join  (cost=137.42..141.40 rows=2 width=1971) (actual
time=285.95..286.02 rows=2 loops=1)
         Hash Cond: ("outer".ids = "inner".ids_color)
         ->  Seq Scan on a_colors col  (cost=0.00..3.12 rows=112
width=101) (actual time=0.01..0.30 rows=112 loops=1)
         ->  Hash  (cost=137.41..137.41 rows=2 width=1870) (actual
time=285.43..285.43 rows=0 loops=1)
               ->  Hash Join  (cost=134.88..137.41 rows=2 width=1870)
(actual time=285.12..285.42 rows=2 loops=1)
                     Hash Cond: ("outer".ids = "inner".ids_mdl_mdf_vol)
                     ->  Seq Scan on a_mdl_mdf_vol m  (cost=0.00..2.34
rows=34 width=189) (actual time=0.03..0.21 rows=34 loops=1)
                     ->  Hash  (cost=134.88..134.88 rows=2 width=1681)
(actual time=284.98..284.98 rows=0 loops=1)
                           ->  Hash Join  (cost=10.76..134.88 rows=2
width=1681) (actual time=189.62..284.97 rows=2 loops=1)
                                 Hash Cond: ("outer".ids = "inner".ids)
                                 ->  Hash Join  (cost=9.73..128.72
rows=1019 width=1617) (actual time=1.58..283.39 rows=1023 loops=1)
                                       Hash Cond: ("outer".ids_proizv =
"inner".ids)
                                       ->  Hash Join  (cost=7.50..108.66
rows=1019 width=1545) (actual time=1.34..234.05 rows=1023 loops=1)
                                             Hash Cond:
("outer".ids_dostav = "inner".ids)
                                             ->  Hash Join
(cost=5.28..88.60 rows=1019 width=1473) (actual time=1.12..188.41
rows=1023 loops=1)
                                                   Hash Cond:
("outer".ids_tapicerii = "inner".ids)
                                                   ->  Hash Join
(cost=2.40..67.89 rows=1019 width=1372) (actual time=0.68..145.58
rows=1023 loops=1)
                                                         Hash Cond:
("outer".ids_combopt = "inner".ids)
                                                         ->  Hash Join
(cost=1.09..46.19 rows=1019 width=1301) (actual time=0.45..106.88
rows=1023 loops=1)
                                                               Hash
Cond: ("outer".ids_autovoz = "inner".ids)
                                                               ->  Hash
Join  (cost=1.09..41.03 rows=1019 width=1189) (actual time=0.31..72.28
rows=1023 loops=1)

Hash Cond: ("outer".ids_slujitel = "inner".ids)
                                                                     ->
Index Scan using i_cars_dog_or_free on a_cars o  (cost=0.00..22.11
rows=1019 width=1119) (actual time=0.12..37.41 rows=1023 loops=1)

Filter: ((dog_or_free = 0) OR (dog_or_free = 2) OR (dog_or_free = 3))
                                                                     ->
Hash  (cost=1.07..1.07 rows=7 width=70) (actual time=0.04..0.04 rows=0
loops=1)

->  Seq Scan on a_slujiteli slu  (cost=0.00..1.07 rows=7 width=70)
(actual time=0.01..0.03 rows=7 loops=1)
                                                               ->  Hash
(cost=0.00..0.00 rows=1 width=112) (actual time=0.00..0.00 rows=0
loops=1)
                                                                     ->
Seq Scan on a_autovoz awt  (cost=0.00..0.00 rows=1 width=112) (actual
time=0.00..0.00 rows=0 loops=1)
                                                         ->  Hash
(cost=1.25..1.25 rows=25 width=71) (actual time=0.09..0.09 rows=0
loops=1)
                                                               ->  Seq
Scan on a_combopt cb  (cost=0.00..1.25 rows=25 width=71) (actual
time=0.01..0.06 rows=25 loops=1)
                                                   ->  Hash
(cost=2.70..2.70 rows=70 width=101) (actual time=0.29..0.29 rows=0
loops=1)
                                                         ->  Seq Scan on
a_tapicerii tap  (cost=0.00..2.70 rows=70 width=101) (actual
time=0.01..0.17 rows=70 loops=1)
                                             ->  Hash  (cost=2.18..2.18
rows=18 width=72) (actual time=0.06..0.06 rows=0 loops=1)
                                                   ->  Seq Scan on
a_klienti dst  (cost=0.00..2.18 rows=18 width=72) (actual
time=0.01..0.03 rows=18 loops=1)
                                       ->  Hash  (cost=2.18..2.18
rows=18 width=72) (actual time=0.07..0.07 rows=0 loops=1)
                                             ->  Seq Scan on a_klienti
prz  (cost=0.00..2.18 rows=18 width=72) (actual time=0.01..0.05 rows=18
loops=1)
                                 ->  Hash  (cost=1.03..1.03 rows=2
width=64) (actual time=0.03..0.03 rows=0 loops=1)
                                       ->  Seq Scan on a_ch_car cht
(cost=0.00..1.03 rows=2 width=64) (actual time=0.02..0.03 rows=2
loops=1)
                                             Filter: ((instime =
1059300812726::bigint) AND (ses = 1059300377005::bigint))
   ->  Hash  (cost=1.10..1.10 rows=10 width=80) (actual time=0.07..0.07
rows=0 loops=1)
         ->  Seq Scan on a_locations tt  (cost=0.00..1.10 rows=10
width=80) (actual time=0.03..0.05 rows=10 loops=1)
 Total runtime: 287.61 msec
(44 rows)

Time: 301.36 ms