CODEWITHSUNDEEP
mysqlpostgresql
d8tasmith
d8tasmith

Reputation: 43

Generating multiple indexes in a single postgres table

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?

Upvotes: 4

Views: 5397

Answers (3)

The Impaler
The Impaler

Reputation: 48770

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.

Upvotes: 4

oscar
oscar

Reputation: 1

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.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311073

You could break it into separate statements. Note also that creating a primary key will implicitly create a unique index on that column, so you shouldn't attempt to explicitly create one too:

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

Upvotes: 0

Related Questions