Bill wrote:
> 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?
This is a very broad question. Optimizing your SQL to run fast as on
any other database is something of an art form. This is a very broad
topic that could fill a book. For example, a common performance killer
is not having enough sort memory for large ordered result sets.
A critical skill is being able to figure out if the planner is
optimizing your queries badly. Knowing this is a mixture of observation
and intuition that comes with experience. The absolute best case
performance of a query is roughly defined by the data that is looked at
to generate the result set and the size of the result set itself when
the query is pulling data from the cache. The cache problem is
compromisable by throwing more money at the problem but a poorly planned
query will run slowly on any hardware.
I would suggest isolating particular problems and posting them to the
list. (explain analyze works wonders).
Merlin