Re: Newbie Question: FAQ for database optimization?

On Tue, Dec 20, 2005 at 10:21:54PM +0100, Alexander Scholz wrote:
> Hi,
>
> is there a newbie's FAQ / book / link for "howto optimize databases with
> PostgreSQL"?
>
> Background: Customer has the Windows* (sorry <g>) Postgres 8.1.0
> standard installation "out of the box". A table has 2.5 mio records.
> No indizes defined, primary key (sequence) does exist. In pgAdmin
> "select count(*)" takes over 30 seconds,

That sounds about right. If you want to cache this result, there are
ways to do that, and there are approximations to the result if you're
interested in such things.

> an "update" affecting 70'000 records takes minutes...

An index on the (set of) column(s) the WHERE clause refers to would
very likely help. For example, if your update looks like:

UPDATE foo
SET bar = 555
WHERE baz = 'blurf';

You could get some mileage out of indexing the baz column. See the
docs on CREATE INDEX for the syntax.

> I am sure PostgreSQL could do better, we "just" need to tune the
> database. (I hope so at least!)

>
> What action and/or reading can you recommend? (We quickly need some
> 'wow' effects to keep the customer happy <sigh>).

There are archives of the pgsql-performance mailing list at
<http://archves.postresql.org/> for a lot of this. For things you
don't find there, you can either post here or go to
<irc://irc.freenode.net/postgresql>, where there are friendly, helpful
people, and occasionally Yours Truly.

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!