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)
In addition to what [@Catcall already provided](https://dba.stackexchange.com/a/31517/3684), and to add a small corrective.

Answers so far seem to indicate you'd need to create indexes on primary keys, but that's not the case in PostgreSQL (partial exceptions apply). [The manual:](http://www.postgresql.org/docs/current/indexes-unique.html)

> 
> PostgreSQL automatically creates a unique index when a unique
> constraint or primary key is defined for a table. The **index covers the
> columns that make up the primary key or unique constraint** (a
> multicolumn index, if appropriate), and is the mechanism that enforces
> the constraint.
> 
> 
> 

Bold emphasis mine.

You *may* want to create *additional indexes* for the second or later columns of a multi-column index, but the first one is generally covered just fine by a multicolumn index - except when additional columns make the index much larger. We discussed that in great detail under this related question:

* [Is a composite index also good for queries on the first field?](https://dba.stackexchange.com/questions/27481/is-a-composite-index-also-good-for-queries-on-the-first-field)

[Multicolumn indexes](http://www.postgresql.org/docs/current/indexes-multicolumn.html), [partial indexes](http://www.postgresql.org/docs/current/indexes-partial.html) and [indexes on expressions](http://www.postgresql.org/docs/current/indexes-expressional.html) are particularly powerful tools in PostgreSQL. Since PostgreSQL 9.2 there are also [**index-only scans**](https://www.postgresql.org/docs/current/indexes-index-only-scans.html), filling the role of "covering indexes" in other RDBMS. This isn't another type of index, but a new capability of the RDBMS with existing index types.

Every index carries specific **costs**, so there is no way around some basic knowledge to really optimize indexing. Just creating more indexes can do more harm than good. In particular, indexes can prevent [**HOT updates**](https://stackoverflow.com/questions/7806058/redundant-data-in-update-statement/7806610#7806610) from improving performance.

Generally, write operations (`INSERT`, `DELETE`, `UPDATE`) become more expensive (but the latter two may also benefit!), while read operations (`SELECT`) generally benefit. Too many indexes can exhaust cache memory so that even read operations *can* suffer.

[This Postgres Wiki page on index maintenance](http://wiki.postgresql.org/wiki/Index_Maintenance) features tools to find duplicate or unused indexes, among other things.

Related answer on SO:

* [Optimize PostgreSQL read-only tables](https://stackoverflow.com/a/7438535/939860)