How can I prioritize one index over another?

Question

I have the following code:

SELECT * FROM table WHERE column1='value1' AND column2='value2';

I have an index for column1 and another for column2.

How do I tell Postgres to prefer the second index? Should I put it first?

Answer 1

You cannot tell Postgres directly which index to prefer. It wouldn't make sense, either, except for rare corner cases.

• The order of expressions in the WHERE clause is completely irrelevant. The query planner will apply predicates as it sees fit.

• You are not limited to use just one index. Postgres can use both an combine them with bitmap index scans. Details in this related answer on dba.SE:

  • PostgreSQL versus MySQL for EAV structures storage

  But its often more effective to use just one index and filter the rest.

• Finally, you can create a single multicolumn index over both columns for optimized performance. Like:

  CREATE INDEX foo ON tbl (column1, column2);
  

  • Is a composite index also good for queries on the first field?

  Or even a partial index if one of your predicates is constant (most or all queries restrict to column2 = 'constant_value') and this selection is substantially smaller than the whole table:

  CREATE INDEX foo ON tbl (column1) WHERE column2 = 'constant_value';
  

If Postgres makes bad choices for query plans, there is typically something off in your database:

• Keep PostgreSQL from sometimes choosing a bad query plan