Addi indexes to foreign key columns - index generator

Postgres doesn’t automatically add indexes to foreign key columns. This can lead to performance issues with the database. In MySQL and MariaDB, you get the indexes for foreign key columns automatically. Most likely, with Postgres, no one adds indexes for the foreign key columns manually.

For an almost automated way of adding the (missing) indexes for the foreign key columns in Postgres, we added a new index generator module.

To get indexes for the foreign key columns, follow these steps:

1. Install the IndexGenerator module.
2. In your project’s namespace, add the console commands to the ConsoleDependencyProvider class.
   • Spryker\Zed\IndexGenerator\Communication\Console\PostgresIndexGeneratorConsole
   • Spryker\Zed\IndexGenerator\Communication\Console\PostgresIndexRemoverConsole
3. Copy and merge all schema files from the core and the project:

console propel:schema:copy

4. Generate new schema files with the missing indexes:

console propel:postgres-indexes:generate

5. In src/{PROJECT-NAMESPACE}/Zed/IndexGenerator/Persistence/Propel/Schema/*,check the generated schema files.
6. Merge the generated index schema files:

console propel:schema:copy

Once the Propel setup is done, you get the indexes for all the foreign key columns where they are missing.