Re: indexes

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/