2

Using PostgreSQL-13, I need advice about key index. I have a table named "employee" and I manage my DB using DBeaver Community Edition (latest).

Fieldname      Type
----------     ----
pid            bigint -  nextval('employee_pid_seq'::regclass) -- 
Primary Key
company_id     varchar(10)
employee_id    varchar(10)
employee_name  varchar(50)

I'm learning to fine-tune this simple table. company_id and employee_id is a unique index.

When running:

select * from employee order by company_id

... to enhance the performance, do I also need to have company_id as non-unique index?

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
Dev
  • 21
  • 2
  • do I also need to have company_id as non-unique index? No. The index prefix of UNIQUE (company_id, employee_id) will be used. – Akina Dec 27 '22 at 08:45
  • 1
    PS. id as VARCHAR is not good idea. Use numeric datatype (INT for example) instead. – Akina Dec 27 '22 at 08:45
  • 1
    Note that identity columns (generated always as identity) are recommended for auto-generated primary key values. company_id should probably be a foreign key. Not sure why you have a employee_id (which sounds like another foreign key column) and an employee_name. If employee_id is a foreign key the name should be stored in the referenced table. –  Dec 27 '22 at 08:46

1 Answers1

2

Once you have a unique index (or constraint, same effect) on (company_id, employee_id), you typically don't need another index on just (company_id).
The multicolumn index with leading company_id supports your ORDER BY. See:

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600