Joel Fradkin wrote:
> 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 6: Have you searched our list archives?

http://archives.postgresql.org

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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/Gener...bits/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 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)