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

  • 13
    There is. Try use-the-index-luke.com for example. Commented Jan 6, 2013 at 20:41
  • The answer I've seen the most is that you should index primary keys and columns that you use in WHERE clauses. Commented Jan 6, 2013 at 22:39
  • Please don't do that. A primary key defines how the data is physically sorted on the table and it has it's own considerations. You have to pick the primary key very carefully as it is used in all of your other indexes as well. Refer to: sqlskills.com/blogs/kimberly/… Commented Jan 6, 2013 at 23:02
  • 4
    @AliRazeghi That (physical sorting) is true in certain DBMSes (under certain circumstances) and not in others. For example, not true in PostgreSQL. Commented Jan 7, 2013 at 11:10
  • Voting back up! Commented Jan 7, 2013 at 18:21

3 Answers 3


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 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.

  • Indexes (Note especially the sections on indexing expressions, on partial indexes, and on examining index usage)

In addition to what @Catcall already provided, 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:

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:

Multicolumn indexes, partial indexes and indexes on expressions are particularly powerful tools in PostgreSQL. Since PostgreSQL 9.2 there are also index-only scans, 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 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 features tools to find duplicate or unused indexes, among other things.

Related answer on SO:

  • If I remember right, automatic index over PK is also created on Oracle v.>=10 and Sql Server >= 2008
    – EAmez
    Commented Sep 9, 2019 at 6:36

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 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.