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")
})

Hi, and welcome to dba.se! Show us the output of \d table_name for the relevant tables using your psql client. The text of your (most important) queries would also be useful. Not everyone here is a Java programmer - however, the experienced users know DDL and DML! — Vérace, Dec 13 '21 at 09:04
Indexes on a table only make sense in the context of a statement that you want to speed up. Please add that statement to the question. — Laurenz Albe, Dec 13 '21 at 09:23
Do you have any idea of the volumetry to be stored in those tables ? Depending on the volumetry if it's very low for example, an index will not be usefull. Thus you could prepare an index for future volumetry. — Hybris95, Dec 13 '21 at 09:35
Does this answer your question? How do I know what indexes to create for a table? — Mike Sherrill 'Cat Recall', Dec 13 '21 at 17:00

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

0

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.

answered Dec 13 '21 at 12:15

Phill W.

8,706
1
11
21

If the searched names are not unique, then should I use index that is NOT unique? Or no need to use index in that situation? – Henry Dec 14 '21 at 06:38
Unique names, unique index. Non-unique names, non-unique index.
If you put a unique index on name and then a /second/ guy called Fred comes along, the table won't let him in! – Phill W. Dec 14 '21 at 10:26

When should I use Index on PostgreSQL?

1 Answers1