From: | "Joel Fradkin" <jfradkin(at)wazagua(dot)com> |
---|---|
To: | "'Richard Huxton'" <dev(at)archonet(dot)com> |
Cc: | <gsstark(at)mit(dot)edu>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: same question little different test MSSQL vrs Postgres |
Date: | 2005-01-26 14:58:00 |
Message-ID: | 000001c503b7$6ea08800$797ba8c0@jfradkin |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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 = '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
From | Date | Subject | |
---|---|---|---|
Next Message | George Essig | 2005-01-26 15:01:59 | Re: Moving from Transact SQL to PL/pgsql |
Previous Message | Matteo Braidotti | 2005-01-26 14:28:18 |