Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 0C708632B8D for ; Thu, 26 Aug 2010 21:19:48 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 86892-03 for ; Fri, 27 Aug 2010 00:19:38 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from web39705.mail.mud.yahoo.com (web39705.mail.mud.yahoo.com [209.191.106.51]) by mail.postgresql.org (Postfix) with SMTP id A67DA632AF3 for ; Thu, 26 Aug 2010 21:19:39 -0300 (ADT) Received: (qmail 8887 invoked by uid 60001); 27 Aug 2010 00:19:37 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1282868377; bh=0JmMTSKLRgUAtSbSX3FUFfX1mllO3qryQkmvh0J3jlM=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=XU/0s+SV5/5sniviBGllOPWFeGrja3kdoqNOhVRnN3BUOIv2aZhe5ZMiesJQ9Tmi8YZRL7hAUAeAm3heTX50iv/fjl8skOThpSnv7eykIHHlRGo0NN8CSOaLwUFOcpJCvSwQZvoIcFXlRUCEEllyRpO9ejcJ9UxjmxBTJAqWo1k= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=pYfMmnJEDOBGtBkjtKrkxfjBBwBjuL9HzoHjjv7rjCwiN+jJJ+eemw39joNTt6OsUPFXhu/8cOLgzap6as5JElOaqiv0A71+676f+XvY0GdGAOSJotHHdy11Hs9NQsxeA5yyFEM8jngCrJ6UlKmvkzdyZW7INDrRXJeUfZalSP8=; Message-ID: <599616.8843.qm@web39705.mail.mud.yahoo.com> X-YMail-OSG: Dovuo64VM1l48Ep3hn9trTrJpFeIHa3MMBbVI46nwFpsJXD PbrKXBTL0LTDxBHXJ_r8Ewad99uCMwhEJToOgMa8_QbIUuiPmei8KWZXLZh3 TWtVa5nwSkVf4r9v55dSoPhFAWA7j1isfXf7ACKhrBINgoXJZy.bDWdHRMDl U6W.k414y2_uU4FApbOp8mfAo9wFd6339LVvexVR656hZOjR2I4Bpm.4.mwP tQa3BfEx1D5uYCBkRnV95uiq.ImpEZNmy3o_HydweAE8I6Ml9RLOJJ8bYzIW N4.7slyHMaVgwnRys286Xrm4Y.MzV6_PzfMHpH8WYSBFtvXJtnWfGUgGiN5b lHsdDCnZ1cS6EdjHUSCSfLp9g.6gdv33n Received: from [69.76.130.83] by web39705.mail.mud.yahoo.com via HTTP; Thu, 26 Aug 2010 17:19:37 PDT X-Mailer: YahooMailClassic/11.3.2 YahooMailWebService/0.8.105.279950 Date: Thu, 26 Aug 2010 17:19:37 -0700 (PDT) From: Bob Lunney Subject: Re: Slow Query To: pgsql-performance@postgresql.org, PamOzer In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1592438393-1282868377=:8843" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201008/289 X-Sequence-Number: 40132 --0-1592438393-1282868377=:8843 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable We need more information than that, like: What version of PostgreSQL? What does the hardware look like? What does the disk and tablespace layout look like? How are your configuration variables set? Other than that, are the statistics up to date on the VehicleMake table? Bob Lunney --- On Thu, 8/26/10, Ozer, Pam wrote: From: Ozer, Pam Subject: [PERFORM] Slow Query To: pgsql-performance@postgresql.org Date: Thursday, August 26, 2010, 8:03 PM =0A=0A=0A =0A =0A=0A=0A=0A=0A=0A=0A=0AI am new to Postgres and I am trying = to understand the=0AExplain Analyze so I can tune the following query.=C2= =A0 I run the same query using=0Amysql and it takes less than 50ms.=C2=A0 I= run it on postgres and it takes 10=0Aseconds. I feel like I am missing som= ething very obvious. (VehicleUsed is a big=0Atable over 750,000records) and= datasetgroupyearmakemodel has 150000 records. =0A=0A =C2=A0 =0A=0AIt looks= like the cost is highest in the Hash Join=C2=A0 on=0APostalcode.=C2=A0 =C2= =A0Am I reading this correctly.?=C2=A0 I do have indexes on the=0Alower(pos= talcode) in both tables.=C2=A0 Why wouldn=E2=80=99t be using the index?=C2= =A0 Thanks=0Ain advance for any help. =0A=0A =C2=A0 =0A=0AHere is my query:= =0A=0A =C2=A0 =0A=0Aselect=C2=A0 distinct VehicleMake.VehicleMake =0A=0Afr= om VehicleUsed =0A=0Ainner join PostalCodeRegionCountyCity on ( lower (=0AV= ehicleUsed.PostalCode ) =3D lower ( PostalCodeRegionCountyCity.PostalCode )= ) =0A=0A=C2=A0INNER JOIN DATASETGROUPYEARMAKEMODEL ON (=0AVEHICLEUSED.VEHI= CLEYEAR =3D DATASETGROUPYEARMAKEMODEL.VEHICLEYEAR ) =0A=0A=C2=A0AND ( VEHI= CLEUSED.VEHICLEMAKEID =3D=0ADATASETGROUPYEARMAKEMODEL.VEHICLEMAKEID ) =0A= =0A=C2=A0AND ( VEHICLEUSED.VEHICLEMODELID =3D=0ADATASETGROUPYEARMAKEMODEL.V= EHICLEMODELID ) =0A=0A=C2=A0inner join VehicleMake on ( VehicleUsed.Vehicle= MakeId =3D=0AVehicleMake.VehicleMakeId ) =0A=0Awhere =0A=0A( DatasetGroupY= earMakeModel.DatasetGroupId =3D 3 )=C2=A0 and =0A=0A=C2=A0( VehicleUsed.Dat= asetId <> 113 ) =0A=0A=C2=A0and ( VehicleUsed.ProductGroupId <> 13 ) =0A= =0A=C2=A0and ( PostalCodeRegionCountyCity.RegionId =3D 36 ) =0A=0Aorder by = VehicleMake.VehicleMake =0A=0A=C2=A0limit 500000 =0A=0A =C2=A0 =0A=0AHere i= s the explain analyze =0A=0A =C2=A0 =0A=0A"Limit=C2=A0 (cost=3D38292.53..38= 293.19 rows=3D261 width=3D8)=0A(actual time=3D10675.857..10675.892 rows=3D4= 2 loops=3D1)" =0A=0A"=C2=A0 ->=C2=A0 Sort=C2=A0 (cost=3D38292.53..38293.19 = rows=3D261=0Awidth=3D8) (actual time=3D10675.855..10675.868 rows=3D42 loops= =3D1)" =0A=0A"=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Sort Key: vehiclem= ake.vehiclemake" =0A=0A"=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Sort Met= hod:=C2=A0 quicksort=C2=A0 Memory: 18kB" =0A=0A"=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 ->=C2=A0 HashAggregate=C2=A0 (cost=3D38279.45..38282.06=0Ar= ows=3D261 width=3D8) (actual time=3D10675.710..10675.728 rows=3D42 loops=3D= 1)" =0A=0A"=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 ->=C2=A0 Hash Join=C2=A0 (cost=3D436.31..38270.51=0Arows=3D= 3576 width=3D8) (actual time=3D4.471..10658.291 rows=3D10425 loops=3D1)" = =0A=0A"=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Hash Cond:=0A(vehicleused.= vehiclemakeid =3D vehiclemake.vehiclemakeid)" =0A=0A"=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 ->=C2=A0 Hash Join=C2=A0=0A(cost=3D428.43..38213.47 rows= =3D3576 width=3D4) (actual time=3D4.152..10639.742=0Arows=3D10425 loops=3D1= )" =0A=0A"=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 Hash Cond:=0A(lower((vehicleused.postalcode)::text) =3D=0Alowe= r((postalcoderegioncountycity.postalcode)::text))" =0A=0A"=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ->=C2=A0 Neste= d Loop=C2=A0=0A(cost=3D101.81..37776.78 rows=3D11887 width=3D10) (actual ti= me=3D1.172..9876.586=0Arows=3D382528 loops=3D1)" =0A=0A"=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0->=C2=A0 Bitmap Heap=0AScan on datasetgroupyearmakemod= el=C2=A0 (cost=3D101.81..948.81 rows=3D5360 width=3D6)=0A(actual time=3D0.9= 88..17.800 rows=3D5377 loops=3D1)" =0A=0A"=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Recheck Cond:=0A(datasetgroupid =3D= 3)" =0A=0A"=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 ->=C2=A0 Bitmap=0AIndex Scan on datasetgroupyearmakemodel_i04=C2= =A0 (cost=3D0.00..100.47 rows=3D5360=0Awidth=3D0) (actual time=3D0.830..0.8= 30 rows=3D5377 loops=3D1)" =0A=0A"=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Index= =0ACond: (datasetgroupid =3D 3)" =0A=0A"=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 ->=C2=A0 Index Scan=0Ausing vehicleused_i10 on vehicleused=C2=A0 (cost= =3D0.00..6.85 rows=3D1 width=3D12) (actual=0Atime=3D0.049..1.775 rows=3D71 = loops=3D5377)" =0A=0A"=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 Index Cond:=0A((vehicleused.vehiclemodelid =3D datase= tgroupyearmakemodel.vehiclemodelid) AND=0A(vehicleused.vehiclemakeid =3D da= tasetgroupyearmakemodel.vehiclemakeid) AND=0A(vehicleused.vehicleyear =3D d= atasetgroupyearmakemodel.vehicleyear))" =0A=0A"=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Filter:=0A((vehicleused.datas= etid <> 113) AND (vehicleused.productgroupid <>=0A13))" =0A=0A"=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ->=C2=A0 Ha= sh=C2=A0=0A(cost=3D308.93..308.93 rows=3D1416 width=3D6) (actual time=3D2.7= 38..2.738 rows=3D1435=0Aloops=3D1)" =0A=0A"=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 ->=C2=A0 Bitmap Heap=0AScan on postalcoderegioncountycity=C2=A0 (cos= t=3D27.23..308.93 rows=3D1416 width=3D6)=0A(actual time=3D0.222..0.955 rows= =3D1435 loops=3D1)" =0A=0A"=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 Recheck Cond:=0A(regionid =3D 36)" =0A=0A"=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ->=C2=A0= Bitmap=0AIndex Scan on postalcoderegioncountycity_i05=C2=A0 (cost=3D0.00..= 26.87 rows=3D1416=0Awidth=3D0) (actual time=3D0.202..0.202 rows=3D1435 loop= s=3D1)" =0A=0A"=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Index=0ACond: (regionid = =3D 36)" =0A=0A"=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ->=C2=A0 Hash=C2= =A0 (cost=3D4.61..4.61=0Arows=3D261 width=3D10) (actual time=3D0.307..0.307= rows=3D261 loops=3D1)" =0A=0A"=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ->=C2=A0 Seq Scan on=0Avehiclemake=C2=A0 = (cost=3D0.00..4.61 rows=3D261 width=3D10) (actual time=3D0.033..0.154=0Arow= s=3D261 loops=3D1)" =0A=0A"Total runtime: 10676.058 ms" =0A=0A =C2=A0 =0A= =0A=0A=0APam Ozer =0A=0AData Architect =0A=0Apozer@automotive.com=0A =0A=0A= =0A =0A =0A tel. 949.705.3468 =0A =0A =0A=0A=0A=0A=0A=0A =0A =0A =0A = =0A =0A =0A =0A =0A Source Interlink Media =0A 1733 Alton Pkwy Suit= e 100, Irvine, CA 92606 =0A www.simautomotive.com=0A =0A =0A =0A=0A=0A= =0A=0AConfidentiality Notice- This electronic communication, and all=0Ainfo= rmation herein, including files attached hereto, is private, and is the=0Ap= roperty of the sender. This communication is intended only for the use of t= he=0Aindividual or entity named above. If you are not the intended recipien= t, you=0Aare hereby notified that any disclosure of; dissemination of; dist= ribution of;=0Acopying of; or, taking any action in reliance upon this comm= unication, is=0Astrictly prohibited. If you have received this communicatio= n in error, please=0Aimmediately notify us by telephone, (949)-705-3000, an= d destroy all copies of=0Athis communication. Thank you. =0A=0A=0A=0A =0A =0A =0A=0A=0A=0A =0A=0A=0A=0A=0A --0-1592438393-1282868377=:8843 Content-Type: multipart/related; boundary="0-1565241553-1282868377=:8843" --0-1565241553-1282868377=:8843 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
We need more information than that, like:
=
What version of PostgreSQL?
What does the hardware look like?
Wha= t does the disk and tablespace layout look like?
How are your configurat= ion variables set?

