X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BB3008B9C01 for ; Tue, 1 Feb 2005 19:21:18 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 04408-03 for ; Tue, 1 Feb 2005 19:21:07 +0000 (GMT) Received: from out011.verizon.net (out011pub.verizon.net [206.46.170.135]) by svr1.postgresql.org (Postfix) with ESMTP id 9B8348B9BD0 for ; Tue, 1 Feb 2005 19:21:02 +0000 (GMT) Received: from jfradkin ([66.15.127.235]) by out011.verizon.net (InterMail vM.5.01.06.06 201-253-122-130-106-20030910) with ESMTP id <20050201192049.ILXO18675.out011.verizon.net@jfradkin>; Tue, 1 Feb 2005 13:20:49 -0600 From: "Joel Fradkin" To: "'Scott Marlowe'" Cc: Subject: Re: MSSQL versus Postgres timing Date: Tue, 1 Feb 2005 14:23:15 -0500 Message-ID: <000001c50893$9b5f0e80$797ba8c0@jfradkin> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable In-Reply-To: <1107277147.16640.162.camel@state.g2switchworks.com> X-Authentication-Info: Submitted using SMTP AUTH at out011.verizon.net from [66.15.127.235] at Tue, 1 Feb 2005 13:20:49 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=4.078 tagged_above=0 required=5 tests=AWL, MSGID_DOLLARS, WEIRD_QUOTING X-Spam-Level: **** X-Archive-Number: 200502/30 X-Sequence-Number: 20141 QUERY PLAN "Merge Join (cost=3D47489.81..47975.65 rows=3D3758 width=3D111) (actual time=3D27167.305..29701.080 rows=3D85694 loops=3D1)" " Merge Cond: (""outer"".locationid =3D ""inner"".locationid)" " -> Sort (cost=3D1168.37..1169.15 rows=3D312 width=3D48) (actual time=3D261.096..262.410 rows=3D402 loops=3D1)" " Sort Key: l.locationid" " -> Index Scan using ix_tbllocation on tbllocation l (cost=3D0.00..1155.44 rows=3D312 width=3D48) (actual = time=3D213.107..259.160 rows=3D402 loops=3D1)" " Index Cond: ('SAKS'::text =3D (clientnum)::text)" " -> Sort (cost=3D46321.45..46535.47 rows=3D85611 width=3D74) (actual time=3D26906.148..27689.258 rows=3D85695 loops=3D1)" " Sort Key: a.locationid" " -> Merge Right Join (cost=3D38119.24..39307.55 rows=3D85611 = width=3D74) (actual time=3D22236.915..25384.945 rows=3D99139 loops=3D1)" " Merge Cond: (((""outer"".clientnum)::text =3D ""inner"".""?column10?"") AND (""outer"".id =3D ""inner"".jobtitleid))" " -> Index Scan using ix_tbljobtitle_id on tbljobtitle jt (cost=3D0.00..338.90 rows=3D6337 width=3D37) (actual = time=3D164.976..2290.760 rows=3D5662 loops=3D1)" " Filter: (1 =3D presentationid)" " -> Sort (cost=3D38119.24..38333.26 rows=3D85611 = width=3D52) (actual time=3D20667.645..21031.627 rows=3D99139 loops=3D1)" " Sort Key: (a.clientnum)::text, a.jobtitleid" " -> Seq Scan on tblassociate a = (cost=3D0.00..31105.34 rows=3D85611 width=3D52) (actual time=3D14.768..16024.395 rows=3D99139 = loops=3D1)" " Filter: ((clientnum)::text =3D 'SAKS'::text)" "Total runtime: 30319.859 ms" Joel Fradkin =20 -----Original Message----- From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]=20 Sent: Tuesday, February 01, 2005 11:59 AM To: Joel Fradkin Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] MSSQL versus Postgres timing On Tue, 2005-02-01 at 10:54, Joel Fradkin wrote: > All is moving along well. >=20 > I have all my views and data and am testing things out a bit. >=20 > A table with 645,000 records for associates has view (basically select > * from tblassociates where clientnum =3D 'test') What does=20 explain analyze select * from tblassociates where clientnum =3D 'test'=20 say?