Re: Query problem

From: Rod Taylor <rbt(at)rbt(dot)ca>
To: pginfo <pginfo(at)t1(dot)unisoftbg(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Query problem
Date: 2003-07-27 14:25:40
Message-ID: 1059315939.32716.17.camel@jester
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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
>

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2003-07-27 16:18:52 Re: Mapping a database completly into Memory
Previous Message Bruno BAGUETTE 2003-07-27 13:26:03 RE : Query problem