Other than that, are the statistics up to date on= the VehicleMake table?

Bob Lunney

--- On Thu, 8/26/10, Oz= er, Pam <pozer@automotive.com> wrote:

From: Ozer, Pam <pozer@automotive.com>
Subject: [PERFO= RM] Slow Query
To: pgsql-performance@postgresql.org
Date: Thursday, A= ugust 26, 2010, 8:03 PM

=0A=0A=0A =0A =0A=0A= =0A=0A
=0A=0A

I a= m new to Postgres and I am trying to understand the=0AExplain Analyze so I = can tune the following query.  I run the same query using=0Amysql and = it takes less than 50ms.  I run it on postgres and it takes 10=0Asecon= ds. I feel like I am missing something very obvious. (VehicleUsed is a big= =0Atable over 750,000records) and datasetgroupyearmakemodel has 150000 reco= rds.

=0A=0A

 

=0A=0A

It looks like the cost is highest in the Hash Joi= n  on=0APostalcode.   Am I reading this correctly.?  I = do have indexes on the=0Alower(postalcode) in both tables.  Why wouldn= =E2=80=99t be using the index?  Thanks=0Ain advance for any help.

= =0A=0A

 

=0A=0A

Here is my query:

=0A=0A

 

=0A=0A

select  distin= ct VehicleMake.VehicleMake

