X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id CDB5FD1C4CD for ; Sun, 27 Jul 2003 14:25:11 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 15855-02 for ; Sun, 27 Jul 2003 11:24:23 -0300 (ADT) Received: from mailav.tor1.inquent.com (mail.inquent.com [216.208.117.106]) by svr1.postgresql.org (Postfix) with ESMTP id 759B6D1C4BB for ; Sun, 27 Jul 2003 11:25:10 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by mailav.tor1.inquent.com (Postfix) with ESMTP id 78CC61024D; Sun, 27 Jul 2003 10:17:49 -0400 (EDT) Received: from [192.168.1.200] (dyn-133-81.tor.dsl.tht.net [134.22.133.81]) by mailav.tor1.inquent.com (Postfix) with ESMTP id 8343310248; Sun, 27 Jul 2003 10:17:46 -0400 (EDT) Subject: Re: Query problem From: Rod Taylor To: pginfo Cc: "pgsql-performance@postgresql.org" In-Reply-To: <3F23BC59.B96BBBFE@t1.unisoftbg.com> References: <3F23BC59.B96BBBFE@t1.unisoftbg.com> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-R5uW4lhIhASnKOzxHWZy" Message-Id: <1059315939.32716.17.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.3 Date: 27 Jul 2003 10:25:40 -0400 X-Virus-Scanned: by AMaViS perl-11 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/434 X-Sequence-Number: 2727 --=-R5uW4lhIhASnKOzxHWZy Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Try re-arranging your join structure: , A_CARS O JOIN A_CH_CAR CHT ON (CHT.IDS=3DO.IDS) left outer join A_SLUJITELI SLU ON(O.IDS_SLUJITEL=3DSLU.IDS) left outer join A_AUTOVOZ AWT ON(O.IDS_AUTOVOZ=3DAWT.IDS) left outer join A_COMBOPT CB ON(O.IDS_COMBOPT=3DCB.IDS) left outer join A_TAPICERII TAP ON(O.IDS_TAPICERII=3DTAP.IDS) left outer join A_KLIENTI DST ON(O.IDS_DOSTAV=3DDST.IDS) left outer join A_KLIENTI PRZ ON(O.IDS_PROIZV =3D PRZ.IDS) WHERE O.IDS_LOCATION=3DTT.IDS AND O.IDS_MDL_MDF_VOL=3DM.IDS AND CHT.INSTIME=3D1059300812726 AND CHT.SES=3D1059300377005 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 , >=20 > 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. >=20 > 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. >=20 > Pls if it is possible poit me how to fix this problem. >=20 > I send the query and the explai analyze. > I have ran vacuum analyze full on db. >=20 > Many thanks, > ivan. >=20 > 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.DO= G_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,SL= U.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.C= ENA_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=3DSLU.IDS) left outer join A_AUTOVOZ AWT > ON(O.IDS_AUTOVOZ=3DAWT.IDS) left outer join A_COMBOPT CB > ON(O.IDS_COMBOPT=3DCB.IDS) left outer join A_TAPICERII TAP > ON(O.IDS_TAPICERII=3DTAP.IDS) left outer join A_KLIENTI DST ON( > O.IDS_DOSTAV=3DDST.IDS) left outer join A_KLIENTI PRZ ON( O.IDS_PROIZV = =3D > PRZ.IDS) ,A_CH_CAR CHT WHERE O.IDS_LOCATION=3DTT.IDS AND > O.IDS_MDL_MDF_VOL=3DM.IDS AND O.IDS_COLOR=3DCOL.IDS AND CHT.IDS=3DO.IDS= AND > CHT.INSTIME=3D1059300812726 AND CHT.SES=3D1059300377005 and O.DOG_OR_FREE > IN(0,2,3) ; >=20 > QUERY PLAN > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------- >=20 > Hash Join (cost=3D138.54..142.57 rows=3D2 width=3D2051) (actual > time=3D286.17..286.29 rows=3D2 loops=3D1) > Hash Cond: ("outer".ids_location =3D "inner".ids) > -> Hash Join (cost=3D137.42..141.40 rows=3D2 width=3D1971) (actual > time=3D285.95..286.02 rows=3D2 loops=3D1) > Hash Cond: ("outer".ids =3D "inner".ids_color) > -> Seq Scan on a_colors col (cost=3D0.00..3.12 rows=3D112 > width=3D101) (actual time=3D0.01..0.30 rows=3D112 loops=3D1) > -> Hash (cost=3D137.41..137.41 rows=3D2 width=3D1870) (actual > time=3D285.43..285.43 rows=3D0 loops=3D1) > -> Hash Join (cost=3D134.88..137.41 rows=3D2 width=3D187= 0) > (actual time=3D285.12..285.42 rows=3D2 loops=3D1) > Hash Cond: ("outer".ids =3D "inner".ids_mdl_mdf_vol) > -> Seq Scan on a_mdl_mdf_vol m (cost=3D0.00..2.34 > rows=3D34 width=3D189) (actual time=3D0.03..0.21 rows=3D34 loops=3D1) > -> Hash (cost=3D134.88..134.88 rows=3D2 width=3D16= 81) > (actual time=3D284.98..284.98 rows=3D0 loops=3D1) > -> Hash Join (cost=3D10.76..134.88 rows=3D2 > width=3D1681) (actual time=3D189.62..284.97 rows=3D2 loops=3D1) > Hash Cond: ("outer".ids =3D "inner".ids) > -> Hash Join (cost=3D9.73..128.72 > rows=3D1019 width=3D1617) (actual time=3D1.58..283.39 rows=3D1023 loops= =3D1) > Hash Cond: ("outer".ids_proizv =3D > "inner".ids) > -> Hash Join (cost=3D7.50..108.66 > rows=3D1019 width=3D1545) (actual time=3D1.34..234.05 rows=3D1023 loops= =3D1) > Hash Cond: > ("outer".ids_dostav =3D "inner".ids) > -> Hash Join > (cost=3D5.28..88.60 rows=3D1019 width=3D1473) (actual time=3D1.12..188.41 > rows=3D1023 loops=3D1) > Hash Cond: > ("outer".ids_tapicerii =3D "inner".ids) > -> Hash Join > (cost=3D2.40..67.89 rows=3D1019 width=3D1372) (actual time=3D0.68..145.58 > rows=3D1023 loops=3D1) > Hash Cond: > ("outer".ids_combopt =3D "inner".ids) > -> Hash Join > (cost=3D1.09..46.19 rows=3D1019 width=3D1301) (actual time=3D0.45..106.88 > rows=3D1023 loops=3D1) > Hash > Cond: ("outer".ids_autovoz =3D "inner".ids) > -> Hash > Join (cost=3D1.09..41.03 rows=3D1019 width=3D1189) (actual time=3D0.31..= 72.28 > rows=3D1023 loops=3D1) >=20 > Hash Cond: ("outer".ids_slujitel =3D "inner".ids) > -> > Index Scan using i_cars_dog_or_free on a_cars o (cost=3D0.00..22.11 > rows=3D1019 width=3D1119) (actual time=3D0.12..37.41 rows=3D1023 loops=3D= 1) >=20 > Filter: ((dog_or_free =3D 0) OR (dog_or_free =3D 2) OR (dog_or_free =3D 3= )) > -> > Hash (cost=3D1.07..1.07 rows=3D7 width=3D70) (actual time=3D0.04..0.04 r= ows=3D0 > loops=3D1) >=20 > -> Seq Scan on a_slujiteli slu (cost=3D0.00..1.07 rows=3D7 width=3D70) > (actual time=3D0.01..0.03 rows=3D7 loops=3D1) > -> Hash > (cost=3D0.00..0.00 rows=3D1 width=3D112) (actual time=3D0.00..0.00 rows= =3D0 > loops=3D1) > -> > Seq Scan on a_autovoz awt (cost=3D0.00..0.00 rows=3D1 width=3D112) (actu= al > time=3D0.00..0.00 rows=3D0 loops=3D1) > -> Hash > (cost=3D1.25..1.25 rows=3D25 width=3D71) (actual time=3D0.09..0.09 rows= =3D0 > loops=3D1) > -> Seq > Scan on a_combopt cb (cost=3D0.00..1.25 rows=3D25 width=3D71) (actual > time=3D0.01..0.06 rows=3D25 loops=3D1) > -> Hash > (cost=3D2.70..2.70 rows=3D70 width=3D101) (actual time=3D0.29..0.29 rows= =3D0 > loops=3D1) > -> Seq Scan on > a_tapicerii tap (cost=3D0.00..2.70 rows=3D70 width=3D101) (actual > time=3D0.01..0.17 rows=3D70 loops=3D1) > -> Hash (cost=3D2.18..2.18 > rows=3D18 width=3D72) (actual time=3D0.06..0.06 rows=3D0 loops=3D1) > -> Seq Scan on > a_klienti dst (cost=3D0.00..2.18 rows=3D18 width=3D72) (actual > time=3D0.01..0.03 rows=3D18 loops=3D1) > -> Hash (cost=3D2.18..2.18 > rows=3D18 width=3D72) (actual time=3D0.07..0.07 rows=3D0 loops=3D1) > -> Seq Scan on a_klienti > prz (cost=3D0.00..2.18 rows=3D18 width=3D72) (actual time=3D0.01..0.05 r= ows=3D18 > loops=3D1) > -> Hash (cost=3D1.03..1.03 rows=3D2 > width=3D64) (actual time=3D0.03..0.03 rows=3D0 loops=3D1) > -> Seq Scan on a_ch_car cht > (cost=3D0.00..1.03 rows=3D2 width=3D64) (actual time=3D0.02..0.03 rows=3D2 > loops=3D1) > Filter: ((instime =3D > 1059300812726::bigint) AND (ses =3D 1059300377005::bigint)) > -> Hash (cost=3D1.10..1.10 rows=3D10 width=3D80) (actual time=3D0.07= ..0.07 > rows=3D0 loops=3D1) > -> Seq Scan on a_locations tt (cost=3D0.00..1.10 rows=3D10 > width=3D80) (actual time=3D0.03..0.05 rows=3D10 loops=3D1) > Total runtime: 287.61 msec > (44 rows) >=20 > Time: 301.36 ms >=20 >=20 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >=20 --=-R5uW4lhIhASnKOzxHWZy Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (FreeBSD) iD8DBQA/I+Di6DETLow6vwwRAsorAJ40TFg+2zg+mc6NAbboKL7IF3ao1QCeIHLu tT4w11Y4Tj7j/M10DBDCc7U= =PTyj -----END PGP SIGNATURE----- --=-R5uW4lhIhASnKOzxHWZy--