pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: ***SPAM*** Re: same question little different test MSSQL
2+ messages / 1 participants
[nested] [flat]
* Re: ***SPAM*** Re: same question little different test MSSQL
@ 2005-01-26 15:55 Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 0 replies; 2+ messages in thread
From: Franco Bruno Borghesi @ 2005-01-26 15:55 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: 'Richard Huxton' <dev@archonet.com>; gsstark@mit.edu; pgsql-sql
I've tested in a relation of mine, with about 20 attributes, and here
are the results:
test=# select count(*) from gestionestareas;
count
--------
447681
(1 row)
test=# explain analyze select * from gestionestareas where agrupable;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Seq Scan on gestionestareas (cost=0.00..12334.81 rows=155495
width=372) (actual time=0.603..1176.177 rows=153530 loops=1)
Filter: agrupable
Total runtime: 1380.113 ms
(3 rows)
So, doing a seq scan on a 450,000 rows table and fetching 150,000 rows
takes only 1.3 secs. This a 900Mhz PIII, 1GB mem (133 Mhz), 7200RPM ide
disk, running freebsd. This machine is also a webserver (apache &
tomcat), mail server, file server (smb & nfs), ldap server, etc.
I don't use pgadmin, I use psql (postgresql console client). It took 2
minutes to display the results of the above query (without the EXPLAIN
ANALIZE).... this makes me think, couldn't be the problem that pgadmin,
psql, etc. takes too much time to display all the rows? It seems a
client software problem, not a server problem.
My advice is, use EXPLAIN ANALYZE to test both servers performance. If
you want to know which the final results will be, you test both
databases from a self programmed application (java, php, C++, etc.).
Hope this helped.
Joel Fradkin wrote:
>Well last evening (did not try it this morning) it was taking the extra
>time.
>
>I have made some adjustmenNots 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 = '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
>
>Wazagua, Inc.
>2520 Trailmate Dr
>Sarasota, Florida 34243
>Tel. 941-753-7111 ext 305
>
>
>
>>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
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: ***SPAM*** Re: same question little different test MSSQL
@ 2005-01-26 17:12 Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 0 replies; 2+ messages in thread
From: Franco Bruno Borghesi @ 2005-01-26 17:12 UTC (permalink / raw)
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>
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@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: Richard Huxton [mailto:dev@archonet.com]
>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.
>>
>>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
>
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-01-26 17:12 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-26 15:55 Re: ***SPAM*** Re: same question little different test MSSQL Franco Bruno Borghesi <franco@akyasociados.com.ar>
2005-01-26 17:12 Re: ***SPAM*** Re: same question little different test MSSQL Franco Bruno Borghesi <franco@akyasociados.com.ar>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox