From: Chris <dmagick@gmail.com>
To: Seb <spluque@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: indexes
Date: Mon, 18 Jan 2010 08:59:56 +1100
Message-ID: <4B53885C.3060706@gmail.com> (raw)
In-Reply-To: <87iqb3z74n.fsf@kolob.sebmags.homelinux.org>
References: <87iqb3z74n.fsf@kolob.sebmags.homelinux.org>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dmagick@gmail.com, spluque@gmail.com
Subject: Re: indexes
In-Reply-To: <4B53885C.3060706@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox