What are some best practices and "rules of thumb" for creating database indexes?

Question

I have an app, which cycles through a huge number of records in a database table and performs a number of SQL and .Net operations on records within that database (currently I am using Castle.ActiveRecord on PostgreSQL).

I added some basic btree indexes on a couple of the feilds, and as you would expect, the performance of the SQL operations increased substantially. Wanting to make the most of dbms performance I want to make some better educated choices about what I should index on all my projects.

I understand that there is a detrement to performance when doing inserts (as the database needs to update the index, as well as the data), but what suggestions and best practices should I consider with creating database indexes? How do I best select the feilds/combination of fields for a set of database indexes (rules of thumb)?

Also, how do I best select which index to use as a clustered index? And when it comes to the access method, under what conditions should I use a btree over a hash or a gist or a gin (what are they anyway?).

Answer 1

Some of my rules of thumb:

• Index ALL primary keys (I think most RDBMS do this when the table is created).
• Index ALL foreign key columns.
• Create more indexes ONLY if:
  • Queries are slow.
  • You know the data volume is going to increase significantly.
• Run statistics when populating a lot of data in tables.

If a query is slow, look at the execution plan and:

• If the query for a table only uses a few columns, put all those columns into an index, then you can help the RDBMS to only use the index.
• Don't waste resources indexing tiny tables (hundreds of records).
• Index multiple columns in order from high cardinality to less. This means: first index the columns with more distinct values, followed by columns with fewer distinct values.
• If a query needs to access more than 10% of the data, a full scan is normally better than an index.