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?

3
  • 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, 2022 at 8:45
  • 1
    PS. id as VARCHAR is not good idea. Use numeric datatype (INT for example) instead.
    – Akina
    Dec 27, 2022 at 8: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.
    – user1822
    Dec 27, 2022 at 8:46

1 Answer 1

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.