From jfradkin@wazagua.com Wed Jan  26 03:47:55 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 195F83A5A1B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 03:47:55 +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 52174-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 03:47:41 +0000 (GMT)
Received: from out009.verizon.net (out009pub.verizon.net [206.46.170.131])
	by svr1.postgresql.org (Postfix) with ESMTP id B222C3A5A01
	for <pgsql-sql@postgresql.org>; Wed, 26 Jan 2005 03:47:43 +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 <20050126034743.RXZE24088.out009.verizon.net@jfradkin>
	for <pgsql-sql@postgresql.org>; Tue, 25 Jan 2005 21:47:43 -0600
From: "Joel Fradkin" <jfradkin@wazagua.com>
To: <pgsql-sql@postgresql.org>
Subject: same question little different test MSSQL vrs Postgres
Date: Tue, 25 Jan 2005 22:50:51 -0500
Message-ID: <000901c5035a$3b619cc0$797ba8c0@jfradkin>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_000A_01C50330.528B94C0"
X-Authentication-Info: Submitted using SMTP AUTH at out009.verizon.net from
	[66.15.127.235] at Tue, 25 Jan 2005 21:47:43 -0600
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=3.079 tagged_above=0 required=5 tests=AWL,
	HTML_MESSAGE, MSGID_DOLLARS
X-Spam-Level: ***
X-Archive-Number: 200501/241
X-Sequence-Number: 20029

This is a multi-part message in MIME format.

------=_NextPart_000_000A_01C50330.528B94C0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I also tried a simple select * from tblcase where clientum =3D 'SAKS'

On both MSSQL and Postgres.

MSSQL was 3 secs, Postgres was 27 secs.

=20

There is a key for clientnum, but it appeared on both systems (identical
Dell Desktops Postgres is running Linux MSSQL is XP) it did not do a =
indexed
search.

=20

Joel Fradkin

=20

Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305

=20

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.

=20


=20

=20


------=_NextPart_000_000A_01C50330.528B94C0
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html>

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">


<meta name=3DGenerator content=3D"Microsoft Word 10 (filtered)">

<style>
<!--
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{font-family:Arial;
	color:windowtext;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}
-->
</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>I also tried a simple select * from tblcase where =
clientum =3D
&#8216;SAKS&#8217;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>On both MSSQL and Postgres.</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>MSSQL was 3 secs, Postgres was 27 =
secs.</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>There is a key for clientnum, but it appeared on both
systems (identical Dell Desktops Postgres is running Linux MSSQL is XP) =
it did
not do a indexed search.</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
 font-family:Arial'>Joel Fradkin</span></font></p>

<div>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:
12.0pt'>&nbsp;</span></font></p>

</div>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>Wazagua, Inc.<br>
</span></font><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;font-family:
  Arial'>2520 Trailmate Dr</span></font><font size=3D2 =
face=3DArial><span
style=3D'font-size:10.0pt;font-family:Arial'><br>
</span></font><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;font-family:
  Arial'>Sarasota</span></font><font size=3D2 face=3DArial><span =
style=3D'font-size:
 10.0pt;font-family:Arial'>, </span></font><font size=3D2 =
face=3DArial><span
  =
style=3D'font-size:10.0pt;font-family:Arial'>Florida</span></font><font =
size=3D2
 face=3DArial><span style=3D'font-size:10.0pt;font-family:Arial'> =
</span></font><font
  size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;font-family:Arial'>34243</span></font><font
size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;font-family:Arial'><br>
Tel.&nbsp; 941-753-7111 ext 305</span></font></p>

<div>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:
12.0pt'>&nbsp;</span></font></p>

</div>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><a =
href=3D"mailto:jfradkin@wazagua.com">jfradkin@wazagua.com</a><br>
<a href=3D"http://www.wazagua.com">www.wazagua.com</a><br>
Powered by Wazagua<br>
Providing you with the latest Web-based technology &amp; advanced =
tools.<br>
&copy; 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA,&nbsp;Inc<br>
&nbsp;This email message is for the use of the intended recipient(s) and =
may
contain confidential and privileged information.&nbsp; Any unauthorized =
review,
use, disclosure or distribution is prohibited.&nbsp; 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.</span></font></p>

<div>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:
12.0pt'>&nbsp;</span></font></p>

</div>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><br>
</span></font>&nbsp;</p>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:
12.0pt'>&nbsp;</span></font></p>

</div>

</body>

</html>

------=_NextPart_000_000A_01C50330.528B94C0--




From dennis@illusions.com Wed Jan  26 04:20:07 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 EFF733A5A71
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 04:20:07 +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 56097-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 04:20:01 +0000 (GMT)
Received: from illusions.com (unknown [69.28.129.146])
	by svr1.postgresql.org (Postfix) with SMTP id 7328E3A5A5F
	for <pgsql-sql@postgresql.org>; Wed, 26 Jan 2005 04:19:58 +0000 (GMT)
Received: (qmail 26320 invoked from network); 26 Jan 2005 04:20:29 -0000
Received: from localhost (HELO ?127.0.0.1?) (127.0.0.1)
	by illusions.com with SMTP; 26 Jan 2005 04:20:29 -0000
Message-ID: <41F71AB4.5030208@illusions.com>
Date: Tue, 25 Jan 2005 21:21:08 -0700
From: Dennis Sacks <dennis@illusions.com>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Joel Fradkin <jfradkin@wazagua.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: same question little different test MSSQL vrs Postgres
References: <000901c5035a$3b619cc0$797ba8c0@jfradkin>
In-Reply-To: <000901c5035a$3b619cc0$797ba8c0@jfradkin>
Content-Type: multipart/alternative;
	boundary="------------050706060502030005020509"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.505 tagged_above=0 required=5 tests=AWL,
	HTML_MESSAGE, HTML_TITLE_EMPTY
X-Spam-Level: 
X-Archive-Number: 200501/243
X-Sequence-Number: 20031

This is a multi-part message in MIME format.
--------------050706060502030005020509
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Joel Fradkin wrote:

> I also tried a simple select * from tblcase where clientum = 'SAKS'
>
> On both MSSQL and Postgres.
>
> MSSQL was 3 secs, Postgres was 27 secs.
>
>  
>
> There is a key for clientnum, but it appeared on both systems 
> (identical Dell Desktops Postgres is running Linux MSSQL is XP) it did 
> not do a indexed search.
>

One of the things you'll want to do regularly is run a "vacuum analyze". 
You can read up on this in the postgresql docs. This is essential to the 
indexes being used properly. At a bare minimum, after you import a large 
amount of data, you'll want to run vacuum analyze.

Dennis Sacks
dennis@illusions.com

--------------050706060502030005020509
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Joel Fradkin wrote:
<blockquote cite="mid000901c5035a$3b619cc0$797ba8c0@jfradkin"
 type="cite">
  <meta http-equiv="Content-Type" content="text/html; ">
  <meta name="Generator" content="Microsoft Word 10 (filtered)">
  <style>
<!--
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{font-family:Arial;
	color:windowtext;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}
-->
  </style>
  <div class="Section1">
  <p class="MsoNormal"><font face="Arial" size="2"><span
 style="font-size: 10pt; font-family: Arial;">I also tried a simple
select * from tblcase where clientum =
&#8216;SAKS&#8217;</span></font></p>
  <p class="MsoNormal"><font face="Arial" size="2"><span
 style="font-size: 10pt; font-family: Arial;">On both MSSQL and
Postgres.</span></font></p>
  <p class="MsoNormal"><font face="Arial" size="2"><span
 style="font-size: 10pt; font-family: Arial;">MSSQL was 3 secs,
Postgres was 27 secs.</span></font></p>
  <p class="MsoNormal"><font face="Arial" size="2"><span
 style="font-size: 10pt; font-family: Arial;">&nbsp;</span></font></p>
  <p class="MsoNormal"><font face="Arial" size="2"><span
 style="font-size: 10pt; font-family: Arial;">There is a key for
