Query column A, order by B – is index needed if A and B together are the PK?

Posted on

Question :

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?

Answer :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *