Re: ***SPAM*** Re: same question little different test MSSQL

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).

Joel Fradkin wrote:

>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
>
>jfradkin(at)wazagua(dot)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: Richard Huxton [mailto:dev(at)archonet(dot)com]
>Sent: Wednesday, January 26, 2005 10:21 AM
>To: Joel Fradkin
>Cc: gsstark(at)mit(dot)edu; pgsql-sql(at)postgresql(dot)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.
>>
>>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
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>