Why SELECT COUNT() query execution plan includes left joined table?

Question

In SQL Server 2012 I have table valued function with join to another table I need to to count number of rows for this 'table valued function'. When I inspect the execution plan, I can see the left join table. Why? How can left joined table influence number of rows returned? I would expect that the db engine does not need to evaluate left joint table in SELECT count(..) query.

Select count(realtyId) FROM [dbo].[GetFilteredRealtyFulltext]('"praha"')

The execution plan:

The table valued function:

CREATE FUNCTION [dbo].[GetFilteredRealtyFulltext]
(@criteria nvarchar(4000))
RETURNS TABLE
AS
RETURN (SELECT 
realty.Id AS realtyId,
realty.OwnerId,
realty.Caption AS realtyCaption,
realty.BusinessCategory,
realty.Created,
realty.LastChanged,
realty.LastChangedType,
realty.Price,
realty.Pricing,
realty.PriceCurrency,
realty.PriceNote,
realty.PricePlus,
realty.OfferState,
realty.OrderCode,
realty.PublishAddress,
realty.PublishMap,
realty.AreaLand,
realty.AreaCover,
realty.AreaFloor,
realty.Views,
realty.TopPoints,
realty.Radius,
COALESCE(realty.Wgs84X, ruian_cobce.Wgs84X, ruian_obec.Wgs84X) as Wgs84X,
COALESCE(realty.Wgs84Y, ruian_cobce.Wgs84Y, ruian_obec.Wgs84Y) as Wgs84Y,
realty.krajId,
realty.okresId,
realty.obecId,
realty.cobceId,
IsNull(CONVERT(int,realty.Ranking),0) as Ranking,

realty.energy_efficiency_rating,
realty.energy_performance_attachment,
realty.energy_performance_certificate,
realty.energy_performance_summary,

Category.Id AS CategoryId,
Category.ParentCategoryId,
Category.WholeName,
okres.nazev AS okres,
ruian_obec.nazev AS obec,
ruian_cobce.nazev AS cobce,
ExternFile.ServerPath,
Person.ParentPersonId,
( COALESCE(ftR.Rank,0) + COALESCE(ftObec.Rank,0) + COALESCE(ftOkres.Rank,0) + COALESCE(ftpobvod.Rank,0)) AS FtRank

FROM realty
JOIN Category ON realty.CategoryId = Category.Id
LEFT JOIN ruian_cobce ON realty.cobceId = ruian_cobce.cobce_kod
LEFT JOIN ruian_obec ON realty.obecId = ruian_obec.obec_kod
LEFT JOIN okres ON realty.okresId = okres.okres_kod
LEFT JOIN ExternFile ON realty.Id = ExternFile.ForeignId AND ExternFile.IsMain = 1 AND ExternFile.ForeignTable = 5
INNER JOIN Person ON realty.OwnerId = Person.Id
Left JOIN CONTAINSTABLE(Realty, *, @criteria) ftR ON realty.Id = ftR.[Key] 
Left JOIN CONTAINSTABLE(ruian_obec, *, @criteria) ftObec ON realty.obecId = ftObec.[Key] 
Left JOIN CONTAINSTABLE(Okres, *, @criteria) ftOkres ON realty.okresId = ftOkres.[Key]
Left JOIN CONTAINSTABLE(pobvod, *, @criteria) ftpobvod ON realty.pobvodId = ftpobvod.[Key]
WHERE Person.ConfirmStatus = 1
AND ( COALESCE(ftR.Rank,0) + COALESCE(ftObec.Rank,0) + COALESCE(ftOkres.Rank,0) + COALESCE(ftpobvod.Rank,0))  > 0
)

UPDATE:

I add unique index to follow idea of Rob Farley:

 Create unique nonclustered index ExternFileIsMainUnique ON ExternFile(ForeignId) WHERE IsMain = 1 AND ForeignTable = 5

And indexed suggested by DB Engine:

CREATE NONCLUSTERED INDEX [RealtyOwnerLocation] ON [dbo].[Realty]

( [OwnerId] ASC ) INCLUDE ( [Id], [okresId], [obecId], [pobvodId]) GO

For simplicity I remove condition

WHERE Person.ConfirmStatus = 1

from tabled valued function above.

Now the execution plan is much simpler but it still touch table ExternFile:

Maybe sql server is not clever enough?

Answer 1

If ForeignId, ForeignTable, IsMain is not known* to be unique in ExternFile, then the QO will need to include that table to work out the count. Any time multiple rows match, the count will be affected.

Join Simplification in SQL Server
Designing for simplification (SQLBits recording)

---

_{* The optimizer does not currently recognize filtered unique indexes as unique}

UPDATE (by OP): The solution is to change line in query from LEFT JOIN (which can produce multiple rows):

LEFT JOIN ExternFile ON realty.Id = ExternFile.ForeignId AND ExternFile.IsMain = 1 AND ExternFile.ForeignTable = 5

to OUTER APPLY with TOP (which produce one row and does not affect COUNT)

OUTER APPLY (SELECT TOP (1) ServerPath FROM ExternFile WHERE ForeignId = realty.Id AND IsMain = 1 AND ForeignTable = 5) AS ExternFile

The query is now more effective. Adding a unique index could not be done, because values weren't unique, they were unique only for combination in the condition and this is not considered as unique as mentioned above.