[join table inheritance]Suboptimal JOIN clause order for ToMany-association to parent class #6571

New Issue

admin · 2026-01-22T15:35:08+01:00

admin commented

2026-01-22 15:35:08 +01:00

Originally created by @flaushi on GitHub (Nov 23, 2020).

Say we have a Class hierarchy Parent and Child1 extends Parent, ..., ChildN extends Parent.

Now if we hydrate such a ManyToMany relation

class AnyClass {
    /** @ORM\ManyToMany(targetEntity="Parent")
     */
    public $parents;

the following query is created (structurally):

 SELECT
    t0.id
    t0.name
    -- all other fields of Parent class
    t1.* -- all fields of Child1
    -- ...
    tN.* -- all fields of ChildN
FROM
    parent t0
    LEFT JOIN child1 ON t0.id = t1.id
    -- ...
    LEFT JOIN childN ON tN.id = t0.id
    INNER JOIN any_class_parent ON t0.id = any_class_parent.parent
WHERE 
    any_class_parent.any_class = ':id'

So the left joins principially assemble the single table inheritance equivalent table, then the inner join and where clause restrict the result set.

On my postgres 12, this leads to a very bad execution plan, because the inner join is applied after all left joins are done.

However, nothing speaks against moving the inner join clause to the front ad first expression after the FROM parent:

 SELECT
    t0.id
    t0.name
    -- all other fields of Parent class
    t1.* -- all fields of Child1
    -- ...
    tN.* -- all fields of ChildN
FROM
    parent to
    INNER JOIN any_class_parent ON t0.id = any_class_parent.parent
    LEFT JOIN child1 ON t0.id = t1.id
    -- ...
    LEFT JOIN childN ON tN.id = t0.id
WHERE 
    any_class_parent.any_class = ':id'

I think this optimization should be generally doable. What do you think? Am I missing something?

Originally created by @flaushi on GitHub (Nov 23, 2020). Say we have a Class hierarchy `Parent` and `Child1 extends Parent`, ..., `ChildN extends Parent`. Now if we hydrate such a ManyToMany relation ``` class AnyClass { /** @ORM\ManyToMany(targetEntity="Parent") */ public $parents; ``` the following query is created (structurally): ``` SELECT t0.id t0.name -- all other fields of Parent class t1.* -- all fields of Child1 -- ... tN.* -- all fields of ChildN FROM parent t0 LEFT JOIN child1 ON t0.id = t1.id -- ... LEFT JOIN childN ON tN.id = t0.id INNER JOIN any_class_parent ON t0.id = any_class_parent.parent WHERE any_class_parent.any_class = ':id' ``` So the left joins principially assemble the single table inheritance equivalent table, then the inner join and where clause restrict the result set. On my postgres 12, this leads to a very bad execution plan, because the inner join is applied **after** all left joins are done. However, nothing speaks against moving the inner join clause to the front ad first expression after the `FROM parent`: ``` SELECT t0.id t0.name -- all other fields of Parent class t1.* -- all fields of Child1 -- ... tN.* -- all fields of ChildN FROM parent to INNER JOIN any_class_parent ON t0.id = any_class_parent.parent LEFT JOIN child1 ON t0.id = t1.id -- ... LEFT JOIN childN ON tN.id = t0.id WHERE any_class_parent.any_class = ':id' ``` I think this optimization should be generally doable. What do you think? Am I missing something?

admin commented

2026-01-22 15:35:08 +01:00

@beberlei commented on GitHub (Nov 23, 2020):

Good idea! The current approach is used more for simplicity. The left joins are part of the code generating the from. This is completely separate from generating the inner join. Reordering them would probably increase code complexity quite a bit

@beberlei commented on GitHub (Nov 23, 2020): Good idea! The current approach is used more for simplicity. The left joins are part of the code generating the from. This is completely separate from generating the inner join. Reordering them would probably increase code complexity quite a bit

admin commented

2026-01-22 15:35:08 +01:00

@flaushi commented on GitHub (Nov 23, 2020):

I had a quick look into EntityManager but really could not find where the queries are built. Can you point me somewhere?

@flaushi commented on GitHub (Nov 23, 2020): I had a quick look into EntityManager but really could not find where the queries are built. Can you point me somewhere?

admin commented

2026-01-22 15:35:09 +01:00

@beberlei commented on GitHub (Nov 23, 2020):

In the Persisters classes and in SqlWalker (for DQL)

@beberlei commented on GitHub (Nov 23, 2020): In the Persisters classes and in SqlWalker (for DQL)

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6571