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>

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