Date: Mon, 28 Apr 2008 20:23:12 +0200
To: "A B" <gentosaker@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: Where do a novice do to make it run faster?
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <dbbf25900804280856m41c67d96heaa616d39d6c44e0@mail.gmail.com>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.uab2oycdcigqcu@apollo13.peufeu.com>
In-Reply-To: <dbbf25900804280856m41c67d96heaa616d39d6c44e0@mail.gmail.com>
User-Agent: Opera Mail/9.24 (Linux)


	You got the order slightly wrong I guess.

> 1) hardware

	Would only come first if your RAM is really too small, or you use RAID5=
  =

on write-heavy tables, or what limits you is transaction fsync (hint :  =

8.3).
	Adding RAM is cheap.

> 2) rewriting my queries and table structures

	This should really come first.
	Log expensive queries. Note that an expensive query can be a slow query=
,  =

or be a rather fast query that you execute lots of times, or a very simp=
le  =

and fast query that you execute really really too often.

	Now ask yourself :
* What is this query supposed to do ?

* Do I need this query ?

	Example :
	You put your sessions in a database ?
	=3D> Perhaps put them in the good old filesystem ?

	Your PHP is loading lots of configuration from the database for every  =

page.
	=3D> Cache it, generate some PHP code once and include it, put it in th=
e  =

session if it depends on the user, but don't reload the thing on each pa=
ge  =

!

	This feature is useless
	=3D> Do you really need to display a birthday cake on your forum for th=
ose  =

users who have their birthday today ?

	UPDATEs...
	=3D> Do you really need to update the last time a user was online every=
  =

time ? What about updating it every 5 minutes instead ?

* Is this query inside a loop ?
	=3D> Use JOIN.

* Do I need all the rows from this query ?

	Example :
You use pagination and perform the same query changing LIMIT/OFFSET ?
=3D> Perform the query once, retrieve the first N pages of result, cache=
 it  =

in the session or in a table.

* You have a website ?
=3D> Use lighttpd and fastcgi

* Do I need all the columns from this query ?

* Do I suffer from locking ?

	etc.


Now you should see some easy targets.
For the queries that are slow, use EXPLAIN ANALYZE.
Question your schema.
etc.