=0A=0A

from= VehicleUsed

=0A=0A

inner join PostalC= odeRegionCountyCity on ( lower (=0AVehicleUsed.PostalCode ) =3D lower ( Pos= talCodeRegionCountyCity.PostalCode ) )

=0A=0A

 INNER JOIN DATASETGROUPYEARMAKEMODEL ON (=0AVEHICLEUSED.VEHIC= LEYEAR =3D DATASETGROUPYEARMAKEMODEL.VEHICLEYEAR )

=0A=0A

 AND ( VEHICLEUSED.VEHICLEMAKEID =3D=0ADATASETGRO= UPYEARMAKEMODEL.VEHICLEMAKEID )

=0A=0A

 AND ( VEHICLEUSED.VEHICLEMODELID =3D=0ADATASETGROUPYEARMAKEMODEL.VE= HICLEMODELID )

=0A=0A

 inner join= VehicleMake on ( VehicleUsed.VehicleMakeId =3D=0AVehicleMake.VehicleMakeId= )

=0A=0A

where

=0A=0A

( DatasetGroupYearMakeModel.DatasetGroupId =3D 3 )&n= bsp; and

=0A=0A

 ( VehicleUsed.Da= tasetId <> 113 )

=0A=0A

 a= nd ( VehicleUsed.ProductGroupId <> 13 )