clientnum, but it appeared on both
systems (identical Dell Desktops Postgres is running Linux MSSQL is XP)
it did
not do a indexed search.</span></font></p>
  </div>
</blockquote>
<br>
One of the things you'll want to do regularly is run a "vacuum
analyze". You can read up on this in the postgresql docs. This is
essential to the indexes being used properly. At a bare minimum, after
you import a large amount of data, you'll want to run vacuum analyze.<br>
<br>
Dennis Sacks<br>
<a class="moz-txt-link-abbreviated" href="mailto:dennis@illusions.com">dennis@illusions.com</a><br>
</body>
</html>

--------------050706060502030005020509--



From gsstark@mit.edu Wed Jan  26 06:26:56 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 5FCB83A59DA
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 06:26:56 +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 73245-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 06:26:50 +0000 (GMT)
Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162])
	by svr1.postgresql.org (Postfix) with ESMTP id 4974F3A5A8F
	for <pgsql-sql@postgresql.org>; Wed, 26 Jan 2005 06:26:49 +0000 (GMT)
Received: from localhost ([127.0.0.1] helo=stark.xeocode.com)
	by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian))
	id 1CtgdZ-0005Rn-00; Wed, 26 Jan 2005 01:26:45 -0500
To: "Joel Fradkin" <jfradkin@wazagua.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: same question little different test MSSQL vrs Postgres
References: <000901c5035a$3b619cc0$797ba8c0@jfradkin>
In-Reply-To: <000901c5035a$3b619cc0$797ba8c0@jfradkin>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 26 Jan 2005 01:26:44 -0500
Message-ID: <878y6g1za3.fsf@stark.xeocode.com>
Lines: 13
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.058 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200501/244
X-Sequence-Number: 20032


"Joel Fradkin" <jfradkin@wazagua.com> writes:

> I also tried a simple select * from tblcase where clientum = 'SAKS'

Try:

explain analyze select * from tblcase where clientum = 'SAKS'

Send the output.

-- 
greg




From jfradkin@wazagua.com Wed Jan  26 13:26:01 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 982DD3A587F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 13:26:01 +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 16935-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 13:25:39 +0000 (GMT)
Received: from out011.verizon.net (out011pub.verizon.net [206.46.170.135])
	by svr1.postgresql.org (Postfix) with ESMTP id 37C2E3A5BA5
	for <pgsql-sql@postgresql.org>; Wed, 26 Jan 2005 13:25:40 +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 <20050126132540.FJHT4717.out011.verizon.net@jfradkin>;
	Wed, 26 Jan 2005 07:25:40 -0600
From: "Joel Fradkin" <jfradkin@wazagua.com>
To: <gsstark@mit.edu>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: same question little different test MSSQL vrs Postgres
Date: Wed, 26 Jan 2005 08:28:48 -0500
Message-ID: <000401c503aa$f8ee5620$797ba8c0@jfradkin>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-Reply-To: <878y6g1za3.fsf@stark.xeocode.com>
X-Authentication-Info: Submitted using SMTP AUTH at out011.verizon.net from
	[66.15.127.235] at Wed, 26 Jan 2005 07:25:40 -0600
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=3.078 tagged_above=0 required=5 tests=AWL,
	MSGID_DOLLARS
X-Spam-Level: ***
X-Archive-Number: 200501/254
X-Sequence-Number: 20042

QUERY PLAN
"Seq Scan on tblcase  (cost=0.00..30066.21 rows=37401 width=996) (actual
time=0.344..962.260 rows=22636 loops=1)"
"  Filter: ((clientnum)::text = 'SAKS'::text)"
"Total runtime: 1034.434 ms"

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: gsstark@mit.edu [mailto:gsstark@mit.edu] 
Sent: Wednesday, January 26, 2005 1:27 AM
To: Joel Fradkin
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] same question little different test MSSQL vrs Postgres


"Joel Fradkin" <jfradkin@wazagua.com> writes:

> I also tried a simple select * from tblcase where clientum = 'SAKS'

Try:

explain analyze select * from tblcase where clientum = 'SAKS'

Send the output.

-- 
greg




From dev@archonet.com Wed Jan  26 13:59:06 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 D135F3A5BAF
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 13:59:06 +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 19961-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 13:59:01 +0000 (GMT)
Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net
	[194.217.242.88])
	by svr1.postgresql.org (Postfix) with ESMTP id B994F3A5B90
	for <pgsql-sql@postgresql.org>; Wed, 26 Jan 2005 13:59:03 +0000 (GMT)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-30.mail.demon.net with esmtp (Exim 4.42)
	id 1CtnhF-000OLF-2L; Wed, 26 Jan 2005 13:59:02 +0000
Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 965C3166B1; Wed, 26 Jan 2005 13:58:58 +0000 (GMT)
Message-ID: <41F7A222.3040009@archonet.com>
Date: Wed, 26 Jan 2005 13:58:58 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Joel Fradkin <jfradkin@wazagua.com>
Cc: gsstark@mit.edu, pgsql-sql@postgresql.org
Subject: Re: same question little different test MSSQL vrs Postgres
References: <000401c503aa$f8ee5620$797ba8c0@jfradkin>
In-Reply-To: <000401c503aa$f8ee5620$797ba8c0@jfradkin>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.071 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200501/256
X-Sequence-Number: 20044

Joel Fradkin wrote:
> QUERY PLAN
> "Seq Scan on tblcase  (cost=0.00..30066.21 rows=37401 width=996) (actual
> time=0.344..962.260 rows=22636 loops=1)"
> "  Filter: ((clientnum)::text = 'SAKS'::text)"
> "Total runtime: 1034.434 ms"

That's only 1 second - to return 22,636 rows. Not 27 seconds, as in the 
original post. You'll never persuade PG to use the index when some 75% 
of your rows match the filter - it just doesn't make sense.

--
   Richard Huxton
   Archonet Ltd



From jfradkin@wazagua.com Wed Jan  26 14:54:55 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 7C2EC3A58E6
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 14:54:55 +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 26198-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 14:54:49 +0000 (GMT)
Received: from out014.verizon.net (out014pub.verizon.net [206.46.170.46])
	by svr1.postgresql.org (Postfix) with ESMTP id E3C923A58C0
	for <pgsql-sql@postgresql.org>; Wed, 26 Jan 2005 14:54:51 +0000 (GMT)
Received: from jfradkin ([66.15.127.235]) by out014.verizon.net
	(InterMail vM.5.01.06.06 201-253-122-130-106-20030910) with ESMTP
	id <20050126145452.JDWV28388.out014.verizon.net@jfradkin>;
	Wed, 26 Jan 2005 08:54:52 -0600
From: "Joel Fradkin" <jfradkin@wazagua.com>
To: "'Richard Huxton'" <dev@archonet.com>
Cc: <gsstark@mit.edu>, <pgsql-sql@postgresql.org>
Subject: Re: same question little different test MSSQL vrs Postgres
Date: Wed, 26 Jan 2005 09:58:00 -0500
Message-ID: <000001c503b7$6ea08800$797ba8c0@jfradkin>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <41F7A222.3040009@archonet.com>
X-Authentication-Info: Submitted using SMTP AUTH at out014.verizon.net from
	[66.15.127.235] at Wed, 26 Jan 2005 08:54:52 -0600
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=3.078 tagged_above=0 required=5 tests=AWL,
	MSGID_DOLLARS
X-Spam-Level: ***
X-Archive-Number: 200501/257
X-Sequence-Number: 20045

Well last evening (did not try it this morning) it was taking the extra
time.

I have made some adjustments to the config file per a few web sites that =
you
all recommended my looking at.

It is now using 137 of 756 meg avail.
it is now taking 8 secs to return 22,000 rows (using pgadminIII in a sql
edit window).

