Re: ***SPAM*** Re: same question little different test MSSQL - Mailing list pgsql-sql

Maybe you should tweak the cpu_index_tuple_cost parameter instead of disabling sequential scans. De default value is
0.001,you should change it to a lower value (0.0005 or something).<br /><br /> Joel Fradkin wrote: <blockquote
cite="mid000001c503c4$816b1880$797ba8c0@jfradkin"type="cite"><pre wrap="">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).

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
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305
<a class="moz-txt-link-abbreviated" href="mailto:jfradkin@wazagua.com">jfradkin@wazagua.com</a>
<a class="moz-txt-link-abbreviated" href="http://www.wazagua.com">www.wazagua.com</a>
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, IncThis email message is for the use of the intended recipient(s)
andmay
 
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: Richard Huxton [<a class="moz-txt-link-freetext" href="mailto:dev@archonet.com">mailto:dev@archonet.com</a>] 
Sent: Wednesday, January 26, 2005 10:21 AM
To: Joel Fradkin
Cc: <a class="moz-txt-link-abbreviated" href="mailto:gsstark@mit.edu">gsstark@mit.edu</a>; <a
class="moz-txt-link-abbreviated"href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>
 
Subject: Re: [SQL] same question little different test MSSQL vrs Postgres

Joel Fradkin wrote: </pre><blockquote type="cite"><pre wrap="">Well last evening (did not try it this morning) it was
takingthe extra
 
time.

I have made some adjustments to the config file per a few web sites that   </pre></blockquote><pre wrap="">you
</pre><blockquotetype="cite"><pre wrap="">all recommended my looking at.   </pre></blockquote><pre wrap="">
 
The crucial one I'd say is the performance guide at:  <a class="moz-txt-link-freetext"
href="http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php">http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php</a>
The first half-dozen settings are the crucial ones.
 </pre><blockquote type="cite"><pre wrap="">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).   </pre></blockquote><pre wrap="">
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?
 </pre><blockquote type="cite"><pre wrap="">The EXPLAIN ANALYSE still shows the same as below, but the table has
</pre></blockquote><prewrap="">344,000 </pre><blockquote type="cite"><pre wrap="">recs of which only 22636 are
clientnum= 'SAKS'   </pre></blockquote><pre wrap="">
 
That sounds like it's about the borderline between using an index and 
not (depending on cache-size, disk speeds etc).
 </pre><blockquote type="cite"><pre wrap="">I am still doing a seq search (this applies to the view question where if
</pre></blockquote><prewrap="">it </pre><blockquote type="cite"><pre wrap="">is a small result set it used a index
searchbut on a larger return set it
 
did a seq search) in my view, but with the adjustments to the kernel I get   </pre></blockquote><pre wrap="">a
</pre><blockquotetype="cite"><pre wrap="">result in 140 secs (MSSQL was 135 secs).   </pre></blockquote><pre wrap="">
 
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.
 </pre><blockquote type="cite"><pre wrap="">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   </pre></blockquote><pre wrap="">its
</pre><blockquotetype="cite"><pre wrap="">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.   </pre></blockquote><pre wrap="">
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).
 </pre><blockquote type="cite"><pre wrap="">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   </pre></blockquote><pre wrap="">a
</pre><blockquotetype="cite"><pre wrap="">huge resource. I really appreciate all the help.   </pre></blockquote><pre
wrap="">
--  Richard Huxton  Archonet Ltd


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              <a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/faq">http://www.postgresql.org/docs/faq</a>
 </pre></blockquote><br />