SQL Performance Tuning: Indexing - Made easy

Indexing Rule:

i) The more indexes you have, the slower INSERTs will become because more writes will need to happen to keep the indexes updated.
ii) Make sure to run VACUUM ANALYZE to keep data statistics up to date — as well as recover disk space.
iii) Make sure you ANALYZE when creating a new index, otherwise Postgres will not have analyzed the data and determined that the new index may help for the query.
iv) Joins are also a better solution than subqueries — Postgres will even internally “rewrite” a subquery, creating a join, whenever possible, but this of course increases the time it takes to come up with the query plan. So be a pal and use joins instead of subselects.
v) Always prefer doing an INNER JOIN instead of a LEFT OUTER JOIN
vi) HTU: Especially as more joins are added to a query, left joins limit the planner’s ability optimize the join order.
vii) Postgres can use an index when doing some_string LIKE 'pattern%' but not for some_string LIKE '%pattern%'.  PostgreSQL can use a b-tree index for prefix searches (eg LIKE 'TEST%') with LIKE or SIMILAR TO if the database is in the C locale or the index has text_pattern_ops.
viii) There are a number of operators available for pattern matching in PostgreSQL. LIKE, SIMILAR TO and ~ are covered in this chapter of the manual.(http://www.postgresql.org/docs/current/interactive/functions-matching.html)

If you can, use LIKE (~~), it's fastest.
If you can't, use a regular expression (~), it's more powerful.
Never user SIMILAR TO. It's utterly pointless. More on this further down.

RL: http://stackoverflow.com/questions/12452395/difference-between-like-and-in-postgres
http://dba.stackexchange.com/questions/10694/pattern-matching-with-like-similar-to-or-regular-expressions-in-postgresql
ix) By default, Postgres locks writes (but not reads) to a table while creating an index on it.
x) A unique index guarantees that the table won’t have more than one row with the same value. It’s advantageous to create unique indexes for two reasons: data integrity and performance. Lookups on a unique index are generally very fast.
xi) Distinction between unique indexes and unique constraints:
Unique indexes can be though of as lower level, since expression indexes and partial indexes cannot be created as unique constraints. Even partial unique indexes on expressions are possible.

For Migration:

i) concurrent indexes must be created outside a transaction.
ii) it’s a good idea to isolate concurrent index migrations to their own migration files.

RL: https://robots.thoughtbot.com/how-to-create-postgres-indexes-concurrently-in