Question: Is there a way I can figure out the best way to know which indexes to create for a table?

Answer: 
Short rules of thumb. (Some of these are created automatically, but can possibly be manually dropped later, depending on your dbms. Don't assume you will *always* work on PostgreSQL.)

* Index every primary key.
* Index every foreign key.
* Index every column used in a JOIN clause.
* Index every column used in a WHERE clause.
* Study your documentation to learn the "esoteric" indexing options your dbms supports.

*Every primary key* means that multi-column primary keys should have a single index covering all columns. PostgreSQL will create this index automatically if you declare a multi-column primary key.

There are many cases in which a single multi-column index gives you better performance than several single-column indexes. [Monitor slow queries](http://www.postgresql.org/docs/9.2/static/runtime-config-logging.html#GUC-LOG-MIN-DURATION-STATEMENT) and do testing to figure out which is which.

Assume that any change to indexing will improve some database activities and degrade others. I find it helpful to have a set of SQL statements that I can profile before and after making changes to indexes. This set includes SELECT, INSERT, UPDATE, and DELETE statements.

There's no substitute for studying the docs for your particular dbms.

* [CREATE INDEX](http://www.postgresql.org/docs/current/static/sql-createindex.html)
* [Indexes](http://www.postgresql.org/docs/current/static/indexes.html) (Note especially the sections on indexing expressions, on partial indexes, and on examining index usage)
There are two options.

1. You do it.
2. Technology does it.

The answer for doing it yourself is pretty exhaustively documented here. So let's look at something else.

Pghero
======

[Pghero](https://github.com/ankane/pghero) may be able to assist you if you want some automated advice.

That said it has some shortcomings.

1. It only works on `WHERE` and `ORDER BY`, no `JOINS`.
2. It only uses statistics on percent NULL, and distinct values.

Check out this video [for more information](https://www.youtube.com/watch?v=Mni_1yTaNbE).