[DBAL-50] PgSQL driver does not create indexes on foreign key columns Created: 18/Aug/10  Updated: 09/Jan/15  Resolved: 11/Sep/10

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.0-BETA4
Fix Version/s: 2.0.0-RC1-RC3

Type: Bug Priority: Critical
Reporter: Petr Motejlek Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
is referenced by DBAL-1063 Exceptions from SchemaTool when runni... Resolved
is referenced by DDC-3478 [GH-1239] Fix index duplication for u... Resolved


The PostgreSQL database does not create indexes for foreign key columns, the user has to create them by hand. I think that indexes for foreign keys should be created automatically... On my system, an index will not be created automatically for the group_id column in the user table.

 * @Entity
class User {
     * @ManyToOne(targetEntity="Group", inversedBy="users")
    protected $group;

 * @Entity
class Group {
     * @OneToMany(targetEntity="User", mappedBy="group")
    protected $users;

    public function __construct() {
        $this->users = new \Doctrine\Common\Collections\ArrayCollection();

I am using current git clone and PgSQL 8.4.

Comment by Petr Motejlek [ 09/Sep/10 ]

I'd just like to add that there's an even worse problem with this – the indices are not created even for tables that Doctrine creates automatically – for example the joining tables...

Comment by Benjamin Eberlei [ 09/Sep/10 ]

i'll look into it.

Comment by Benjamin Eberlei [ 11/Sep/10 ]

Fixed in master, leading to several follow up bugs that all had to be fixed:

1. generate identifier allowed first char to be a number
2. postgresql composite foreign key detection left a space in the second (and more) column names
3. Index column names were not sanitized to lower-case, leading to comparison bugs.

There has been a major refactoring now such that, for each foreign key there is always an explicit index being created. On SQLite, Postgres and Oracle this can lead to quite some additional indexes being created now using SchemaTool --update. MySQL already did this implicitly.

There are now heuristics that detect duplicate indexes (based on columns indexed) and override rules (adding primary on columns foo, bar will delete index on columns foo bar).

Comment by Benjamin Eberlei [ 11/Sep/10 ]

Note, this commit will not get into Doctrine ORM master unless you update the git-submodule explicitly:

cd lib/vendor/doctrine-dbal
git checkout master

For RC-1 this will be visible to the ORM trunk/master also.

Generated at Mon Jul 06 08:41:50 UTC 2015 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.