Sub-optimal query generation against filtered base-typed DbSets in TPC schemas #30774
Comments
|
Oh dear. You'd think I'd check this first. Running some tests with Postgres 14, I've found that the query planner is able to avoid targeting the The results I've found for Postgres**Schema (PostgreSQL v14)**Query #1
|
|
Less familiar with MySQL, but the fact that this is called out as an Given that, closing this issue as, well, a complete non-issue! |
|
I'll call out that SqLite does not appear to be able to optimize this away... if I'm reading this right: ddlCREATE TABLE martial_skill (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
has_strike INTEGER NOT NULL
);
CREATE TABLE magic_skill (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
runic_name TEXT NOT NULL
);
CREATE TABLE divine_skill (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
required_divinity INTEGER NOT NULL
);
INSERT INTO martial_skill (name, has_strike) VALUES ('Karate', 1);
INSERT INTO martial_skill (name, has_strike) VALUES ('Boxing', 1);
INSERT INTO magic_skill (name, runic_name) VALUES ('Wizardry', 'Zarathustra');
INSERT INTO magic_skill (name, runic_name) VALUES ('Necromancy', 'Erebos');
INSERT INTO divine_skill (name, required_divinity) VALUES ('Blessing', 100);
INSERT INTO divine_skill (name, required_divinity) VALUES ('Healing', 50);EXPLAIN QUERY PLAN
SELECT t.id, t.name, t.required_divinity, t.runic_name, t.has_strike, t.discriminator
FROM (
SELECT d.id, d.name, d.required_divinity, NULL AS runic_name, NULL AS has_strike, 'DivineSkill' AS discriminator
FROM divine_skill AS d
UNION ALL
SELECT m.id, m.name, NULL AS required_divinity, m.runic_name, NULL AS has_strike, 'MagicSkill' AS discriminator
FROM magic_skill AS m
UNION ALL
SELECT m0.id, m0.name, NULL AS required_divinity, NULL AS runic_name, m0.has_strike, 'MartialSkill' AS discriminator
FROM martial_skill AS m0
) AS t
WHERE t.discriminator IN ('MartialSkill', 'MagicSkill');Because of this, I'll reopen, because maybe this is something that ought be handled at the EF Core level? If a maintainer wants to close, no gripes from me. |
|
Just to point out that it's possible to rewrite this query by perform the UNION ALL manually against the desired DbSets. So instead of: var martialOrMagicSkills = db.Skills
.Where(s => s is MartialSkill || s is MagicSkill)
.ToList();... one can write: var martialOrMagicSkills = db.MartielSkills
.Concat(db.MagicSkils)
.ToList();I do agree EF could do better here, though it's not likely to be very high-value. |
|
@roji Hm, I may be doing something wrong, but for me, the following: var martialOrMagicSkills2 = db.MartialSkills
.Concat(db.MagicSkills)
.ToList();yields the following compiler error:
Specifying the base type as the generic type argument in var martialOrMagicSkills2 = db.MartialSkills
.Concat<AbstractSkill>(db.MagicSkills)
.ToList();however this leads to a runtime exception when translating the query: Edit: I'll note, filtering multiple base-typed dbsets by derived type with the var martialOrMagicSkills3 = db.Skills.Where(s => s is MartialSkill)
.Concat(db.Skills.Where(s => s is MagicSkill))
.ToList(); |
You're referencing the wrong Concat (ParallelEnumerable.Concat instead of Queryable.Concat) |
|
Note, I believe that compiler error is specifying an arbitrary (perhaps "best-found") extension method candidate, since there is no To demonstrate, explicitly invoking var martialOrMagicSkills2 = Queryable.Concat(
db.MartialSkills,
db.MagicSkills)
.ToList();error:
And, for this form as well, specifying the |
Edit: Since originally writing this, it has come to my attention that many (most?) database implementations will optimize away the "sub-optimal" EF-generated query called out in this issue, such that there is zero effective performance degredation. The databases I tested with, along with the results are laid out in the comments below. TL;DR: This is a complete non-issue for Postgres and MySQL, but appears to negatively impact the query plan on SQLite.
Suppose one has the following schema:
and some related dbsets:
If one wanted to get all "martial" or "magic" skills, they might write the following query:
and that would work!
The generated query, however, is sub-optimal. It looks like this:
Notably, the generated SQL
UNION ALLs the results from all available derived tables, even though it is known at query-time that no results can be found in theDivineSkillstable, based on the query filter.The solution here is to not include the query against the
DivineSkillstable in the generated SQL, since it's corresponding model type has been effectively filtered out.Potentially worth noting that when filtering on a single derived type (instead of 2, as in this example), the generated SQL correctly targets only the specified table. For example, this query:
correctly yields:
Resources
The text was updated successfully, but these errors were encountered: