Обсуждение: Query problem
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
Hello, > 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. Have you tuned your postgresql.conf settings ? The PostgreSQL default settings are very low in order to allow PostgreSQL to RUN on old machines and new machines. If you need PERFORMANCE (which is quite logic), you must setup the postgresql.conf file. Here's a nice article about the postgresql.conf file tuning : http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html Hope this help ! :-) Cheers, --------------------------------------- Bruno BAGUETTE - pgsql-ml@baguette.net
Hi Bruno, I think I have tunet it. Pg is working for most of my selects, but I have problem with this one. regards, ivan Bruno BAGUETTE wrote: > Hello, > > > 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. > > Have you tuned your postgresql.conf settings ? > > The PostgreSQL default settings are very low in order to allow > PostgreSQL to RUN on old machines and new machines. If you need > PERFORMANCE (which is quite logic), you must setup the postgresql.conf > file. > > Here's a nice article about the postgresql.conf file tuning : > http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html > > Hope this help ! :-) > > Cheers, > > --------------------------------------- > Bruno BAGUETTE - pgsql-ml@baguette.net
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
>