Re: Query problem - Mailing list pgsql-performance

Try re-arranging your join structure:

        , A_CARS O
     JOIN A_CH_CAR CHT ON (CHT.IDS=O.IDS)
     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)
    WHERE O.IDS_LOCATION=TT.IDS
      AND O.IDS_MDL_MDF_VOL=M.IDS
      AND CHT.INSTIME=1059300812726
      AND CHT.SES=1059300377005
      and O.DOG_OR_FREE IN(0,2,3);

I believe this will cause fewer rows to be used when hashing for the
left outer joins.

On Sun, 2003-07-27 at 07:49, pginfo wrote:
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>