The EXPLAIN ANALYSE still shows the same as below, but the table has =
344,000
recs of which only 22636 are clientnum =3D 'SAKS'

I am still doing a seq search (this applies to the view question where =
if it
is a small result set it used a index search but on a larger return set =
it
did a seq search) in my view, but with the adjustments to the kernel I =
get a
result in 140 secs (MSSQL was 135 secs).

This is not production, I am still very worried that I have to do all =
this
tweeking to use this, MSSQL worked out of the box as it does (not saying =
its
great, but I never had to adjust a kernel setting etc). Since we cannot
afford the 70,000 dollars they want to license it I am not implying I =
can
use MSSQL, but I could look at other DB's like MYSQL, or Firebird, etc.

I have a lot of time now (two weeks) in this conversion and do not wish =
to
give up, I will see if I can learn what is needed to get the maximum
performance. I have seen much information available and this list has =
been a
huge resource. I really appreciate all the help.


Joel Fradkin
=20
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305
=20
> QUERY PLAN
> "Seq Scan on tblcase  (cost=3D0.00..30066.21 rows=3D37401 width=3D996) =
(actual
> time=3D0.344..962.260 rows=3D22636 loops=3D1)"
> "  Filter: ((clientnum)::text =3D 'SAKS'::text)"
> "Total runtime: 1034.434 ms"

That's only 1 second - to return 22,636 rows. Not 27 seconds, as in the=20
original post. You'll never persuade PG to use the index when some 75%=20
of your rows match the filter - it just doesn't make sense.

--
   Richard Huxton
   Archonet Ltd




From dev@archonet.com Wed Jan  26 15:20:59 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 7586B3A5BF7
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 15:20:59 +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 31055-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 15:20:47 +0000 (GMT)
Received: from anchor-post-36.mail.demon.net (anchor-post-36.mail.demon.net
	[194.217.242.86])
	by svr1.postgresql.org (Postfix) with ESMTP id ED6C33A5C14
	for <pgsql-sql@postgresql.org>; Wed, 26 Jan 2005 15:20:50 +0000 (GMT)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-36.mail.demon.net with esmtp (Exim 4.42)
	id 1CtoyN-0000yu-LM; Wed, 26 Jan 2005 15:20:49 +0000
Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 0B77316730; Wed, 26 Jan 2005 15:20:45 +0000 (GMT)
Message-ID: <41F7B54C.5020204@archonet.com>
Date: Wed, 26 Jan 2005 15:20:44 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Joel Fradkin <jfradkin@wazagua.com>
Cc: gsstark@mit.edu, pgsql-sql@postgresql.org
Subject: Re: same question little different test MSSQL vrs Postgres
References: <000001c503b7$6ea08800$797ba8c0@jfradkin>
In-Reply-To: <000001c503b7$6ea08800$797ba8c0@jfradkin>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.07 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200501/259
X-Sequence-Number: 20047

Joel Fradkin wrote:
> Well last evening (did not try it this morning) it was taking the extra
> time.
> 
> I have made some adjustments to the config file per a few web sites that you
> all recommended my looking at.

The crucial one I'd say is the performance guide at:
   http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
The first half-dozen settings are the crucial ones.

> It is now using 137 of 756 meg avail.
> it is now taking 8 secs to return 22,000 rows (using pgadminIII in a sql
> edit window).

That might be too much RAM. Don't forget PG likes to work with your 
operating-system (unlike many other DBs). Make sure Windows is using 
enough RAM to cache diskspace.
I'm curious as to how this takes 8secs whereas you had 1 second earlier. 
Are you sure some of this isn't pgadmin's overhead to display the rows?

> The EXPLAIN ANALYSE still shows the same as below, but the table has 344,000
> recs of which only 22636 are clientnum = 'SAKS'

That sounds like it's about the borderline between using an index and 
not (depending on cache-size, disk speeds etc).

> I am still doing a seq search (this applies to the view question where if it
> is a small result set it used a index search but on a larger return set it
> did a seq search) in my view, but with the adjustments to the kernel I get a
> result in 140 secs (MSSQL was 135 secs).

If you want to check whether the index would help, try issuing the 
following before running your query:
   SET ENABLE_SEQSCAN=FALSE;
This will force PG to use any index it can regardless of whether it 
thinks it will help.

> This is not production, I am still very worried that I have to do all this
> tweeking to use this, MSSQL worked out of the box as it does (not saying its
> great, but I never had to adjust a kernel setting etc). Since we cannot
> afford the 70,000 dollars they want to license it I am not implying I can
> use MSSQL, but I could look at other DB's like MYSQL, or Firebird, etc.

I'm a little curious what kernel settings you are changing on Windows. I 
wasn't aware there was much to be done there.

I'm afraid you do have to change half a dozen settings in 
postgresql.conf to match your workload, but PG runs on a much wider 
range of machines than MSSQL so it's difficult to come up with a 
"reasonable" default. Takes me about 5 minutes when I setup an 
installation to make sure the figures are reasonable (rather than the 
best they can be).

> I have a lot of time now (two weeks) in this conversion and do not wish to
> give up, I will see if I can learn what is needed to get the maximum
> performance. I have seen much information available and this list has been a
> huge resource. I really appreciate all the help.

--
   Richard Huxton
   Archonet Ltd



From jfradkin@wazagua.com Wed Jan  26 15:59:41 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 C892B3A5C58
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 15:59:41 +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 35445-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 15:59:37 +0000 (GMT)
Received: from out010.verizon.net (out010pub.verizon.net [206.46.170.133])
	by svr1.postgresql.org (Postfix) with ESMTP id 46BE43A5C52
	for <pgsql-sql@postgresql.org>; Wed, 26 Jan 2005 15:59:36 +0000 (GMT)
Received: from jfradkin ([66.15.127.235]) by out010.verizon.net
	(InterMail vM.5.01.06.06 201-253-122-130-106-20030910) with ESMTP
	id <20050126155935.EFJD24714.out010.verizon.net@jfradkin>;
	Wed, 26 Jan 2005 09:59:35 -0600
From: "Joel Fradkin" <jfradkin@wazagua.com>
To: "'Richard Huxton'" <dev@archonet.com>
Cc: <gsstark@mit.edu>, <pgsql-sql@postgresql.org>
Subject: Re: same question little different test MSSQL vrs Postgres
Date: Wed, 26 Jan 2005 11:02:43 -0500
Message-ID: <000001c503c0$792d8e40$797ba8c0@jfradkin>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <41F7B54C.5020204@archonet.com>
X-Authentication-Info: Submitted using SMTP AUTH at out010.verizon.net from
	[66.15.127.235] at Wed, 26 Jan 2005 09:59:35 -0600
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=3.078 tagged_above=0 required=5 tests=AWL,
	MSGID_DOLLARS
X-Spam-Level: ***
X-Archive-Number: 200501/261
X-Sequence-Number: 20049

The postgres is running on Linux Fedora core 3 (production will be =
redhat on
Dell 4 proc 8 gig box).

My client pgadminIII is running on XP.

Sorry I was not clearer on this.

I am playing with the settings now, I got it to return in 100 secs (the =
view
that is that took 135 on MSSQL). My testing is using identical Dell =
desktops
for the MSSQL and the Linux, with a third machine for the clients.

I do not mind getting up to speed on the proper setting to optimize the
hardware, I am worried that as production environment can be somewhat
dynamic that I will have issues getting a optimized environment and that =
it
will work for our needs. My whole reason for being here is that our duel
proc production MSSQL server is just no longer keeping up with the =
demand,
so it is important that whatever I implement is going to up to the
challenge. I am still convinced Postgres was the correct choice, =
especially
with all the guidance I have been able to get here. 100 seconds will be =
fine
compared to the 135 of MSSQL, I just was getting worse responses before
adjusting. At the moment I think I went too far as I see it using swap =
and
going slower, but it never used much of the 756 meg (137 max was all I =
ever
saw it use).

