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@xxxxxxxxxx http://fetter.org/ phone: +1 415 235 3778 Remember to vote!
