3

I have a table (in Postgresql) which has a primary key on columns a and b. I want to do a query:

select * from table where a='XXX' order by b;

Does the existing primary key serve to help with the performance of that query?

Can I improve performance by making one or more other indexes eg. an index on a and/or an index on b?

mcarans
  • 133
  • 2

1 Answers1

1

No, if you already have an index on (a, b) and the PK constraint means you do, then you don't need anything else for this query.

Separate indexes on (a) and (b) would not be useful.

The only thing that might be better than the index on (a, b) would be a covering index. Say you have a query with only SELECT b, c, d columns in the select list while the table has some 10+ columns. Then, an index on (a, b) INCLUDE (c, d) or in lack of that on (a, b, c, d) would be much narrower than the table and would suit the query perfectly. It would result in a better query plan (only an inde seek, no access to the table needed), on the expense of course that you have an additional index targeted for the specific query.

ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
  • 1
    Some additional info for the second part: "A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns" (from https://www.postgresql.org/docs/9.5/indexes-multicolumn.html ). So you even do not need to specify all columns from the index and the index should still be used. – Fiisch Dec 22 '18 at 10:48