I guess the swap buffers and cache are the important settings (least =
that
seems to be what is affecting the memory). Not sure exactly what would =
cause
it to use seq vrs index, but I will try the force and see if it helps =
the
speed.

Joel Fradkin
=20
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305
=20
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.
=20

=20

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]=20
Sent: Wednesday, January 26, 2005 10:21 AM
To: Joel Fradkin
Cc: gsstark@mit.edu; pgsql-sql@postgresql.org
Subject: Re: [SQL] same question little different test MSSQL vrs =
Postgres

Joel Fradkin wrote:
> Well last evening (did not try it this morning) it was taking the =
extra
> time.
>=20
> I have made some adjustments to the config file per a few web sites =
that
you
> all recommended my looking at.

The crucial one I'd say is the performance guide at:
   http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
The first half-dozen settings are the crucial ones.

> It is now using 137 of 756 meg avail.
> it is now taking 8 secs to return 22,000 rows (using pgadminIII in a =
sql
> edit window).

That might be too much RAM. Don't forget PG likes to work with your=20
operating-system (unlike many other DBs). Make sure Windows is using=20
enough RAM to cache diskspace.
I'm curious as to how this takes 8secs whereas you had 1 second earlier. =

Are you sure some of this isn't pgadmin's overhead to display the rows?

> The EXPLAIN ANALYSE still shows the same as below, but the table has
344,000
> recs of which only 22636 are clientnum =3D 'SAKS'

That sounds like it's about the borderline between using an index and=20
not (depending on cache-size, disk speeds etc).

> I am still doing a seq search (this applies to the view question where =
if
it
> is a small result set it used a index search but on a larger return =
set it
> did a seq search) in my view, but with the adjustments to the kernel I =
get
a
> result in 140 secs (MSSQL was 135 secs).

If you want to check whether the index would help, try issuing the=20
following before running your query:
   SET ENABLE_SEQSCAN=3DFALSE;
This will force PG to use any index it can regardless of whether it=20
thinks it will help.

> This is not production, I am still very worried that I have to do all =
this
> tweeking to use this, MSSQL worked out of the box as it does (not =
saying
its
> great, but I never had to adjust a kernel setting etc). Since we =
cannot
> afford the 70,000 dollars they want to license it I am not implying I =
can
> use MSSQL, but I could look at other DB's like MYSQL, or Firebird, =
etc.

I'm a little curious what kernel settings you are changing on Windows. I =

wasn't aware there was much to be done there.

I'm afraid you do have to change half a dozen settings in=20
postgresql.conf to match your workload, but PG runs on a much wider=20
range of machines than MSSQL so it's difficult to come up with a=20
"reasonable" default. Takes me about 5 minutes when I setup an=20
installation to make sure the figures are reasonable (rather than the=20
best they can be).

> I have a lot of time now (two weeks) in this conversion and do not =
wish to
> give up, I will see if I can learn what is needed to get the maximum
> performance. I have seen much information available and this list has =
been
a
> huge resource. I really appreciate all the help.

--
   Richard Huxton
   Archonet Ltd




From gsstark@mit.edu Wed Jan  26 16:20:37 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 DD7963A5C51
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 16:20:37 +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 38342-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 16:20:28 +0000 (GMT)
Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162])
	by svr1.postgresql.org (Postfix) with ESMTP id E06F63A5B3E
	for <pgsql-sql@postgresql.org>; Wed, 26 Jan 2005 16:20:26 +0000 (GMT)
Received: from localhost ([127.0.0.1] helo=stark.xeocode.com)
	by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian))
	id 1Ctpty-0007DI-00; Wed, 26 Jan 2005 11:20:18 -0500
To: "Joel Fradkin" <jfradkin@wazagua.com>
Cc: <gsstark@mit.edu>, <pgsql-sql@postgresql.org>
Subject: Re: same question little different test MSSQL vrs Postgres
References: <000401c503aa$f8ee5620$797ba8c0@jfradkin>
In-Reply-To: <000401c503aa$f8ee5620$797ba8c0@jfradkin>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 26 Jan 2005 11:20:18 -0500
Message-ID: <873bwo17st.fsf@stark.xeocode.com>
Lines: 17
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.057 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200501/262
X-Sequence-Number: 20050

"Joel Fradkin" <jfradkin@wazagua.com> writes:

> QUERY PLAN
> "Seq Scan on tblcase  (cost=0.00..30066.21 rows=37401 width=996) (actual
> time=0.344..962.260 rows=22636 loops=1)"
> "  Filter: ((clientnum)::text = 'SAKS'::text)"
> "Total runtime: 1034.434 ms"

Well that says it only took 1s. So it seems this is highly dependent on
whether the data is in cache. Perhaps it was in cache on MSSQL when you
profiled it there and not on postgres?

You could put an index on clientnum, but if the data is usually in cache like
this it might not even be necessary.

-- 
greg




From dev@archonet.com Wed Jan  26 16:27:37 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 7BC013A5BD8
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 16:27:37 +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 39554-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 16:27:34 +0000 (GMT)
Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net
	[194.217.242.85])
	by svr1.postgresql.org (Postfix) with ESMTP id 0DC723A5BC0
	for <pgsql-sql@postgresql.org>; Wed, 26 Jan 2005 16:27:33 +0000 (GMT)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-35.mail.demon.net with esmtp (Exim 4.42)
	id 1CtpzZ-00091f-Ge; Wed, 26 Jan 2005 16:26:05 +0000
Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id D06581672E; Wed, 26 Jan 2005 16:27:28 +0000 (GMT)
Message-ID: <41F7C4F1.2050500@archonet.com>
Date: Wed, 26 Jan 2005 16:27:29 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Joel Fradkin <jfradkin@wazagua.com>
Cc: gsstark@mit.edu, pgsql-sql@postgresql.org
Subject: Re: same question little different test MSSQL vrs Postgres
References: <000001c503c0$792d8e40$797ba8c0@jfradkin>
In-Reply-To: <000001c503c0$792d8e40$797ba8c0@jfradkin>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.07 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200501/263
X-Sequence-Number: 20051

Joel Fradkin wrote:
> The postgres is running on Linux Fedora core 3 (production will be redhat on
> Dell 4 proc 8 gig box).
> 
> My client pgadminIII is running on XP.
> 
> Sorry I was not clearer on this.

Ah! you're the gent who had the problems with SE-Linux on Fedora 3. 
Sorry - should have made the connection, but there's so much traffic on 
the lists it's easy to miss.

> I am playing with the settings now, I got it to return in 100 secs (the view
> that is that took 135 on MSSQL). My testing is using identical Dell desktops
> for the MSSQL and the Linux, with a third machine for the clients.
> 
> I do not mind getting up to speed on the proper setting to optimize the
> hardware, I am worried that as production environment can be somewhat
> dynamic that I will have issues getting a optimized environment and that it
> will work for our needs. My whole reason for being here is that our duel
> proc production MSSQL server is just no longer keeping up with the demand,
> so it is important that whatever I implement is going to up to the
> challenge.

You might want to look at the overall design of the database at some 
point too. Also, don't forget the compromises you made when designing 
for MSSQL might not be useful (or even harmful) with PG.

 > I am still convinced Postgres was the correct choice, especially
> with all the guidance I have been able to get here. 100 seconds will be fine
> compared to the 135 of MSSQL, I just was getting worse responses before
> adjusting. At the moment I think I went too far as I see it using swap and
> going slower, but it never used much of the 756 meg (137 max was all I ever
> saw it use).

If you're on Linux then 135MB sounds like too much (for one client, far 
too much).

> I guess the swap buffers and cache are the important settings (least that
> seems to be what is affecting the memory). Not sure exactly what would cause
> it to use seq vrs index, but I will try the force and see if it helps the
> speed.

