Re: postgresql and openmosix migration - Mailing list pgsql-performance

Hi Bill, I am more often in the "needing help" category than the "giving
help" when it comes to advise about using postgresql.  I have found it to be
an extremely powerful tool and by far the best performance/price for my
work.

I think you will get some excellent answers and help to your performance
questions if you send the list details about specific queries that are
running too slow.  If you are willing to throw more/bigger hardware at the
problem, let people know that when you ask and they will tell you if your
bottleneck can be alleviated through more ram, disks, cpu or whatever.
Having been watching this list for some time now, I suspect most of the
performance problems can be improved using non-intuitive query or
configuration modifications (for example, replacing min()/max() as suggested
by Mr. Wolf).

The heavy hitters on the list will usually ask for an "explain analyze" of
your query.  If your query is "select * from foo", then change it to
"EXPLAIN ANALYZE select * from foo" and post the output.  It will look
something like this:
                                                QUERY PLAN

----------------------------------------------------------------------------
-------------------------------
 Seq Scan on foo  (cost=0.00..1.04 rows=4 width=44) (actual time=8.46..8.47
rows=4 loops=1)
 Total runtime: 19.63 msec
(2 rows)

I'm sure your data is confidential; mine is too.  The good news is that none
of your data is included in the query. Only technical details about what the
database is doing.

If your problem might involve the application that works with the data, give
some details about that. For example, if you're using a Java application,
let people know what driver version you use, what jvm and other related
info.  There are lurkers on this list using just about every programming
language imaginable on more platforms than you can shake a stick at (I don't
care how good you are at shaking sticks, either).

The more details you give the better help you're going to get and you'd be
amazed at the results I've seen people get with a judicious amount of
tweaking. The other day someone had a query that took hours decrease to less
than 10 minutes by using some techniques prescribed by members on the list.
Bringing 30 - 60 second queries down to 2-3 seconds is commonplace.

You seem to be ready to throw money at the problem by investing in new
hardware but I would suggest digging into the performance problems first.
Too many times we've seen people on the list say, "I've just spent $x0,000
on a new xyz and I'm still having problems with this query."  Often times
the true solution is rewriting queries, tweaking config parameters, adding
RAM and upgrading disks (in that order I believe).

As I found out even today on the SQL list, it's best to ask questions in
this form:
"I want to do this...  I've been trying this...  I'm getting this... which
is problematic because..."

The more clearly you state the abstract goal the more creative answers
you'll get with people often suggesting things you'd never considered.

I hope this helps and I hope that you achieve your goals of a well
performing application.

Matthew Nuzum        | Makers of "Elite Content Management System"
www.followers.net        | View samples of Elite CMS in action
matt@followers.net    | http://www.followers.net/portfolio/


> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-
> owner@postgresql.org] On Behalf Of Bill
> Sent: Tuesday, June 22, 2004 1:31 PM
> To: Josh Berkus
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] postgresql and openmosix migration
>
> Ok, so maybe someone on this group will have a better idea.  We have a
> database of financial information, and this has literally millions of
> entries.  I have installed indicies, but for the rather computationally
> demanding processes we like to use, like a select query to find the
> commodity with the highest monthly or annual returns, the computer
> generally
> runs unacceptably slow.  So, other than clustring, how could I achieve a
> speed increase in these complex queries?  Is this better in mysql or
> postgresql?
>
> Thanks.