Thread: indexes
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? o Should all foreign keys have an index? o Naming conventions? o Does PostgreSQL use available indexes that can be useful in any query, without the user having to do anything in particular? I'd appreciate any pointers to documents with guidance on these questions. Thanks. -- Seb
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/
On Mon, 18 Jan 2010 08:59:56 +1100, Chris <dmagick@gmail.com> wrote: >> 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. Thanks for all your pointers! Do views use the indexes in the underlying tables, whenever say a SELECT operation is called on the view? If so, indexes on views don't make any sense right? -- Seb
Seb wrote: > On Mon, 18 Jan 2010 08:59:56 +1100, > Chris <dmagick@gmail.com> wrote: > >>> 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. > > Thanks for all your pointers! > > Do views use the indexes in the underlying tables, whenever say a SELECT > operation is called on the view? If so, indexes on views don't make any > sense right? A view WILL use an index(es) on the underlying table(s) if the planner thinks the use of those indexes will be helpful. In general it's as if you substituted the text of the view's query into the query using the view and executed that composite query. Sometimes views are very expensive to compute, and avoiding computing values you're not interested in for a particular query would be very handy. It's not always possible to tack on a WHERE clause that gets applied as part of a big complex join; sometimes you land up computing a lot of data then throwing the vast majority of it away. That's not really desirable. If you have very expensive views, a good way to handle this is to maintain a materialized view and index the materialized view. Pg doesn't currently have any support for creating and maintaining materialized views automatically, but it's easy enough to do it with a few triggers (and you can often do it more efficiently/intelligently than the database could do in the general case). I have a couple of materialized views in my schema that make queries several orders of magnitude faster (!) when querying for current data by eliminating the need for tree-like multiple self joins. Updates to these views are cheap, because the triggers on the source tables can selectively update only the mat.view entries they know are affected by a given update/insert/delete. -- Craig Ringer