Try starting with your shared-buffers at say 4000-8000 (32MB to 64MB), 
sort-mem/work-mem at 8000-32000 (8MB-32MB), random-page-cost somewhere 
between 2 and 4. Then, judge how much RAM your box is using to cache 
disk-space (free -m) and set effective-cache-size accordingly. That's it 
- you may want to play around with the figures slightly, but pick the 
lowest numbers above and restart PG and it'll run OK.

--
   Richard Huxton
   Archonet Ltd



From jfradkin@wazagua.com Wed Jan  26 16:28:33 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 E34D73A5BC0
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 16:28:33 +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 38978-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 16:28:29 +0000 (GMT)
Received: from out001.verizon.net (out001pub.verizon.net [206.46.170.140])
	by svr1.postgresql.org (Postfix) with ESMTP id 020573A5C61
	for <pgsql-sql@postgresql.org>; Wed, 26 Jan 2005 16:28:28 +0000 (GMT)
Received: from jfradkin ([66.15.127.235]) by out001.verizon.net
	(InterMail vM.5.01.06.06 201-253-122-130-106-20030910) with ESMTP
	id <20050126162827.EJPF28025.out001.verizon.net@jfradkin>;
	Wed, 26 Jan 2005 10:28:27 -0600
From: "Joel Fradkin" <jfradkin@wazagua.com>
To: "'Richard Huxton'" <dev@archonet.com>
Cc: <gsstark@mit.edu>, <pgsql-sql@postgresql.org>, <ac@wazagua.com>,
	"Steve Goldsmith" <SGoldsmith@fcci-group.com>
Subject: Re: same question little different test MSSQL vrs Postgres
Date: Wed, 26 Jan 2005 11:31:32 -0500
Message-ID: <000001c503c4$816b1880$797ba8c0@jfradkin>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <41F7B54C.5020204@archonet.com>
X-Authentication-Info: Submitted using SMTP AUTH at out001.verizon.net from
	[66.15.127.235] at Wed, 26 Jan 2005 10:28:23 -0600
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=3.078 tagged_above=0 required=5 tests=AWL,
	MSGID_DOLLARS
X-Spam-Level: ***
X-Archive-Number: 200501/264
X-Sequence-Number: 20052

I tried the SET ENABLE_SEQSCAN=3DFALSE;
And the result took 29 secs instead of 117.

After playing around with the cache and buffers etc I see I am no longer
doing any swapping (not sure how I got the 100 sec response might have =
been
shared buffers set higher, been goofing around with it all morning).

My worry here is it should obviously use an index scan so something is =
not
setup correctly yet. I don't want to second guess the analyzer (or is =
this a
normal thing?)

Least it is blowing the doors off MSSQL (which is what I touted to my =
boss
and was pretty upset when I got no result last night).

The 117 was before I forced the seq off so even doing a seq I am getting
results now that are better then MSSQL.

Joel Fradkin
=20
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305
=20
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.
=20

=20

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]=20
Sent: Wednesday, January 26, 2005 10:21 AM
To: Joel Fradkin
Cc: gsstark@mit.edu; pgsql-sql@postgresql.org
Subject: Re: [SQL] same question little different test MSSQL vrs =
Postgres

Joel Fradkin wrote:
> Well last evening (did not try it this morning) it was taking the =
extra
> time.
>=20
> I have made some adjustments to the config file per a few web sites =
that
you
> all recommended my looking at.

The crucial one I'd say is the performance guide at:
   http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
The first half-dozen settings are the crucial ones.

> It is now using 137 of 756 meg avail.
> it is now taking 8 secs to return 22,000 rows (using pgadminIII in a =
sql
> edit window).

That might be too much RAM. Don't forget PG likes to work with your=20
operating-system (unlike many other DBs). Make sure Windows is using=20
enough RAM to cache diskspace.
I'm curious as to how this takes 8secs whereas you had 1 second earlier. =

Are you sure some of this isn't pgadmin's overhead to display the rows?

> The EXPLAIN ANALYSE still shows the same as below, but the table has
344,000
> recs of which only 22636 are clientnum =3D 'SAKS'

That sounds like it's about the borderline between using an index and=20
not (depending on cache-size, disk speeds etc).

> I am still doing a seq search (this applies to the view question where =
if
it
> is a small result set it used a index search but on a larger return =
set it
> did a seq search) in my view, but with the adjustments to the kernel I =
get
a
> result in 140 secs (MSSQL was 135 secs).

If you want to check whether the index would help, try issuing the=20
following before running your query:
   SET ENABLE_SEQSCAN=3DFALSE;
This will force PG to use any index it can regardless of whether it=20
thinks it will help.

> This is not production, I am still very worried that I have to do all =
this
> tweeking to use this, MSSQL worked out of the box as it does (not =
saying
its
> great, but I never had to adjust a kernel setting etc). Since we =
cannot
> afford the 70,000 dollars they want to license it I am not implying I =
can
> use MSSQL, but I could look at other DB's like MYSQL, or Firebird, =
etc.

I'm a little curious what kernel settings you are changing on Windows. I =

wasn't aware there was much to be done there.

I'm afraid you do have to change half a dozen settings in=20
postgresql.conf to match your workload, but PG runs on a much wider=20
range of machines than MSSQL so it's difficult to come up with a=20
"reasonable" default. Takes me about 5 minutes when I setup an=20
installation to make sure the figures are reasonable (rather than the=20
best they can be).

> I have a lot of time now (two weeks) in this conversion and do not =
wish to
> give up, I will see if I can learn what is needed to get the maximum
> performance. I have seen much information available and this list has =
been
a
> huge resource. I really appreciate all the help.

--
   Richard Huxton
   Archonet Ltd




From gsstark@mit.edu Wed Jan  26 16:50:21 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 E5D073A5C8A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 16:50:21 +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 42354-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 16:50:17 +0000 (GMT)
Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162])
	by svr1.postgresql.org (Postfix) with ESMTP id 1B70A3A5C96
	for <pgsql-sql@postgresql.org>; Wed, 26 Jan 2005 16:50:16 +0000 (GMT)
Received: from localhost ([127.0.0.1] helo=stark.xeocode.com)
	by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian))
	id 1CtqMp-0007vN-00; Wed, 26 Jan 2005 11:50:07 -0500
To: "Joel Fradkin" <jfradkin@wazagua.com>
Cc: "'Richard Huxton'" <dev@archonet.com>, <gsstark@mit.edu>,
	<pgsql-sql@postgresql.org>, <ac@wazagua.com>,
	"Steve Goldsmith" <SGoldsmith@fcci-group.com>
Subject: Re: same question little different test MSSQL vrs Postgres
References: <000001c503c4$816b1880$797ba8c0@jfradkin>
In-Reply-To: <000001c503c4$816b1880$797ba8c0@jfradkin>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 26 Jan 2005 11:50:06 -0500
Message-ID: <87r7k8yw1t.fsf@stark.xeocode.com>
Lines: 33
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.057 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200501/265
X-Sequence-Number: 20053

"Joel Fradkin" <jfradkin@wazagua.com> writes:

> I tried the SET ENABLE_SEQSCAN=FALSE;
> And the result took 29 secs instead of 117.
> 
> After playing around with the cache and buffers etc I see I am no longer
> doing any swapping (not sure how I got the 100 sec response might have been
> shared buffers set higher, been goofing around with it all morning).

If it's swapping you're definitely going to get bad results. You really want
the *majority* of RAM left free for the OS to cache disk data.

> My worry here is it should obviously use an index scan so something is not
> setup correctly yet. I don't want to second guess the analyzer (or is this a
> normal thing?)

No that's not obvious. 22k out of 344k is a selectivity of 6.6% which is
probably about borderline. The optimizer is estimating even worse at 10.9%
which isn't far off but puts it well out of the range for an index scan.

If you really want to get postgres using an index scan you'll have to a)
improve the estimate using "alter table tblcase alter column clientnum set
statistics" to raise the statistics target for that column and reanalyze. 

