Generating multiple indexes in a single postgres table

Question

I have some source syntax that I need to convert from MySQL to postgreSQL. It seems that there is no analog in postgres for generating multiple indexes on a single table. Also, I am having trouble understanding why one would generate multiple indexes on a single table:

ALTER TABLE concept 
  ADD PRIMARY KEY (table_id), 
  ADD UNIQUE INDEX idx_table_table_id (table_id), 
  ADD INDEX idx_table_code (table_code), 
  ADD INDEX idx_table_v_id (v_id), 
  ADD INDEX idx_cd_id (d_id), 
  ADD INDEX idx_table_class_id (table_class_id);

any ideas?

Usually you create unique constraints instead of unique indexes. Well... unless you have a very good reason to do so. — The Impaler
– The Impaler, Commented Oct 19, 2018 at 18:52
Well in general you need an index on everything you're searching for. So, for examples, in customers, you create an index on lastname, mobile number, email, ssn. — Antti Rytsölä
– Antti Rytsölä, Commented Jul 3, 2024 at 9:35

The Impaler · Accepted Answer · 2022-11-18 22:34:54Z

4

In PostgreSQL each constraint is added separately. See ALTER TABLE.

Your single DDL (Data Definition Language) statement needs to be broken down into:

Constraints keep data in check according to rules:

 ALTER TABLE concept ADD PRIMARY KEY (table_id);

Indexes are useful to speed up some queries. Use carefully:
```
 CREATE INDEX idx_table_code ON concept (table_code);

 CREATE INDEX idx_table_v_id ON concept (v_id);

 CREATE INDEX idx_cd_id ON concept (d_id);

 CREATE INDEX idx_table_class_id ON concept (table_class_id);
```
Note: Create indexes only when you need them. My [very personal] rule of thumb is not to exceed 10 for each table. If I get to that level, then I need to reconsider carefully what I'm doing.

edited Nov 18, 2022 at 22:34

answered Oct 19, 2018 at 19:00

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

oscar · Accepted Answer · 2018-10-19 19:49:16Z

0

As somebody said earlier, you should make a CREATE INDEX statement per each index.

About the multiple index on a table... the issue is that sometimes the queries on a single table could be very different. On your example, is not the same make a sql with the predicate based on table_code column or based on d_id column.

That's why sometimes the better is make a separate index for both. Maybe seems easier and better make a single index for both columns but you should think that the order of the index columns is very important and even more if both columns have a lot of different values. Also not always both table_code or d_id columns could be on the sqls.

answered Oct 19, 2018 at 19:49

oscar

1

Collectives™ on Stack Overflow

Generating multiple indexes in a single postgres table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related