=0A=0A

 and ( PostalCodeRegionCountyCity.RegionId =3D 36 ) =0A=0A

order by VehicleMake.VehicleMake<= /p> =0A=0A

 limit 500000

=0A=0A  

=0A=0A

Here is the explain analyze

=0A=0A

 

=0A=0A

"Limit  (cos= t=3D38292.53..38293.19 rows=3D261 width=3D8)=0A(actual time=3D10675.857..10= 675.892 rows=3D42 loops=3D1)"

=0A=0A

"=   ->  Sort  (cost=3D38292.53..38293.19 rows=3D261=0Awidth= =3D8) (actual time=3D10675.855..10675.868 rows=3D42 loops=3D1)"

=0A=0A<= p class=3D"yiv30141525MsoNormal">"       = ; Sort Key: vehiclemake.vehiclemake"

=0A=0A

"        Sort Method:  quicks= ort  Memory: 18kB"

=0A=0A

" =        ->  HashAggregate  (cost= =3D38279.45..38282.06=0Arows=3D261 width=3D8) (actual time=3D10675.710..106= 75.728 rows=3D42 loops=3D1)"

=0A=0A

"&= nbsp;           &nbs= p; ->  Hash Join  (cost=3D436.31..38270.51=0Arows=3D3576 width= =3D8) (actual time=3D4.471..10658.291 rows=3D10425 loops=3D1)"

=0A=0A"       =              Ha= sh Cond:=0A(vehicleused.vehiclemakeid =3D vehiclemake.vehiclemakeid)"

= =0A=0A

"     &nbs= p;            &= nbsp; ->  Hash Join =0A(cost=3D428.43..38213.47 rows=3D3576 wi= dth=3D4) (actual time=3D4.152..10639.742=0Arows=3D10425 loops=3D1)"

=0A= =0A

"      &= nbsp;           &nbs= p;       Hash Cond:=0A(lower((vehicleused.pos= talcode)::text) =3D=0Alower((postalcoderegioncountycity.postalcode)::text))= "

=0A=0A

"    &nbs= p;            &= nbsp;        ->  Nested Loop&nbs= p;=0A(cost=3D101.81..37776.78 rows=3D11887 width=3D10) (actual time=3D1.172= ..9876.586=0Arows=3D382528 loops=3D1)"

=0A=0A