And b) lower random_page_cost. random_page_cost tells postgres how much slower
indexes are than table scans and at the default setting it accurately
represents most disk hardware. If your database fits within RAM and is often
cached then you might have to lower it to model that fact. But you shouldn't
do it based on a single query like this.


-- 
greg




From jfradkin@wazagua.com Wed Jan  26 17:01: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 B5A2B3A5CA4
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 17:01: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 43291-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 17:01:03 +0000 (GMT)
Received: from out001.verizon.net (out001pub.verizon.net [206.46.170.140])
	by svr1.postgresql.org (Postfix) with ESMTP id 957543A5C72
	for <pgsql-sql@postgresql.org>; Wed, 26 Jan 2005 17:01:02 +0000 (GMT)
Received: from jfradkin ([66.15.127.235]) by out001.verizon.net
	(InterMail vM.5.01.06.06 201-253-122-130-106-20030910) with ESMTP
	id <20050126170059.EXBL28025.out001.verizon.net@jfradkin>;
	Wed, 26 Jan 2005 11:00:59 -0600
From: "Joel Fradkin" <jfradkin@wazagua.com>
To: <gsstark@mit.edu>
Cc: "'Richard Huxton'" <dev@archonet.com>,
	<pgsql-sql@postgresql.org>, <ac@wazagua.com>,
	"'Steve Goldsmith'" <SGoldsmith@fcci-group.com>
Subject: Re: same question little different test MSSQL vrs Postgres
Date: Wed, 26 Jan 2005 12:04:07 -0500
Message-ID: <000001c503c9$0d2e38d0$797ba8c0@jfradkin>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <87r7k8yw1t.fsf@stark.xeocode.com>
X-Authentication-Info: Submitted using SMTP AUTH at out001.verizon.net from
	[66.15.127.235] at Wed, 26 Jan 2005 11:00:59 -0600
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=3.078 tagged_above=0 required=5 tests=AWL,
	MSGID_DOLLARS
X-Spam-Level: ***
X-Archive-Number: 200501/266
X-Sequence-Number: 20054

Thank you I will look at that info.
I did do an EXPLAIN ANALYSE on the view and could see it was doing the =
seq
scan on 3 fields, so I did an index for the three fields and it then =
chose
an index scan and ran in 27 seconds.

I also did adjust my defaults to much smaller numbers on shared buffers =
(per
the tidbits page recommendation like 8 meg for my memory size). I looked =
at
http://www.desknow.com/kb/idx/0/061/article/ which recommended doing a
vacuum verbose to determine the exact max_fsm_pages and I set the cache =
to
use 25% of my available memory per the recommendation on tid bits.

Joel Fradkin
=20
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305
=20
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.
=20

=20

-----Original Message-----
From: gsstark@mit.edu [mailto:gsstark@mit.edu]=20
Sent: Wednesday, January 26, 2005 11:50 AM
To: Joel Fradkin
Cc: 'Richard Huxton'; gsstark@mit.edu; pgsql-sql@postgresql.org;
ac@wazagua.com; Steve Goldsmith
Subject: Re: [SQL] same question little different test MSSQL vrs =
Postgres

"Joel Fradkin" <jfradkin@wazagua.com> writes:

> I tried the SET ENABLE_SEQSCAN=3DFALSE;
> And the result took 29 secs instead of 117.
>=20
> After playing around with the cache and buffers etc I see I am no =
longer
> doing any swapping (not sure how I got the 100 sec response might have
been
> shared buffers set higher, been goofing around with it all morning).

If it's swapping you're definitely going to get bad results. You really =
want
the *majority* of RAM left free for the OS to cache disk data.

> My worry here is it should obviously use an index scan so something is =
not
> setup correctly yet. I don't want to second guess the analyzer (or is =
this
a
> normal thing?)

No that's not obvious. 22k out of 344k is a selectivity of 6.6% which is
probably about borderline. The optimizer is estimating even worse at =
10.9%
which isn't far off but puts it well out of the range for an index scan.

If you really want to get postgres using an index scan you'll have to a)
improve the estimate using "alter table tblcase alter column clientnum =
set
statistics" to raise the statistics target for that column and =
reanalyze.=20

And b) lower random_page_cost. random_page_cost tells postgres how much
slower
indexes are than table scans and at the default setting it accurately
represents most disk hardware. If your database fits within RAM and is =
often
cached then you might have to lower it to model that fact. But you =
shouldn't
do it based on a single query like this.


--=20
greg




From dev@archonet.com Wed Jan  26 17:39:33 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 0AE5C3A5C8F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 17:39:33 +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 48570-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 17:39:26 +0000 (GMT)
Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net
	[194.217.242.88])
	by svr1.postgresql.org (Postfix) with ESMTP id B849B3A5CAA
	for <pgsql-sql@postgresql.org>; Wed, 26 Jan 2005 17:39:25 +0000 (GMT)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-30.mail.demon.net with esmtp (Exim 4.42)
	id 1Ctr8U-0004Sh-0b; Wed, 26 Jan 2005 17:39:23 +0000
Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id EF039166E6; Wed, 26 Jan 2005 17:39:15 +0000 (GMT)
Message-ID: <41F7D5BC.7050000@archonet.com>
Date: Wed, 26 Jan 2005 17:39:08 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Joel Fradkin <jfradkin@wazagua.com>
Cc: gsstark@mit.edu, pgsql-sql@postgresql.org, ac@wazagua.com,
	'Steve Goldsmith' <SGoldsmith@fcci-group.com>
Subject: Re: same question little different test MSSQL vrs Postgres
References: <000001c503c9$0d2e38d0$797ba8c0@jfradkin>
In-Reply-To: <000001c503c9$0d2e38d0$797ba8c0@jfradkin>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.07 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200501/268
X-Sequence-Number: 20056

Joel Fradkin wrote:
> Thank you I will look at that info.
> I did do an EXPLAIN ANALYSE on the view and could see it was doing the seq
> scan on 3 fields, so I did an index for the three fields and it then chose
> an index scan and ran in 27 seconds.
> 
> I also did adjust my defaults to much smaller numbers on shared buffers (per
> the tidbits page recommendation like 8 meg for my memory size). I looked at
> http://www.desknow.com/kb/idx/0/061/article/ which recommended doing a
> vacuum verbose to determine the exact max_fsm_pages and I set the cache to
> use 25% of my available memory per the recommendation on tid bits.

Note that the effective_cache_size (if I've spelt it right) just tells 
PG what your cache size is. You should set it based on what "free" tells 
you about your system's use of memory.

-- 
   Richard Huxton
   Archonet Ltd



From dennis@illusions.com Wed Jan  26 20:56:29 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 05F393A5451
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 20:56:29 +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 56893-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 20:56:19 +0000 (GMT)
Received: from illusions.com (unknown [69.28.129.146])
	by svr1.postgresql.org (Postfix) with SMTP id AB63C3A3CBB
	for <pgsql-sql@postgresql.org>; Wed, 26 Jan 2005 20:56:19 +0000 (GMT)
Received: (qmail 26640 invoked from network); 26 Jan 2005 20:56:56 -0000
Received: from localhost (HELO ?127.0.0.1?) (127.0.0.1)
	by illusions.com with SMTP; 26 Jan 2005 20:56:56 -0000
Message-ID: <41F8043C.6080101@illusions.com>
Date: Wed, 26 Jan 2005 13:57:32 -0700
From: Dennis Sacks <dennis@illusions.com>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Bruno Wolff III <bruno@wolff.to>
Cc: Joel Fradkin <jfradkin@wazagua.com>, pgsql-sql@postgresql.org
Subject: Re: same question little different test MSSQL vrs Postgres
References: <000901c5035a$3b619cc0$797ba8c0@jfradkin>
	<41F71AB4.5030208@illusions.com> <20050126210450.GA26734@wolff.to>
In-Reply-To: <20050126210450.GA26734@wolff.to>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.329 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200501/272
X-Sequence-Number: 20060

Bruno Wolff III wrote:

>On Tue, Jan 25, 2005 at 21:21:08 -0700,
>  Dennis Sacks <dennis@illusions.com> wrote:
>  
>
>>One of the things you'll want to do regularly is run a "vacuum analyze". 
>>You can read up on this in the postgresql docs. This is essential to the 
>>indexes being used properly. At a bare minimum, after you import a large 
>>amount of data, you'll want to run vacuum analyze.
>>    
>>
>
>Note that there is no need to vacuum after inserts (only updates and deletes),
>so you can just do an analyze in that case.
>  
>
Good point! Analyze after bulk inserts, vacuum analyze after 
updates/deletes and inserts. :)

