When should I use Index on PostgreSQL?

Question

In my Java (Spring Boot app), I have the following entity definition which has a relationship with Company entity via companyUuid property. I will also let users to search by name field. We do not use entity relation annotations e.g. @OneToMany,...

@Entity
public class DemoEntity {
@Id
private long id;

private UUID uuid;

private UUID companyUuid;

private String name;

// code omitted for brevity

}

1. As this table has a FK of companyUuid, should I add an index for it? If so, is the following index definition is true?

@Table(indexes = {
    @Index(unique = true, name = "company_uuid_key", columnList = "companyUuid")
})

2. As users search Demo entity records by name field, should I also add index for each search field (for this scenario, pnly name field)? If so, is the following index definition is true?

@Table(indexes = {
    @Index(unique = true, name = "name_key", columnList = "name")
})

score 0 · Accepted Answer · answered Dec 13 '21 at 12:15

Should you index fields that are foreign keys to [fields in] other tables? Yes.
Does that index have to be unique? Probably not.

Consider a table of Towns, each of which exists within a State.
Many different Towns will appear within one particular State. The Primary Key on the States table will (by definition) be unique [within that table] but the same State id will appear in many Town records.

If you're going to allow searching by name then you ought to index that as well. However, consider whether these names are unique [within that table]. From the above example, there are many Towns called "Birmingham", but each one is in a different State.

When should I use Index on PostgreSQL?

1 Answers1