"           =              &n= bsp;       ->  Bitmap Heap=0ASca= n on datasetgroupyearmakemodel  (cost=3D101.81..948.81 rows=3D5360 wid= th=3D6)=0A(actual time=3D0.988..17.800 rows=3D5377 loops=3D1)"

=0A=0A"       =             &nb= sp;            =       Recheck Cond:=0A(datasetgroupid =3D 3)"

= =0A=0A

"     &nbs= p;            &= nbsp;           &nbs= p;       ->  Bitmap=0AIndex Scan on d= atasetgroupyearmakemodel_i04  (cost=3D0.00..100.47 rows=3D5360=0Awidth= =3D0) (actual time=3D0.830..0.830 rows=3D5377 loops=3D1)"

=0A=0A

"        = ;            &n= bsp;            = ;           Index=0ACond:= (datasetgroupid =3D 3)"

=0A=0A

" = ;            &n= bsp;            = ;      ->  Index Scan=0Ausing vehicleused_= i10 on vehicleused  (cost=3D0.00..6.85 rows=3D1 width=3D12) (actual=0A= time=3D0.049..1.775 rows=3D71 loops=3D5377)"

=0A=0A

"          =             &nb= sp;            =    Index Cond:=0A((vehicleused.vehiclemodelid =3D datasetgroupyea= rmakemodel.vehiclemodelid) AND=0A(vehicleused.vehiclemakeid =3D datasetgrou= pyearmakemodel.vehiclemakeid) AND=0A(vehicleused.vehicleyear =3D datasetgro= upyearmakemodel.vehicleyear))"

=0A=0A

= "            &n= bsp;            = ;             F= ilter:=0A((vehicleused.datasetid <> 113) AND (vehicleused.productgrou= pid <>=0A13))"

=0A=0A

" &nb= sp;            =             ->&nb= sp; Hash =0A(cost=3D308.93..308.93 rows=3D1416 width=3D6) (actual time= =3D2.738..2.738 rows=3D1435=0Aloops=3D1)"

=0A=0A

"          &nb= sp;            =          ->  Bitmap Heap=0A= Scan on postalcoderegioncountycity  (cost=3D27.23..308.93 rows=3D1416 = width=3D6)=0A(actual time=3D0.222..0.955 rows=3D1435 loops=3D1)"

=0A=0A=

"      &nbs= p;            &= nbsp;           &nbs= p;      Recheck Cond:=0A(regionid =3D 36)"

=0A= =0A

"      &= nbsp;           &nbs= p;            &= nbsp;      ->  Bitmap=0AIndex Scan on post= alcoderegioncountycity_i05  (cost=3D0.00..26.87 rows=3D1416=0Awidth=3D= 0) (actual time=3D0.202..0.202 rows=3D1435 loops=3D1)"

=0A=0A

"        =             &nb= sp;            =            Index=0ACond: = (regionid =3D 36)"

=0A=0A

"  = ;            &n= bsp;     ->  Hash  (cost=3D4.61..4.61=0Aro= ws=3D261 width=3D10) (actual time=3D0.307..0.307 rows=3D261 loops=3D1)"

= =0A=0A

"     &nb= sp;            =         ->  Seq Scan on=0Avehicl= emake  (cost=3D0.00..4.61 rows=3D261 width=3D10) (actual time=3D0.033.= .0.154=0Arows=3D261 loops=3D1)"

=0A=0A

"Total runtime: 10676.058 ms"

=0A=0A

=  

=0A=0A
=0A=0A

Pam Ozer

=0A=0A

Data Architect

=0A=0A

pozer@automotive.com=0A

=0A=0A=0A =0A =0A =0A
=0A

tel. 949.705.3468

=0A
=0A=0A
=0A=0A=0A =0A =0A =0A =0A = =0A
=0A

3D"Source

=0A= 		=0A

3D"vertical

=0A 		=0A

<= b>Source Interlink Media

=0A

1733 Alton Pkwy Suite 100, Irvine, CA 92606

=0A

www.simautomotive.com=0A

=0A
=0A=0A
=0A=0A

Confidentiality Notice- This electronic communi= cation, and all=0Ainformation herein, including files attached hereto, is p= rivate, and is the=0Aproperty of the sender. This communication is intended= only for the use of the=0Aindividual or entity named above. If you are not= the intended recipient, you=0Aare hereby notified that any disclosure of; = dissemination of; distribution of;=0Acopying of; or, taking any action in r= eliance upon this communication, is=0Astrictly prohibited. If you have rece= ived this communication in error, please=0Aimmediately notify us by telepho= ne, (949)-705-3000, and destroy all copies of=0Athis communication. Thank y= ou.