Dennis Sacks



From bruno@wolff.to Wed Jan  26 20:50:38 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 6184C3A57A2
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 20:50:38 +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 55961-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 20:50:34 +0000 (GMT)
Received: from wolff.to (wolff.to [66.93.249.74])
	by svr1.postgresql.org (Postfix) with SMTP id 374B03A573F
	for <pgsql-sql@postgresql.org>; Wed, 26 Jan 2005 20:50:34 +0000 (GMT)
Received: (qmail 28954 invoked by uid 500); 26 Jan 2005 21:04:50 -0000
Date: Wed, 26 Jan 2005 15:04:50 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: Dennis Sacks <dennis@illusions.com>
Cc: Joel Fradkin <jfradkin@wazagua.com>, pgsql-sql@postgresql.org
Subject: Re: same question little different test MSSQL vrs Postgres
Message-ID: <20050126210450.GA26734@wolff.to>
Mail-Followup-To: Dennis Sacks <dennis@illusions.com>,
	Joel Fradkin <jfradkin@wazagua.com>, pgsql-sql@postgresql.org
References: <000901c5035a$3b619cc0$797ba8c0@jfradkin>
	<41F71AB4.5030208@illusions.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <41F71AB4.5030208@illusions.com>
User-Agent: Mutt/1.5.6i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200501/271
X-Sequence-Number: 20059

On Tue, Jan 25, 2005 at 21:21:08 -0700,
  Dennis Sacks <dennis@illusions.com> wrote:
> 
> One of the things you'll want to do regularly is run a "vacuum analyze". 
> You can read up on this in the postgresql docs. This is essential to the 
> indexes being used properly. At a bare minimum, after you import a large 
> amount of data, you'll want to run vacuum analyze.

Note that there is no need to vacuum after inserts (only updates and deletes),
so you can just do an analyze in that case.



From ahammond@ca.afilias.info Wed Jan  26 22:15:49 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 5C7433A5AD6
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 22:15:49 +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 65039-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Jan 2005 22:15:43 +0000 (GMT)
Received: from mail.libertyrms.com (unknown [207.219.45.62])
	by svr1.postgresql.org (Postfix) with ESMTP id 73F5D3A5ADE
	for <pgsql-sql@postgresql.org>; Wed, 26 Jan 2005 22:15:44 +0000 (GMT)
Received: from dba4.int.libertyrms.com ([10.1.3.13] ident=ahammond)
	by mail.libertyrms.com with esmtp (Exim 4.22)
	id 1CtvRw-0000zD-95; Wed, 26 Jan 2005 17:15:44 -0500
Message-ID: <41F8168F.2010208@ca.afilias.info>
Date: Wed, 26 Jan 2005 17:15:43 -0500
From: Andrew Hammond <ahammond@ca.afilias.info>
Organization: Afilias Canada Corp.
User-Agent: Mozilla Thunderbird 0.9 (X11/20041124)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Joel Fradkin <jfradkin@wazagua.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: same question little different test MSSQL vrs Postgres
References: <000001c503c0$792d8e40$797ba8c0@jfradkin>
In-Reply-To: <000001c503c0$792d8e40$797ba8c0@jfradkin>
X-Enigmail-Version: 0.89.0.0
X-Enigmail-Supports: pgp-inline, pgp-mime
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-SA-Exim-Mail-From: ahammond@ca.afilias.info
X-SA-Exim-Scanned: No; SAEximRunCond expanded to false
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.52 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO, INFO_TLD
X-Spam-Level: 
X-Archive-Number: 200501/274
X-Sequence-Number: 20062

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

We've done some pretty extensive benchmarking and load testing on a
couple of platforms including the Xeon and Opteron. You may have already
bought that Dell box, but I'll say it anyway. Xeon quad processors are a
terrible platform for postgres. Trying to use more than 4GB of memory on
a 32 bit machine is a waste of money.

If you want performance, get a quad Opteron with the same amount of
memory. I guarantee you'll see at least an order of magnitude
performance improvement and substantially more under highly concurrent
loads. If you decide to go this way, HP sells a very nice box. I also
strongly recommend you investigate SuSE instead of RedHat. Fedora core
is good technology, but SuSE offers equally good technology with better
support.

