From jfradkin@wazagua.com Tue Feb 1 16:51:44 2005 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 2E42B8B9B37 for ; Tue, 1 Feb 2005 16:51:44 +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 91455-01 for ; Tue, 1 Feb 2005 16:51:34 +0000 (GMT) Received: from out004.verizon.net (out004pub.verizon.net [206.46.170.142]) by svr1.postgresql.org (Postfix) with ESMTP id 414F48B9B83 for ; Tue, 1 Feb 2005 16:51:33 +0000 (GMT) Received: from jfradkin ([66.15.127.235]) by out004.verizon.net (InterMail vM.5.01.06.06 201-253-122-130-106-20030910) with ESMTP id <20050201165120.FDOS17674.out004.verizon.net@jfradkin> for ; Tue, 1 Feb 2005 10:51:20 -0600 From: "Joel Fradkin" To: Subject: MSSQL versus Postgres timing Date: Tue, 1 Feb 2005 11:54:11 -0500 Message-ID: <000001c5087e$b92ddfa0$797ba8c0@jfradkin> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0001_01C50854.D057D7A0" X-Authentication-Info: Submitted using SMTP AUTH at out004.verizon.net from [66.15.127.235] at Tue, 1 Feb 2005 10:51:20 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=3.097 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE, MSGID_DOLLARS X-Spam-Level: *** X-Archive-Number: 200502/24 X-Sequence-Number: 20135 This is a multi-part message in MIME format. ------=_NextPart_000_0001_01C50854.D057D7A0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable All is moving along well. I have all my views and data and am testing things out a bit. A table with 645,000 records for associates has view (basically select * from tblassociates where clientnum =3D 'test') This is taking 13 seconds in postgres and 3 seconds in MSSQL. =20 I tried making an index on clientnum (there is one on clinetnum, = associateID in MSSQL). I did an analyze. I did a set seq scan off =20 Is there a possibility postgres is this much slower, or (my hope) I am missing some key concept. Be glad to provide the view and tables etc. =20 Joel Fradkin =20 =20 ------=_NextPart_000_0001_01C50854.D057D7A0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

All is moving along well.

I have all my views and data and am testing things = out a bit.

A table with 645,000 records for associates has view = (basically select * from tblassociates where clientnum =3D = ‘test’)

This is taking 13 seconds in postgres and 3 seconds = in MSSQL.

 

I tried making an index on clientnum (there is one on = clinetnum, associateID in MSSQL).

I did an analyze.

I did a set seq scan off

 

Is there a possibility postgres is this much slower, = or (my hope) I am missing some key concept.

Be glad to provide the view and tables = etc.

 

Joel Fradkin

 

 