=0A=0A
=0A=0A


=0A
=0A=

=0A=0A
=0A=0A =0A=0A
<= br>=0A=0A --0-1565241553-1282868377=:8843 Content-Type: image/jpeg; name="image001.jpg" Content-Transfer-Encoding: base64 Content-Id: <1.998394907@web39705.mail.mud.yahoo.com> Content-Description: image001.jpg /9j/4AAQSkZJRgABAgAAZABkAAD/7AARRHVja3kAAQAEAAAAQAAA/+4ADkFkb2JlAGTAAAAAAf/b AIQABQQEBAQEBQQEBQgFBAUICQcFBQcJCwkJCQkJCw4LDAwMDAsODAwNDg0MDBAQEREQEBcXFxcX GhoaGhoaGhoaGgEGBgYLCgsUDg4UFxIPEhcaGhoaGhoaGhoaGhoaGhoaGhoaGhoaGhoaGhoaGhoa GhoaGhoaGhoaGhoaGhoaGhoa/8AAEQgANABqAwERAAIRAQMRAf/EAKkAAAIDAQEBAAAAAAAAAAAA AAUGAwQHCAIAAQEAAwEBAQAAAAAAAAAAAAAAAQIDBAUGEAAABQIFAQUEBQYJDQAAAAABAgMEBRES ABMUBgchMUEiFQhRYTIWQmIjMzRxU3MkNRhScrLSQ2O0Rlez00RUZHQlVWVmFyc3EQACAgEDAgMF CAMAAAAAAAAAARECAyESBBMFUWEUMXGBMjPwQaHhIlJiU7HRgv/aAAwDAQACEQMRAD8AYWzZAsi+ HerKWWdiboZvQpwPUbr80o191MfWWs9q6TrB89Wur6itIShF9sNN1xTiKfSUQiRZK8rtIil43+Io mTOShDh4R8A0xnmrkeKysq29xpjeNZFtdq+8NbkYud48qK7ddvVEWZBsSt6gmRNDNG0o9KmGvXGG C6w8Xelr+Zrmq8ufa3p+QQ3JxBGQ8FISrWTcnXZImWKRQCCU1gVEBtAB6hjPB3S17qrqtS+bgVrV tN6ABeQePuIiatYyxm0sCCJjiIiCYJXgFR9gnHHQqKvL0++pi7t8fX9xtexuuzoL/c0f5OPD5n1r e89XjfTr7jK9piP/AJD3f1/opD/KY9bk/Qx/8nBg+tf4iTtaO2i/SdG3PNKxaqZig2KmmY95RAbh GhD0p0x3cjJlrGyu44sFMdk99oDMO2YsN/QrfZcmvJtTnSM4WEhiDS4c0ogIFqUEwqIiGMMtrWwW eVKrNsaVc1VjbaL76MX3zyfIw0g+USaoKLFTEvisTRCgFIA+EK9+M6ZFg4yslrp+Je1HmzureiDs tws3aNgXiZoyLshy0UeGAiYB/GIFQN7MYYu7NuLV08jW/bkl+m2vmBuU0XbaK2m3kHZXrpJFUi7t IwmKoJRIFQMNK9O/G3bmna7ShSZ85NVom5AQM9h0CqE/X9Gjjo3Z/Gn4mO3F4XGn5f2j/wBX/Y+q +BP7n+D+l93Zjk6+X+Pzx9vI6ulj/l8v2+J5ZwUlDpO5KB5FjU4VRUqJnqjogkuCokIYwidMD2iP QBxlbnYbpb6alq8XLVvbfQuNtiS26nbOdld2MpeNZm6u2hiqhama4xQOUCkD3iI9MF3DFSrVKxI9 He1k72mDzuuDhJCcLvCA3tGRx3J6Auo8TKXOTIBTZahDCAjaIXF9/vxnxufWuPZesotm4jtffW0M Fu2klJtzsXnKMQ4bL0KogMiUQOFewQAev5Ma05vHq5VNfgUtxs1lDuMB4LZDPZy+ynm7o5GRKuLh Zc7pAokchQKCmY4GAAKFoh24533B9bqRp7I8jb0i6WyQLGw842jlSxPJEaWKjwDNMi+AUkCnNQtw hUCAY3ZUe3HRfncezl01+BjXi5qqFfQJbLYbS24eSfSG9Yh7Kv0ToFMV8jaUFOpjCJj3GMY1O7GH L5yyQkoqjXj8Xpy25bJ9tbG2ntxoZXeL+KeEkjkGKcKLlIU4AHiAhjiQD1uD4a4tyu5Wu1smpGDh VqnuhhN/xiRvuqOn4BRvFxrEUlHLepwERTOJjiHaAAYlA6jiKdxnE6XmzZNuHGRWrokAJ/bca5mH G9ts71jo9sutaq7M7IVNNwcviICxDCWpw62j1xbB3Cqxql6zBTLw7O++loPCuyt17njM1fezSUhA MJzrkXMsgGXW4bihZ4Ovf0xpTn4KOa0hlb8TLdQ76Em5dswcnt7byUXuyLTjorMahILukwTVXNac SlMUxi3d9ta0xnx+4Kt7WsvmL5uG7Vqk/lJZGC3zDopry3ILWPQUG1JV05MiUw0rQonAoCNMT6vj f1kenz/vPtFur/EVj+D1P4v+h/P9n3X1+zEeq4/9ZboZv3nHrIAD0wzVA/vY2/spseZ952I0Db8j O+nKSbsJU6rzivf8cVw3ciUTaZyu2ARHp2HTMYCnD6ZKG7QxBIxenPizZnJvE6ae72R3hYyXemaZ ax0bRVSQA1bBCtbQwbEAT05cObC3ypvBfcUeo4UgpUqEaJF1E7EwE4gA2iF3wh24NiJM/lWTB1y7 yER7FwsmRORcZac9IqR6ZPtzBVIyaqInN7QERxJA9aCDZ8K8niwjIKMkVEo4FU4GSVkb0CuS0Mrm LLWUOPhpSuAENvEbTDjwj5XiOccSPluYO6SulytDK5fRzaBBLl3eKnZTAFvcJiN+FOGll35XKJJe TUMepqNygumIpiJgD4Kde72dMCTtQOW+NNwkXhYTdce/lXiKxGrNFYDKKGBIw0KHf0AcVgk4XZAH 7sEqNP73tv7EbFiDQmD+d9Ob1zt+VOs94t35GKKx7sSifTuV23aFOw5DHAihQ+Ilpu0KYe0GeLXh 6cdvCkUDK/Obiwo9gm0JKAOANCByruTm1m09TJTRLdFIny9EF/Y5jDSwoqXnAUjj2muG43Q5gDpg Dt7QRH+qtvw2m+7T/C/m+z7r6vw4oWOJW/NvFCm1X8Q04ZXU2gi6TeSKSa4mbJuRDLTOocCCBTCH hCpsXgqE5T1S8fbtiWm0ZPjFaWiyGRTYxZnJFKHTCxIqYFTvuAPCFOvdiIJJoL1T7H48QcbdhOM3 MCkmuc7qPB0UglXoBTXFOmJgN4QDCAiPaHqi2Ft1y4ZbM4xctHc0uVRdu0dFOdwuIiBfDlmETDcP QMIBFM7x2G+cv56c9PEmdwsZR3IPlwWKAiNTqKHHLAod4iOEFZADHnzhKMYScWw4k08fMpppSbcr wLVyJHBQhTVL9E4AbphBYd4X1P7dmIU2ytv8WSEhCpNBamiGi+cUrSmWJRAiRjWUGmEENgeZ5K2E y26zjJ7gSRa7ZilVVmibkVU0EFHAhmGuMmFBPQO0cIEi7Ec7cHQMihLRHEIM5FqIi3cpvAuIJiiU aVKIfCIhhBJcZc6cQO4ZTZ8fw8ovDrLjJKxSLm8DLIJDcsIFIJvAiA1HuDCCArPeqzjjdEMjt/cH GykjDNxTFuzWdpiVMUgtIJRBMDBQvToOEEgQfUBwsMIjtweJf+Bt3Qv0WOsCwrkxATFQPDW4SBTt wgDHvf1J7D3Cgwjt98UrvEippvY5J44IBgSXJUiiZssDWqE9g0HCAA/3iOI/8MXP4Ly79pn/AAf5 j9H7sTBUzvjrdY7S463E8OmR7HOZqObTEQoYLXrFVu6KskID7upT/QNaYOuDBZPGwPE5Ft3QUqlL Sc/4ePViiUVWbNYAzXrggD9k5SAwt0yj2KXnD4S4BsReWDgryTupQD5gHkFjZgGuuqPbd1rX24Fk T8NqES5X2QooYCJkmGRjnMIAAACxaiIjgGPc7t2S8vlDJ7F3G2NkriV2tNZqKfhMN508gt5C9olu 6h34FDCMC5pnF7OWkYTkCPgiqKS7mIbA1RQPYqcSyjQ5gJ1KI0IUwiAdwDgQxg2dB772w6l5TeZX MdtnyeTRkBkFqpL5zU6aSIEMccw51zEtKAD1691cAzE8CTQ+GlE0t7iZQ4EL5PPluMIAFTQ7sACo +0RpgRYzzAk0HiLYam/t3oMVETLREeUXssUggUx0UxCiRRHpcscSph7KibuwlL2uEQ34G7c48d7g nNrrz7xskMpt0uobHbmAxRjj0zWtOlulPRRIOyy+ndjy+F3GufNkVaZK0n9O+lq/5R6nJxYvTYrK y6qW3JXz1iy8fBnJv8zHqnk/7O6zeknhsHzqNHcUkEgzRB07a6lvmJomrRQxcmoF8I9cVkvB5d+k zhpizQfu9wSiTN0FzdYV0RBQBIKnhogIj4Cib8mEiAUh6beAXSZ1W+7ZFUhDAQRKskNTiIhaAaep jVKNQDr0HCQWm/pe4LcrN26O6pHUulxat0DOECKHWBPNsApkANdlhcHTqHZhIgnkPStwpFODs5Hc 8i2dJoao7c7pC8ERUKkB7cmtBUMBQ9o4biIKJ/TPwMmkqqpumTTIjbmgdVMpigetoiUW4GtG0etK YSSSsfTFwVIGRKy3VIqmcGKRAAcIlvMfoUC1QCtw9A9uEsQSSXpb4QinAtJHc8mi5KJQMiK6JjgJ iicAoVAR6lCv5MJIgkX9KXCraJTnHG4pRKKVEpU3RlkgKYTmsKABkXVE3QOmEkkLX0vcFu0jLN91 SApkNlnEXKBBKYSHUCoGRAQASJnGo9PCPswkQRo+mPghcaE3TJB4BVLeskQDEClTFE6AXAFwdmEg kjvTTwe6f+WRu7JU0gYbTN010ymqFR6/YBT4R7cJIgINPS1xFItWztluyVdNHyp2rVVN4iciqpQP cmUQS6iAJnqHuwkbS9+5jxj/AM0mOyn36P8AmcNxMBNLzHzIc/V/M2ukvOa6XKysn9arT7fI02Tp 7ul2X78QBrhPmzN4/wDPfL/Kal0GiztRf5atZm5ngpl3XW/Sp3YAgZeZ6Db3keT5z5/OaXW36a29 7mX5fjrb8Nvf29K4ABzeb82ufnnJ1uQOX5VfT7gdNkZn2mqz/g99tO/AFJXW6uL8x/8AomoL5vqK Wa3Xx+mvyv8ARMqlmX3V+lXAD0b5k8/k/mnRav5fd6Hy3Ny8vNLm5md4rq2W06duABb/AMv8vZeY 5mT8ptsnK++1Gcjp8r+tz7LPrUwABU8x0u3tXk/OOtkvmbU6qzW5JLqaH7XLyrMmnhspgB5nfNfk zb+l03m/mUdp7tTpszUhbdnfrNtO27rgBM3r5vq5D5u02u0yFnll+VptJK0+/wDFmZl31aU9+CBH un5xzx+YaabSGzdBrdHflFsrqf1fsr914ru3pXEoDbuzzDN355dm3aOL1mnt1GkqvqMm7w5uTdbX v9+IApMfOPN23yvl6PzNlka/K0mt8vcVy9D0t0mXfT6Vv1sAaV/7W/7e7P8Abe3Ak//Z --0-1565241553-1282868377=:8843 Content-Type: image/jpeg; name="image002.jpg" Content-Transfer-Encoding: base64 Content-Id: <2.998394907@web39705.mail.mud.yahoo.com> Content-Description: image002.jpg /9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAoHBwgHBgoICAgLCgoLDhgQDg0NDh0VFhEYIx8lJCIf IiEmKzcvJik0KSEiMEExNDk7Pj4+JS5ESUM8SDc9Pjv/wAALCAAyAAEBAREA/8QAHwAAAQUBAQEB AQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQAAAF9AQIDAAQRBRIhMUEGE1Fh ByJxFDKBkaEII0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3ODk6Q0RFRkdISUpTVFVWV1hZ WmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMXG x8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/9oACAEBAAA/APVfOk9aZRRRTtp9K0do 9BX/2Q== --0-1565241553-1282868377=:8843-- --0-1592438393-1282868377=:8843--