Also make sure that your SCSI HBA is actually using the 64 bit PCI bus.
There are cards out there which plug into 64 bit PCI but only actually
address 32 bits (Qlogic's QLA2340 / 2342 for example).

You make no mention of the disk subsystem you plan to use. This is most
critical part of your system. Database performance is almost always
bound by IO. Usually disk IO. Briefly, put PGDATA on the widest RAID 10
array of disks you can manage. It's not worth spending the extra money
to get 15kRPM disks for this. The size of the disks involved is pretty
much irrelevant, only the number of them matters. Put the WAL files on a
dedicated RAID 1 pair of 15kRPM disks. Put the postgres log files (or
syslog) on a seperate filesystem.

- --
Andrew Hammond    416-673-4138    ahammond@ca.afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A


Joel Fradkin wrote:
| The postgres is running on Linux Fedora core 3 (production will be
redhat on
| Dell 4 proc 8 gig box).
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFB+BaPgfzn5SevSpoRAgirAKDBbedScL3leQVidZjmsGmxoph8wQCgvhoW
2ZznEkxOMA3btZEBdzHd8TU=
=eg7h
-----END PGP SIGNATURE-----



From jfradkin@wazagua.com Thu Jan  27 14:08:05 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 0C9E83A5EF0
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 27 Jan 2005 14:08:05 +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 63678-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 27 Jan 2005 14:07:53 +0000 (GMT)
Received: from out006.verizon.net (out006pub.verizon.net [206.46.170.106])
	by svr1.postgresql.org (Postfix) with ESMTP id A547B3A5EB8
	for <pgsql-sql@postgresql.org>; Thu, 27 Jan 2005 14:07:55 +0000 (GMT)
Received: from jfradkin ([66.15.127.235]) by out006.verizon.net
	(InterMail vM.5.01.06.06 201-253-122-130-106-20030910) with ESMTP
	id <20050127140755.WJDZ7873.out006.verizon.net@jfradkin>;
	Thu, 27 Jan 2005 08:07:55 -0600
From: "Joel Fradkin" <jfradkin@wazagua.com>
To: "'Andrew Hammond'" <ahammond@ca.afilias.info>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: same question little different test MSSQL vrs Postgres
Date: Thu, 27 Jan 2005 09:11:05 -0500
Message-ID: <000001c5047a$0b811fa0$797ba8c0@jfradkin>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <41F8168F.2010208@ca.afilias.info>
X-Authentication-Info: Submitted using SMTP AUTH at out006.verizon.net from
	[66.15.127.235] at Thu, 27 Jan 2005 08:07:55 -0600
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=3.318 tagged_above=0 required=5 tests=AWL, INFO_TLD,
	MSGID_DOLLARS
X-Spam-Level: ***
X-Archive-Number: 200501/290
X-Sequence-Number: 20078


Now you tell me.
We had a fellow working here kept screaming AMD, but I am a very =
paranoid
person and was not aware Linux and Postgres have been running on the new
chips. I don't like to be a first. We have bought the Dell and I cant =
tell
you if the controller uses 64bits, I just got what they had on their =
page
for their 4 proc rack mount. Part of my reason for going Dell was we =
already
have Dell equipment and the Linux support is offered from Dell as well, =
so I
have one vendor to worry about. Being a developer and Director of IT I =
want
the fastest best, but sometimes I flavor my opinions with safest and
easiest. The RPM delivery is something I understand (it's easy). What is =
SU
like? Is there any difference in the performance between the two =
Vendors?
I am sure we will be buying more Postgres servers in the near future =
(One of
the big reasons we are taking the time to convert from MSSQL was so we =
could
afford to invest in more servers MSSQL was cost prohibitive even for one
server). As easy as Fedura was I still had several issues getting to =
where I
am now, so I am paranoid of something that requires even more knowledge =
to
pull it off; that being said I never minded getting into the details to =
get
a better end result. As you said we have made the investment in the Dell
(25K). I feel pretty stupid if it is as you say a waste of money to get =
8
gigs on this platform as I just made that same mistake a year ago when I
bought the 2 processor boxes with standard addition MSSQL and 4 gigs (It
only uses 2 gig). I was under the impression this machine would utilize =
all
8 gigs. Are you saying only 4 will be available for caching etc, or just =
the
chipset cant deal with numbers 8 gig and will be slower to access them? =
If
it is the later then I would imagine it would still outperform a similar =
box
with 4 gig assuming my demand on cache is larger then 4 gig.

Just to confirm you have these quad Opteron (I am assuming a 4 processor
config?) in a production environment running su and postgres with =
hardware
support from HP and software from su? You indicate three separate =
physical
drives will give best performance (one for data 10K speeds, one for =
admin,
one for wall 15 speed)? I am not too sophisticated at knowing how to =
irder
this arrangement and set it up in Linux, any chance you could detail (1 =
card
with 2 channels 4 10k drives on one channel, 2 15k drives on the second, =
do
I need another channel and drive(s) for admin files?), drive layout when
installing config in postgres to utilize?=20

If need be maybe we can get you to do this as a consultant as I do
understand how important the hardware and the proper config is.  I found =
out
too late with MSSQL that I should have used two seprate drive arrays, =
one
for data, one for log (this would have required the split back plane).

So not to plug a specific vendor but if you have production environment
example with real equipment suggestions I would be very appreciative.

I know that's a lot to ask so if you don't have time that's cool, thanks =
so
much for bringing this up so that my next purchase I will seriously look =
at
quad Opteron technology if it is a tried and true solution for this OS =
and
Postgres.

Joel Fradkin
=20


-----Original Message-----
From: Andrew Hammond [mailto:ahammond@ca.afilias.info]=20
Sent: Wednesday, January 26, 2005 5:16 PM
To: Joel Fradkin
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] same question little different test MSSQL vrs =
Postgres

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

We've done some pretty extensive benchmarking and load testing on a
couple of platforms including the Xeon and Opteron. You may have already
bought that Dell box, but I'll say it anyway. Xeon quad processors are a
terrible platform for postgres. Trying to use more than 4GB of memory on
a 32 bit machine is a waste of money.

If you want performance, get a quad Opteron with the same amount of
memory. I guarantee you'll see at least an order of magnitude
performance improvement and substantially more under highly concurrent
loads. If you decide to go this way, HP sells a very nice box. I also
strongly recommend you investigate SuSE instead of RedHat. Fedora core
is good technology, but SuSE offers equally good technology with better
support.

Also make sure that your SCSI HBA is actually using the 64 bit PCI bus.
There are cards out there which plug into 64 bit PCI but only actually
address 32 bits (Qlogic's QLA2340 / 2342 for example).

You make no mention of the disk subsystem you plan to use. This is most
critical part of your system. Database performance is almost always
bound by IO. Usually disk IO. Briefly, put PGDATA on the widest RAID 10
array of disks you can manage. It's not worth spending the extra money
to get 15kRPM disks for this. The size of the disks involved is pretty
much irrelevant, only the number of them matters. Put the WAL files on a
dedicated RAID 1 pair of 15kRPM disks. Put the postgres log files (or
syslog) on a seperate filesystem.

- --
Andrew Hammond    416-673-4138    ahammond@ca.afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A


Joel Fradkin wrote:
| The postgres is running on Linux Fedora core 3 (production will be
redhat on
| Dell 4 proc 8 gig box).
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFB+BaPgfzn5SevSpoRAgirAKDBbedScL3leQVidZjmsGmxoph8wQCgvhoW
2ZznEkxOMA3btZEBdzHd8TU=3D
=3Deg7h
-----END PGP SIGNATURE-----




From mischa_Sandberg@telus.net Sat Jan  29 05:59:07 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 2A6883A6374
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 29 Jan 2005 05:59:07 +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 45629-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 29 Jan 2005 05:58:56 +0000 (GMT)
Received: from priv-edtnes56.telusplanet.net (outbound01.telus.net
	[199.185.220.220])
	by svr1.postgresql.org (Postfix) with ESMTP id 46FF33A5D3A
	for <pgsql-sql@postgresql.org>; Sat, 29 Jan 2005 05:58:55 +0000 (GMT)
Received: from localhost ([199.185.220.240]) by priv-edtnes56.telusplanet.net
	(InterMail vM.6.01.04.00 201-2131-118-20041027) with ESMTP
	id <20050129055854.MVPU10613.priv-edtnes56.telusplanet.net@localhost>
	for <pgsql-sql@postgresql.org>; Fri, 28 Jan 2005 22:58:54 -0700
Received: from 64.180.225.200 ( [64.180.225.200])
	as user a3a18850@192.168.200.1 by webmail.telus.net with HTTP;
	Fri, 28 Jan 2005 21:58:54 -0800
Message-ID: <1106978334.41fb261e28ac2@webmail.telus.net>
Date: Fri, 28 Jan 2005 21:58:54 -0800
From: Mischa <mischa_Sandberg@telus.net>
To: pgsql-sql@postgresql.org
Subject: Re: same question little different test MSSQL vrs Postgres
References: <000901c5035a$3b619cc0$797ba8c0@jfradkin>
	<41F71AB4.5030208@illusions.com> <20050126210450.GA26734@wolff.to>
	<41F8043C.6080101@illusions.com>
In-Reply-To: <41F8043C.6080101@illusions.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: Internet Messaging Program (IMP) 3.1-cvs
X-Originating-IP: 64.180.225.200
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.426 tagged_above=0 required=5
	tests=DNS_FROM_RFC_POST, FORGED_RCVD_HELO
X-Spam-Level: *
X-Archive-Number: 200502/9
X-Sequence-Number: 20120

Quoting Dennis Sacks <dennis@illusions.com>:
> Bruno Wolff III wrote:
> >On Tue, Jan 25, 2005 at 21:21:08 -0700,
> >  Dennis Sacks <dennis@illusions.com> wrote:
> >>One of the things you'll want to do regularly is run a "vacuum analyze". 
> >>You can read up on this in the postgresql docs. This is essential to the 
> >>indexes being used properly. At a bare minimum, after you import a large 
> >>amount of data, you'll want to run vacuum analyze.
> >
> Good point! Analyze after bulk inserts, vacuum analyze after 
> updates/deletes and inserts. :)

Hmmm ... in performance tests of bulk inserts into a table with six indexes, I
found that without vacuum analyze (rather than analyze), insertion slowed down
albeit something less than linearly. Table of 6M rows, about 3GB (including
index files).
This is 7.4.1 on SuSE Linux, RAID5 Xeon(sigh) 2.8GHz 4GB nothing else running.
The inserts were always done with an existing record check (LEFT JOIN ... WHERE
joinkey IS NULL).
-- 
"Dreams come true, not free."