Indexed view maintenance with foreign key not elided when inserting parent row

Question

TL;DR;

Given an indexed view that has a JOIN between two tables that already have a foreign key relationship along with a WHERE predicate on the parent table.
When inserting into the parent table of the foreign key, the compiler is adding index maintenance to the plan, even though it is provable that no matching rows can exist.

Is this a bug, or perhaps a missed optimization? Or is there some logical or algebraic fallacy that I'm under?

Setup

CREATE TABLE Parent (Id int identity primary key, SomeCol bit not null, OtherCol int not null);

CREATE TABLE Child (Id int identity primary key, ParentId int not null references Parent (Id) INDEX IX_Parent NONCLUSTERED);

CREATE VIEW dbo.vChild
WITH SCHEMABINDING
AS
SELECT c.Id, c.ParentId
FROM dbo.Child c
JOIN dbo.Parent p ON p.Id = c.ParentId
-- WHERE p.SomeCol = 0;  -- problem dependent on this line

CREATE UNIQUE CLUSTERED INDEX CX_vChild ON vChild (Id)

db<>fiddle with WHERE
db<>fiddle without WHERE

At this stage, any UPDATE affecting columns in the view, and any DELETE, of the view's tables quite rightly trigger view maintenance. The compiler will take the modified rows, spool them up and feed them through the view's joins, outputting any results into the view's index.

The same can be said for insertions to Child, because a row could exist for Parent already (that qualifies against the WHERE), and therefore the new Child row could qualify for the join.

Problem

When inserting into Parent, it is provable that index maintenance does not need to be done. A matching row cannot yet exist in Child because of the foreign key relationship, so there can therefore be no rows from the insert that qualify for the view.

If you were to run the following script, you will see that no view maintenance is done.

INSERT Parent (SomeCol, OtherCol)
VALUES (0, 100);

PasteThePlan

Quite clearly, the compiler can deduce that view maintenance is not needed here.

However, if you were to uncomment the line WHERE p.SomeCol = 0 in the view definition, you suddenly get view maintenance. So the addition of another column to the view, which is not a joining column and does not have a foreign-key relationship, causes this. This is despite the same relational logic applying: the insert should still provably not qualify for the view, because of the foreign-key column that is still there.

PasteThePlan

Oddly enough, the compiler can still recognize some cases where the insert will not qualify for the view (and despite this particular example being auto-parameterized).

Here the compiler recognizes that SomeCol fails the WHERE, and there is no need to do index maintenance.

INSERT Parent (SomeCol, OtherCol)
VALUES (1, 100);

PasteThePlan

Answer 1

The optimizer doesn't do the sort of reasoning you describe.

Rather, it depends on a standard set of often-useful, easy-to-implement, quick-to-check features like contradiction detection and redundant join removal to produce useful simplifications.

It is the interaction between these modular features than can produce apparently-complex behaviours that people sometimes mistake for extensive optimization and deep semantic analysis.

The behaviour you observe can be explained by reference to these standard optimizer features, and the way indexed views are maintained using the delta algebra.

---

This query only touches the Child table in the execution plan because the parent row is guaranteed to exist:

SELECT c.Id, c.ParentId
FROM dbo.Child c
JOIN dbo.Parent p ON p.Id = c.ParentId

When you add a predicate on the Parent table, that is no longer logically possible:

SELECT c.Id, c.ParentId
FROM dbo.Child c
JOIN dbo.Parent p ON p.Id = c.ParentId
WHERE p.SomeCol = 0;  -- problem dependent on this line

The same underlying mechanism is responsible for removing the join in the maintenance subtree produced via the delta algebra. You can see a bit of this with trace flag 8619, when the debug output includes:

Full Join removed for table TBL: dbo.Parent

For an insert, the delta produced is very similar to the above illustrative queries. For an update, delete, or merge, the deltas may be different because different information may be needed to maintain the view. (In your particular example, it is hard to see what might be updated on the Parent table, but the question is primarily about an insert.)

---

When the WHERE clause is present, a Filter on that predicate appears in the maintenance part of the plan.

For this statement, a contradiction is detected between the filter on SomeCol = 0 and the value of SomeCol specified in the insert. That contradiction results in the entire maintenance subtree being guaranteed empty and therefore removed:

INSERT Parent (SomeCol, OtherCol)
VALUES (1, 100);

For this statement, there is no contradiction, but the Filter is redundant and therefore removed. The maintenance subtree is not guaranteed empty, and is so not removed:

INSERT Parent (SomeCol, OtherCol)
VALUES (0, 100);

If you use e.g. local variables or parameters instead of literals, the Filter will reappear.

The example given is not simple parameterized. It qualified for consideration, but parameterization was not determined to be safe. Don't be mislead by the presence of markers like @1 in the text. See Why does a plan with FULL optimization show simple parameterization?