DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=j0zrUKH5aujokOZdbwT6YErAhpTw43fjhxD5Bx9jBs1WLG/7fdpvw2CTtMZ0gHhczT
	Jjv4nGpdjPNoAIgqTjuhH9Tl9m9uXmGb4P61XKQn2N9JYnMWwO4veRpS0A4QSPe+W6F/
	4jqExJCAvWpG1RSqVNFwz8/8uacCtbdOUwGVg=
Message-ID: <4B53885C.3060706@gmail.com>
Date: Mon, 18 Jan 2010 08:59:56 +1100
From: Chris <dmagick@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Seb <spluque@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: indexes
References: <87iqb3z74n.fsf@kolob.sebmags.homelinux.org>
In-Reply-To: <87iqb3z74n.fsf@kolob.sebmags.homelinux.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Seb wrote:
> Hi,
> 
> I have some views and queries that take a bit too long to return, so
> perhaps some judicious indexes might help, but I don't know much about
> how to use them.  The PostgreSQL manual has a good section on indexes,
> but I can't find guidance on (unless I missed something):
> 
> o How to decide what columns need an index?

I wrote something a little while ago about this:

http://www.designmagick.com/article/16/ (comments welcome!)

> o Should all foreign keys have an index?

Not necessarily, you might just want the db to enforce the restriction 
but not actually use the data in it. For example, keep a userid (and 
timestamp) column of the last person to update a row. You may need it to 
say "aha - this was last changed on this date and by person X", but 
you'll never generally use it.

If you never have a where clause with that column, no need to index it. 
If you're using it in a join all the time, then yes it would be better 
to index it.

> o Naming conventions?

That comes down to personal or project preference - there's no 
particular convention used anywhere.

> o Does PostgreSQL use available indexes that can be useful in any query,
>   without the user having to do anything in particular?

Yes - though just because an index is present doesn't mean postgres will 
use it, in some cases it's better for it to ignore the index altogether 
and use some other method to perform your query.

-- 
Postgresql & php tutorials
http://www.designmagick.com/