The documentation is a little misleading. The DMV is a non-materialized view, and does not have a primary key as such. The underlying definitions are a little complex but a simplified definition of `sys.query_store_plan` is:

    CREATE VIEW sys.query_store_plan AS
    SELECT
        PPM.plan_id
        -- various other attributes
    FROM sys.plan_persist_plan_merged AS PPM
    LEFT JOIN sys.syspalvalues AS P
        ON P.class = 'PFT' 
        AND P.[value] = plan_forcing_type;

Further, `sys.plan_persist_plan_merged` is also a view, though one needs to connect via the Dedicated Administrator Connection to see its definition. Again, simplified:

    CREATE VIEW sys.plan_persist_plan_merged AS   
    SELECT     
        P.plan_id as plan_id,    
        -- various other attributes
    FROM sys.plan_persist_plan P 
        -- NOTE - in order to prevent potential deadlock
        -- between QDS_STATEMENT_STABILITY LOCK and index locks   
        WITH (NOLOCK) 
    LEFT JOIN sys.plan_persist_plan_in_memory PM
        ON P.plan_id = PM.plan_id;

The indexes on `sys.plan_persist_plan` are:

``` none
╔════════════════════════╦══════════════════════════════════════╦═════════════╗
║       index_name       ║          index_description           ║ index_keys  ║
╠════════════════════════╬══════════════════════════════════════╬═════════════╣
║ plan_persist_plan_cidx ║ clustered, unique located on PRIMARY ║ plan_id     ║
║ plan_persist_plan_idx1 ║ nonclustered located on PRIMARY      ║ query_id(-) ║
╚════════════════════════╩══════════════════════════════════════╩═════════════╝
```

So `plan_id` is constrained to be unique on `sys.plan_persist_plan`.

Now, `sys.plan_persist_plan_in_memory` is a streaming table-valued function, presenting a tabular view of data only held in internal memory structures. As such, it does not have any unique constraints.

At heart, the query being executed is therefore equivalent to:

    DECLARE @t1 table (plan_id integer NOT NULL);
    DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
    DECLARE @t3 table (plan_id integer NULL);
    
    SELECT 
        T1.plan_id
    FROM @t1 AS T1 
    LEFT JOIN
    (
        SELECT 
            T2.plan_id
        FROM @t2 AS T2
        LEFT JOIN @t3 AS T3 
            ON T3.plan_id = T2.plan_id
    ) AS Q1
        ON Q1.plan_id = T1.plan_id;

...which does not produce join elimination:

[![no join elimination][1]][1]

Getting right to the core of the issue, the problem is the inner query:

    DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
    DECLARE @t3 table (plan_id integer NULL);
    
    SELECT 
        T2.plan_id
    FROM @t2 AS T2
    LEFT JOIN @t3 AS T3 
        ON T3.plan_id = T2.plan_id;

...clearly the left join might result in rows from `@t2` being duplicated because `@t3` has no uniqueness constraint on `plan_id`. Therefore, the join cannot be eliminated:

[![inner query][2]][2]

To workaround this, we can explicitly tell the optimizer that we do not require any duplicate `plan_id` values:

```
DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
DECLARE @t3 table (plan_id integer NULL);

SELECT DISTINCT -- added
    T2.plan_id
FROM @t2 AS T2
LEFT JOIN @t3 AS T3 
    ON T3.plan_id = T2.plan_id;
```

The outer join to `@t3` can now be eliminated:

[![join eliminated][3]][3]

Applying that to the real query:

```
SELECT DISTINCT
    T.plan_id
FROM #tears AS T
LEFT JOIN sys.query_store_plan AS QSP
    ON QSP.plan_id = T.plan_id;
```

Equally, we could add `GROUP BY T.plan_id` instead of the `DISTINCT`. Anyway, the optimizer can now correctly reason about the `plan_id` attribute all the way down through the nested views, and eliminate both outer joins as desired:

[![Both joins eliminated][4]][4]

Note that making `plan_id` unique in the temporary table would not be sufficient to obtain join elimination, since it would not preclude incorrect results. We must explicitly reject duplicate `plan_id` values from the final result to allow the optimizer to work its magic here.

  [1]: https://i.stack.imgur.com/iVQdv.png
  [2]: https://i.stack.imgur.com/19qlT.png
  [3]: https://i.stack.imgur.com/EyM0S.png
  [4]: https://i.stack.imgur.com/b0yx2.png