Re: ***SPAM*** Re: same question little different test MSSQL - Mailing list pgsql-sql

I've tested in a relation of mine, with about 20 attributes, and here are the results:<br /><br /> test=# select
count(*)from gestionestareas;<br />  count<br /> --------<br />  447681<br /> (1 row)<br /><br /> test=# explain
analyzeselect * from gestionestareas where agrupable;<br />                                                         
QUERYPLAN<br />
----------------------------------------------------------------------------------------------------------------------------<br
/> Seq Scan on gestionestareas  (cost=0.00..12334.81 rows=155495 width=372) (actual time=0.603..1176.177 rows=153530
loops=1)<br/>    Filter: agrupable<br />  Total runtime: 1380.113 ms<br /> (3 rows)<br /><br /> So, doing a seq scan on
a450,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),
ldapserver, etc.<br /><br /> I don't  use pgadmin, I use psql (postgresql console client). It took 2 minutes to display
theresults 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.<br/><br /> My advice is, use EXPLAIN ANALYZE to test both servers performance. If you want to know which the
finalresults will be, you test both databases from a self programmed application (java, php, C++, etc.).<br /><br />
Hopethis helped.<br /><br /> Joel Fradkin wrote: <blockquote cite="mid000001c503b7$6ea08800$797ba8c0@jfradkin"
type="cite"><prewrap="">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 </pre><blockquote type="cite"><pre wrap="">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"   </pre></blockquote><pre wrap="">
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?
              <a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/faq">http://www.postgresql.org/docs/faq</a>
 </pre></blockquote><br />