------=_NextPart_000_0001_01C50854.D057D7A0-- From smarlowe@g2switchworks.com Tue Feb 1 16:59:13 2005 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 B903C8B9B72 for ; Tue, 1 Feb 2005 16:59:13 +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 92020-08 for ; Tue, 1 Feb 2005 16:59:10 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id DF4098B9B6A for ; Tue, 1 Feb 2005 16:59:10 +0000 (GMT) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 01 Feb 2005 10:59:07 -0600 Subject: Re: MSSQL versus Postgres timing From: Scott Marlowe To: Joel Fradkin Cc: pgsql-sql@postgresql.org In-Reply-To: <000001c5087e$b92ddfa0$797ba8c0@jfradkin> References: <000001c5087e$b92ddfa0$797ba8c0@jfradkin> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Message-Id: <1107277147.16640.162.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Tue, 01 Feb 2005 10:59:07 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/25 X-Sequence-Number: 20136 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 =E2=80=98test=E2=80=99) What does=20 explain analyze select * from tblassociates where clientnum =3D =E2=80=98te= st=E2=80=99=20 say? From mike@fuhr.org Tue Feb 1 17:07:04 2005 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 E8A2A8B9B6F for ; Tue, 1 Feb 2005 17:07:04 +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 93473-01 for ; Tue, 1 Feb 2005 17:07:01 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id AB4588B9B6A for ; Tue, 1 Feb 2005 17:06:59 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id j11H6t7Q008563 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Tue, 1 Feb 2005 10:06:57 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id j11H6sJa065565; Tue, 1 Feb 2005 10:06:54 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.1/8.13.1/Submit) id j11H6sGg065564; Tue, 1 Feb 2005 10:06:54 -0700 (MST) (envelope-from mfuhr) Date: Tue, 1 Feb 2005 10:06:54 -0700 From: Michael Fuhr To: Joel Fradkin Cc: pgsql-sql@postgresql.org Subject: Re: MSSQL versus Postgres timing Message-ID: <20050201170654.GA65518@winnie.fuhr.org> References: <000001c5087e$b92ddfa0$797ba8c0@jfradkin> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <000001c5087e$b92ddfa0$797ba8c0@jfradkin> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/27 X-Sequence-Number: 20138 On Tue, Feb 01, 2005 at 11:54:11AM -0500, Joel Fradkin wrote: > > A table with 645,000 records for associates has view (basically select * > from tblassociates where clientnum = 'test') > > This is taking 13 seconds in postgres and 3 seconds in MSSQL. Please post the EXPLAIN ANALYZE output for the slow query, once with enable_seqscan on and once with it off. For example: SET enable_seqscan TO on; -- if not already on EXPLAIN ANALYZE SELECT * FROM tblassociates WHERE clientnum = 'test'; SET enable_seqscan TO off; EXPLAIN ANALYZE SELECT * FROM tblassociates WHERE clientnum = 'test'; > Be glad to provide the view and tables etc. Please do -- it might help us spot something that could be improved. What version of PostgreSQL are you using? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ From BAnderson@PresiNET.com Tue Feb 1 17:25:58 2005 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 AA2FE8B9B2A for ; Tue, 1 Feb 2005 17:25:58 +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 95401-03 for ; Tue, 1 Feb 2005 17:25:44 +0000 (GMT) Received: from presinet-main.presinet.com (mail.presinet.com [209.53.156.1]) by svr1.postgresql.org (Postfix) with ESMTP id 907388B9B9A for ; Tue, 1 Feb 2005 17:25:43 +0000 (GMT) Received: from [10.10.1.151] (BRICK [10.10.1.151]) by presinet-main.presinet.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2658.3) id CZZVSBL9; Tue, 1 Feb 2005 09:24:15 -0800 Message-ID: <41FFBB95.3090306@PresiNET.com> Date: Tue, 01 Feb 2005 09:25:41 -0800 From: Bricklen Anderson User-Agent: Debian Thunderbird 1.0 (X11/20050116) X-Accept-Language: en-us, en MIME-Version: 1.0 Cc: Joel Fradkin , pgsql-sql@postgresql.org Subject: Re: MSSQL versus Postgres timing References: <000001c5087e$b92ddfa0$797ba8c0@jfradkin> <20050201170654.GA65518@winnie.fuhr.org> In-Reply-To: <20050201170654.GA65518@winnie.fuhr.org> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200502/28 X-Sequence-Number: 20139 Michael Fuhr wrote: > On Tue, Feb 01, 2005 at 11:54:11AM -0500, Joel Fradkin wrote: > >>A table with 645,000 records for associates has view (basically select * >>from tblassociates where clientnum = 'test') >> >>This is taking 13 seconds in postgres and 3 seconds in MSSQL. > > > Please post the EXPLAIN ANALYZE output for the slow query, once > with enable_seqscan on and once with it off. For example: > > SET enable_seqscan TO on; -- if not already on > EXPLAIN ANALYZE SELECT * FROM tblassociates WHERE clientnum = 'test'; > > SET enable_seqscan TO off; > EXPLAIN ANALYZE SELECT * FROM tblassociates WHERE clientnum = 'test'; > > >>Be glad to provide the view and tables etc. > > > Please do -- it might help us spot something that could be improved. > What version of PostgreSQL are you using? > Also, is clientnum a string datatype, or are you doing implicit type conversion? From jfradkin@wazagua.com Tue Feb 1 19:21:18 2005 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? From jfradkin@wazagua.com Tue Feb 1 19:23:18 2005 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 1ACFB8B9C90 for ; Tue, 1 Feb 2005 19:23: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 04708-01 for ; Tue, 1 Feb 2005 19:23:15 +0000 (GMT) Received: from out011.verizon.net (out011pub.verizon.net [206.46.170.135]) by svr1.postgresql.org (Postfix) with ESMTP id B6E5C8B9C1E for ; Tue, 1 Feb 2005 19:23:01 +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 <20050201192248.IMZY18675.out011.verizon.net@jfradkin>; Tue, 1 Feb 2005 13:22:48 -0600 From: "Joel Fradkin" To: "'Michael Fuhr'" Cc: Subject: Re: MSSQL versus Postgres timing Date: Tue, 1 Feb 2005 14:26:07 -0500 Message-ID: <000101c50893$e25da5d0$797ba8c0@jfradkin> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0002_01C50869.F9879DD0" In-Reply-To: <20050201170654.GA65518@winnie.fuhr.org> X-Authentication-Info: Submitted using SMTP AUTH at out011.verizon.net from [66.15.127.235] at Tue, 1 Feb 2005 13:22:48 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=4.095 tagged_above=0 required=5 tests=AWL, MSGID_DOLLARS, WEIRD_QUOTING X-Spam-Level: **** X-Archive-Number: 200502/31 X-Sequence-Number: 20142 This is a multi-part message in MIME format. ------=_NextPart_000_0002_01C50869.F9879DD0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit With seq scan on. -----Original Message----- From: Michael Fuhr [mailto:mike@fuhr.org] Sent: Tuesday, February 01, 2005 12:07 PM To: Joel Fradkin Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] MSSQL versus Postgres timing On Tue, Feb 01, 2005 at 11:54:11AM -0500, Joel Fradkin wrote: > > A table with 645,000 records for associates has view (basically select * > from tblassociates where clientnum = 'test') > > This is taking 13 seconds in postgres and 3 seconds in MSSQL. Please post the EXPLAIN ANALYZE output for the slow query, once with enable_seqscan on and once with it off. For example: SET enable_seqscan TO on; -- if not already on EXPLAIN ANALYZE SELECT * FROM tblassociates WHERE clientnum = 'test'; SET enable_seqscan TO off; EXPLAIN ANALYZE SELECT * FROM tblassociates WHERE clientnum = 'test'; > Be glad to provide the view and tables etc. Please do -- it might help us spot something that could be improved. What version of PostgreSQL are you using? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ------=_NextPart_000_0002_01C50869.F9879DD0 Content-Type: text/plain; name="explainanalyzeviwassoclist.txt" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="explainanalyzeviwassoclist.txt" 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" ------=_NextPart_000_0002_01C50869.F9879DD0-- From jfradkin@wazagua.com Tue Feb 1 20:16:44 2005 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 520EC8B9C05 for ; Tue, 1 Feb 2005 20:16:44 +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 09151-07 for ; Tue, 1 Feb 2005 20:16:32 +0000 (GMT) Received: from out005.verizon.net (out005pub.verizon.net [206.46.170.143]) by svr1.postgresql.org (Postfix) with ESMTP id 7A0938B9B53 for ; Tue, 1 Feb 2005 20:16:32 +0000 (GMT) Received: from jfradkin ([66.15.127.235]) by out005.verizon.net (InterMail vM.5.01.06.06 201-253-122-130-106-20030910) with ESMTP id <20050201201632.BTOX1013.out005.verizon.net@jfradkin> for ; Tue, 1 Feb 2005 14:16:32 -0600 From: "Joel Fradkin" To: Subject: Re: MSSQL versus Postgres timing Date: Tue, 1 Feb 2005 15:19:19 -0500 Message-ID: <000001c5089b$63d56650$797ba8c0@jfradkin> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable In-Reply-To: <20050201170654.GA65518@winnie.fuhr.org> X-Authentication-Info: Submitted using SMTP AUTH at out005.verizon.net from [66.15.127.235] at Tue, 1 Feb 2005 14:16:32 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=3.162 tagged_above=0 required=5 tests=AWL, MSGID_DOLLARS X-Spam-Level: *** X-Archive-Number: 200502/33 X-Sequence-Number: 20144 View and table creates CREATE TABLE tblassociate ( clientnum varchar(16) NOT NULL, associateid int4 NOT NULL, associatenum varchar(10), firstname varchar(50), middleinit varchar(5), lastname varchar(50), ssn varchar(18), dob timestamp, address varchar(100), city varchar(50), state varchar(50), country varchar(50), zip varchar(10), homephone varchar(14), cellphone varchar(14), pager varchar(14), associateaccount varchar(50), doh timestamp, dot timestamp, rehiredate timestamp, lastdayworked timestamp, staffexecid int4, jobtitleid int4, locationid int4, deptid int4, positionnum int4, worktypeid int4, sexid int4, maritalstatusid int4, ethnicityid int4, weight float8, heightfeet int4, heightinches int4, haircolorid int4, eyecolorid int4, isonalarmlist bool NOT NULL DEFAULT false, isactive bool NOT NULL DEFAULT true, ismanager bool NOT NULL DEFAULT false, issecurity bool NOT NULL DEFAULT false, createdbyid int4, isdeleted bool NOT NULL DEFAULT false, militarybranchid int4, militarystatusid int4, patrontypeid int4, identificationtypeid int4, workaddress varchar(200), testtypeid int4, testscore int4, pin int4, county varchar(50), CONSTRAINT pk_tblassociate PRIMARY KEY (clientnum, associateid), CONSTRAINT ix_tblassociate UNIQUE (clientnum, associatenum) ) CREATE TABLE tbllocation ( clientnum varchar(16) NOT NULL, locationid int4 NOT NULL, districtid int4 NOT NULL, regionid int4 NOT NULL, divisionid int4 NOT NULL, locationnum varchar(8), name varchar(50), clientlocnum varchar(50), address varchar(100), address2 varchar(100), city varchar(50), state varchar(2) NOT NULL DEFAULT 'zz'::character varying, zip varchar(10), countryid int4, phone varchar(15), fax varchar(15), payname varchar(40), contact char(36), active bool NOT NULL DEFAULT true, coiprogram text, coilimit text, coiuser varchar(255), coidatetime varchar(32), ec_note_field varchar(1050), locationtypeid int4, open_time timestamp, close_time timestamp, insurance_loc_id varchar(50), lpregionid int4, sic int4, CONSTRAINT pk_tbllocation PRIMARY KEY (clientnum, locationid), CONSTRAINT ix_tbllocation_1 UNIQUE (clientnum, locationnum, name), CONSTRAINT ix_tbllocation_unique_number UNIQUE (clientnum, divisionid, regionid, districtid, locationnum) ) CREATE TABLE tbljobtitle ( clientnum varchar(16) NOT NULL, id int4 NOT NULL, value varchar(50), code varchar(16), isdeleted bool DEFAULT false, presentationid int4 NOT NULL DEFAULT 1, CONSTRAINT pk_tbljobtitle PRIMARY KEY (clientnum, id, presentationid) ) CREATE OR REPLACE VIEW viwassoclist AS=20 SELECT a.clientnum, a.associateid, a.associatenum, a.lastname, = a.firstname, jt.value AS jobtitle, l.name AS "location", l.locationid AS = mainlocationid, l.divisionid, l.regionid, l.districtid, (a.lastname::text || ', '::text) = || a.firstname::text AS assocname, a.isactive, a.isdeleted FROM tblassociate a LEFT JOIN tbljobtitle jt ON a.jobtitleid =3D jt.id AND = jt.clientnum::text =3D a.clientnum::text AND 1 =3D jt.presentationid JOIN tbllocation l ON a.locationid =3D l.locationid AND = l.clientnum::text =3D a.clientnum::text; =20 -----Original Message----- From: Michael Fuhr [mailto:mike@fuhr.org]=20 Sent: Tuesday, February 01, 2005 12:07 PM To: Joel Fradkin Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] MSSQL versus Postgres timing On Tue, Feb 01, 2005 at 11:54:11AM -0500, Joel Fradkin wrote: >=20 > A table with 645,000 records for associates has view (basically select = * > from tblassociates where clientnum =3D 'test') >=20 > This is taking 13 seconds in postgres and 3 seconds in MSSQL. Please post the EXPLAIN ANALYZE output for the slow query, once with enable_seqscan on and once with it off. For example: SET enable_seqscan TO on; -- if not already on EXPLAIN ANALYZE SELECT * FROM tblassociates WHERE clientnum =3D 'test'; SET enable_seqscan TO off; EXPLAIN ANALYZE SELECT * FROM tblassociates WHERE clientnum =3D 'test'; > Be glad to provide the view and tables etc. Please do -- it might help us spot something that could be improved. What version of PostgreSQL are you using? --=20 Michael Fuhr http://www.fuhr.org/~mfuhr/ From jfradkin@wazagua.com Tue Feb 1 20:16:44 2005 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 704558B9C1E for ; Tue, 1 Feb 2005 20:16:44 +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 09297-03 for ; Tue, 1 Feb 2005 20:16:34 +0000 (GMT) Received: from out005.verizon.net (out005pub.verizon.net [206.46.170.143]) by svr1.postgresql.org (Postfix) with ESMTP id 075078B9B64 for ; Tue, 1 Feb 2005 20:16:34 +0000 (GMT) Received: from jfradkin ([66.15.127.235]) by out005.verizon.net (InterMail vM.5.01.06.06 201-253-122-130-106-20030910) with ESMTP id <20050201201634.BTQD1013.out005.verizon.net@jfradkin> for ; Tue, 1 Feb 2005 14:16:34 -0600 From: "Joel Fradkin" To: Subject: Re: MSSQL versus Postgres timing Date: Tue, 1 Feb 2005 15:19:19 -0500 Message-ID: <000101c5089b$654bb2a0$797ba8c0@jfradkin> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0002_01C50871.7C75AAA0" In-Reply-To: <20050201170654.GA65518@winnie.fuhr.org> X-Authentication-Info: Submitted using SMTP AUTH at out005.verizon.net from [66.15.127.235] at Tue, 1 Feb 2005 14:16:32 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=4.143 tagged_above=0 required=5 tests=AWL, MSGID_DOLLARS, WEIRD_QUOTING X-Spam-Level: **** X-Archive-Number: 200502/32 X-Sequence-Number: 20143 This is a multi-part message in MIME format. ------=_NextPart_000_0002_01C50871.7C75AAA0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit -----Original Message----- From: Michael Fuhr [mailto:mike@fuhr.org] Sent: Tuesday, February 01, 2005 12:07 PM To: Joel Fradkin Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] MSSQL versus Postgres timing On Tue, Feb 01, 2005 at 11:54:11AM -0500, Joel Fradkin wrote: > > A table with 645,000 records for associates has view (basically select * > from tblassociates where clientnum = 'test') > > This is taking 13 seconds in postgres and 3 seconds in MSSQL. Please post the EXPLAIN ANALYZE output for the slow query, once with enable_seqscan on and once with it off. For example: SET enable_seqscan TO on; -- if not already on EXPLAIN ANALYZE SELECT * FROM tblassociates WHERE clientnum = 'test'; SET enable_seqscan TO off; EXPLAIN ANALYZE SELECT * FROM tblassociates WHERE clientnum = 'test'; > Be glad to provide the view and tables etc. Please do -- it might help us spot something that could be improved. What version of PostgreSQL are you using? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ------=_NextPart_000_0002_01C50871.7C75AAA0 Content-Type: text/plain; name="explainanalyzeviwassoclist_noseqscan.txt" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="explainanalyzeviwassoclist_noseqscan.txt" QUERY PLAN=0A= "Merge Join (cost=3D109454.32..109940.16 rows=3D3758 width=3D111) = (actual time=3D11646.990..13474.449 rows=3D85694 loops=3D1)"=0A= " Merge Cond: (""outer"".locationid =3D ""inner"".locationid)"=0A= " -> Sort (cost=3D1168.37..1169.15 rows=3D312 width=3D48) (actual = time=3D35.359..36.651 rows=3D402 loops=3D1)"=0A= " Sort Key: l.locationid"=0A= " -> Index Scan using ix_tbllocation on tbllocation l = (cost=3D0.00..1155.44 rows=3D312 width=3D48) (actual = time=3D29.811..33.415 rows=3D402 loops=3D1)"=0A= " Index Cond: ('SAKS'::text =3D (clientnum)::text)"=0A= " -> Sort (cost=3D108285.96..108499.98 rows=3D85611 width=3D74) = (actual time=3D11611.560..11995.898 rows=3D85695 loops=3D1)"=0A= " Sort Key: a.locationid"=0A= " -> Merge Right Join (cost=3D100083.75..101272.06 rows=3D85611 = width=3D74) (actual time=3D7758.824..10598.571 rows=3D99139 loops=3D1)"=0A= " Merge Cond: (((""outer"".clientnum)::text =3D = ""inner"".""?column10?"") AND (""outer"".id =3D ""inner"".jobtitleid))"=0A= " -> Index Scan using ix_tbljobtitle_id on tbljobtitle jt = (cost=3D0.00..338.90 rows=3D6337 width=3D37) (actual = time=3D0.089..30.193 rows=3D5662 loops=3D1)"=0A= " Filter: (1 =3D presentationid)"=0A= " -> Sort (cost=3D100083.75..100297.77 rows=3D85611 = width=3D52) (actual time=3D7709.988..8543.451 rows=3D99139 loops=3D1)"=0A= " Sort Key: (a.clientnum)::text, a.jobtitleid"=0A= " -> Index Scan using ix_associate_clientnum on = tblassociate a (cost=3D0.00..93069.85 rows=3D85611 width=3D52) (actual = time=3D0.281..2046.482 rows=3D99139 loops=3D1)"=0A= " Index Cond: ((clientnum)::text =3D = 'SAKS'::text)"=0A= "Total runtime: 13899.614 ms"=0A= ------=_NextPart_000_0002_01C50871.7C75AAA0-- From lists@boutiquenumerique.com Tue Feb 1 21:44:46 2005 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 527048B9BBB for ; Tue, 1 Feb 2005 21:44:46 +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 16334-04 for ; Tue, 1 Feb 2005 21:44:43 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id 7A15F8B9B86 for ; Tue, 1 Feb 2005 21:44:43 +0000 (GMT) Received: (qmail 27751 invoked from network); 1 Feb 2005 22:45:05 +0100 Received: from unknown (HELO musicbox) (boutiquenumerique-lists@192.168.0.2) by boutiquenumerique.com with SMTP; 1 Feb 2005 22:45:05 +0100 To: "Joel Fradkin" , pgsql-sql@postgresql.org Subject: Re: MSSQL versus Postgres timing References: <000001c5089b$63d56650$797ba8c0@jfradkin> Message-ID: Date: Tue, 01 Feb 2005 22:47:19 +0100 From: PFC Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit In-Reply-To: <000001c5089b$63d56650$797ba8c0@jfradkin> User-Agent: Opera M2/7.54 (Linux, build 751) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/34 X-Sequence-Number: 20145 > CREATE OR REPLACE VIEW viwassoclist AS > SELECT a.clientnum, a.associateid, a.associatenum, a.lastname, > a.firstname, > jt.value AS jobtitle, l.name AS "location", l.locationid AS > mainlocationid, > l.divisionid, l.regionid, l.districtid, (a.lastname::text || ', '::text) > || > a.firstname::text AS assocname, a.isactive, a.isdeleted > FROM tblassociate a > LEFT JOIN tbljobtitle jt ON a.jobtitleid = jt.id AND > jt.clientnum::text = > a.clientnum::text AND 1 = jt.presentationid > JOIN tbllocation l ON a.locationid = l.locationid AND > l.clientnum::text = > a.clientnum::text; Try removing those ::text casts... and creating two column indexes on the columns you use in your Join From tgl@sss.pgh.pa.us Tue Feb 1 21:53:35 2005 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 489308B9B86 for ; Tue, 1 Feb 2005 21:53:35 +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 17266-02 for ; Tue, 1 Feb 2005 21:53:23 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 2AE238B9BD8 for ; Tue, 1 Feb 2005 21:53:24 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j11LrNd5019309; Tue, 1 Feb 2005 16:53:23 -0500 (EST) To: "Joel Fradkin" Cc: "'Michael Fuhr'" , pgsql-sql@postgresql.org Subject: Re: MSSQL versus Postgres timing In-reply-to: <000101c50893$e25da5d0$797ba8c0@jfradkin> References: <000101c50893$e25da5d0$797ba8c0@jfradkin> Comments: In-reply-to "Joel Fradkin" message dated "Tue, 01 Feb 2005 14:26:07 -0500" Date: Tue, 01 Feb 2005 16:53:23 -0500 Message-ID: <19308.1107294803@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/35 X-Sequence-Number: 20146 "Joel Fradkin" writes: > " -> Sort (cost=38119.24..38333.26 rows=85611 width=52) (actual time=20667.645..21031.627 rows=99139 loops=1)" > " Sort Key: (a.clientnum)::text, a.jobtitleid" > " -> Seq Scan on tblassociate a (cost=0.00..31105.34 rows=85611 width=52) (actual time=14.768..16024.395 rows=99139 loops=1)" > " Filter: ((clientnum)::text = 'SAKS'::text)" The sort steps seem slower than they ought to be. I suspect you ought to raise sort_mem ... try 10MB instead of 1MB. Also, if you are running in a locale other than C and don't have a good reason for doing so, it would be worth trying C locale instead. The results with enable_seqscan off also suggest that random_page_cost may be too high for your environment. BTW, the schema you posted does not match these plans --- there are indexes referenced in the plans that do not appear in the schema. regards, tom lane From jfradkin@wazagua.com Tue Feb 1 22:05:58 2005 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 A10B08B9B6A for ; Tue, 1 Feb 2005 22:05:58 +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 18160-04 for ; Tue, 1 Feb 2005 22:05:53 +0000 (GMT) Received: from out009.verizon.net (out009pub.verizon.net [206.46.170.131]) by svr1.postgresql.org (Postfix) with ESMTP id B9C178B9B31 for ; Tue, 1 Feb 2005 22:05:53 +0000 (GMT) Received: from jfradkin ([66.15.127.235]) by out009.verizon.net (InterMail vM.5.01.06.06 201-253-122-130-106-20030910) with ESMTP id <20050201220553.KEH4172.out009.verizon.net@jfradkin>; Tue, 1 Feb 2005 16:05:53 -0600 From: "Joel Fradkin" To: "'Tom Lane'" Cc: Subject: Re: MSSQL versus Postgres timing Date: Tue, 1 Feb 2005 17:09:13 -0500 Message-ID: <000e01c508aa$aab7c540$797ba8c0@jfradkin> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit In-Reply-To: <19308.1107294803@sss.pgh.pa.us> X-Authentication-Info: Submitted using SMTP AUTH at out009.verizon.net from [66.15.127.235] at Tue, 1 Feb 2005 16:05:53 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=3.189 tagged_above=0 required=5 tests=AWL, MSGID_DOLLARS X-Spam-Level: *** X-Archive-Number: 200502/36 X-Sequence-Number: 20147 I have added indexes for clientnum (and clientnum and unique identifier like jobtitleid for jobtitle table) to see if it would help sorry about it not matching. I gave you the definition outlined in PGadmin table window (I can add the indexes if it will help). It is still running slower even when I force an indexed scan. I will look at the other ideas you mentioned as we have added indexes to another view with the same results (slower then MSSQL) I did not put in the ::text it did that in PGadmin the original text I ran to create the view was. CREATE OR REPLACE VIEW viwassoclist as select a.clientnum, a.associateid, a.associatenum, a.lastname, a.firstname, jt.value as jobtitle, l.name as location, l.locationid as mainlocationid, l.divisionid, l.regionid, l.districtid, a.lastname || ', ' || a.firstname as assocname, a.isactive, a.isdeleted from tblassociate a left outer join tbljobtitle jt on a.jobtitleid = jt.id and jt.clientnum = a.clientnum and 1= jt.presentationid inner join tbllocation l on a.locationid = l.locationid and l.clientnum = a.clientnum ; Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 jfradkin@wazagua.com www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Tuesday, February 01, 2005 4:53 PM To: Joel Fradkin Cc: 'Michael Fuhr'; pgsql-sql@postgresql.org Subject: Re: [SQL] MSSQL versus Postgres timing "Joel Fradkin" writes: > " -> Sort (cost=38119.24..38333.26 rows=85611 width=52) (actual time=20667.645..21031.627 rows=99139 loops=1)" > " Sort Key: (a.clientnum)::text, a.jobtitleid" > " -> Seq Scan on tblassociate a (cost=0.00..31105.34 rows=85611 width=52) (actual time=14.768..16024.395 rows=99139 loops=1)" > " Filter: ((clientnum)::text = 'SAKS'::text)" The sort steps seem slower than they ought to be. I suspect you ought to raise sort_mem ... try 10MB instead of 1MB. Also, if you are running in a locale other than C and don't have a good reason for doing so, it would be worth trying C locale instead. The results with enable_seqscan off also suggest that random_page_cost may be too high for your environment. BTW, the schema you posted does not match these plans --- there are indexes referenced in the plans that do not appear in the schema. regards, tom lane