10

I've created the filtered index below however when I run the 2 queries further down this index only gets used for a seek in the first example that has the END_DTTM in the JOIN rather than the where clause (that's the only difference in the queries). Can anybody explain why this happens?

Index Creation

CREATE NONCLUSTERED INDEX [ix_PATIENT_LIST_BESPOKE_LIST_ID_includes] ON [dbo].[PATIENT_LIST_BESPOKE] 
(
    [LIST_ID] ASC,
    [END_DTTM] ASC
)
WHERE ([END_DTTM] IS NULL)

Queries

DECLARE @LIST_ID INT = 3655

--This one seeks on the index

SELECT  
    PATIENT_LISTS.LIST_ID
FROM    
    DBO.PATIENT_LISTS
    LEFT JOIN DBO.PATIENT_LIST_BESPOKE ON PATIENT_LISTS.LIST_ID = PATIENT_LIST_BESPOKE.LIST_ID  
                                      AND PATIENT_LIST_BESPOKE.END_DTTM IS NULL
WHERE
    PATIENT_LISTS.LIST_ID = @LIST_ID

--This one scans on the index

SELECT  
    PATIENT_LISTS.LIST_ID
FROM    
    DBO.PATIENT_LISTS
    LEFT JOIN DBO.PATIENT_LIST_BESPOKE ON PATIENT_LISTS.LIST_ID = PATIENT_LIST_BESPOKE.LIST_ID  
WHERE   
    PATIENT_LISTS.LIST_ID = @LIST_ID AND
    PATIENT_LIST_BESPOKE.END_DTTM IS NULL   
12

For the optimizer to match a predicate to an index (filtered or otherwise), the predicate must appear adjacent to the Get operation in the logical query tree. To facilitate this, predicates are generally pushed as close as possible to the leaves of the logical tree before optimization begins.

To greatly simplify, the physical index strategy implementation does this:

Predicate + Logical Get -> Physical Get (using Index)

The query you are interested in starts with the predicate above an outer join:

Predicate on T2 --+-- LOJ -- Get (T1)
                       |
                       +---- Get (T2)

This shape does not match the index strategy rule because the predicate is not adjacent to the Get. So, the first part of the answer is that filtered index matching will fail unless the predicate can be pushed past the outer join.

The second part is simply that the optimizer does not contain the necessary exploration rule to move a predicate past an outer join on the preserved side, because the transformation is so rarely valid. It is a general feature of the optimizer that only the most frequently useful rules are implemented.

As a result, matching the filtered index fails in this case. To be clear, the rewrite would be valid in the very specific case you cite (second query).

For the first query form (with different semantics), the predicate is associated with the join from the start, and the predicate push-down logic can move this the short distance to the Get because it does not have to move past an outer join as explained above.

Background and further information:

9

These are not semantically the same queries, since one can filter before the join the other can filter after. Let me illustrate with a simpler example:

CREATE TABLE dbo.Lefty(LeftyID INT PRIMARY KEY);

CREATE TABLE dbo.Righty(LeftyID INT, SomeList INT);

INSERT dbo.Lefty(LeftyID) VALUES(1),(2),(3);

INSERT dbo.Righty(LeftyID, SomeList) VALUES(1,1),(1,NULL),(2,2);

Query 1 returns all three rows:

SELECT l.LeftyID, r.SomeList
FROM dbo.Lefty AS l
LEFT OUTER JOIN dbo.Righty AS r
ON l.LeftyID = r.LeftyID
AND r.SomeList IS NULL;

Query 2, however, leaves out LeftyID 2:

SELECT l.LeftyID, r.SomeList
FROM dbo.Lefty AS l
LEFT OUTER JOIN dbo.Righty AS r
ON l.LeftyID = r.LeftyID
WHERE r.SomeList IS NULL;

SQLfiddle proof

If you're trying perform an anti-semi join, the tested column needs to be not nullable. Moving criteria between ON and WHERE makes no logical difference when you're dealing with INNER joins only, but with OUTER there is a significant difference. And you should care more that your results are correct than whether or not a filtered index could even be used.

  • thanks for the answer but I'm not claiming the queries are the same, I'm asking why one query uses the filtered index and the other doesn't. – chris Sep 21 '15 at 12:17
  • @chris Did you try to force that index with an index hint? I'd be curious to compare actual, post-execution plans with and without that hint. To me it's clear that the optimizer is ignoring that index when it believes that it is doing an anti-semi join (since it wouldn't expect a nullable column to be used in that case), but I'm not sure if that's to do with costing or order of operations or some underlying knowledge that there are potentially a lot more rows coming from the left side than those that are in the filtered index. Seeing the plans might help. – Aaron Bertrand Sep 21 '15 at 13:20
3

The two queries are different - in meaning and results. Here is a rewriting, so it's more obvious what the two queries are doing:

-- 1st query
SELECT  
    a.LIST_ID
FROM    
      ( SELECT LIST_ID 
        FROM   DBO.PATIENT_LISTS
        WHERE  LIST_ID = @LIST_ID
      ) AS a
    LEFT JOIN  
      ( SELECT LIST_ID                    -- the filtered index
        FROM   DBO.PATIENT_LIST_BESPOKE   -- can be used
        WHERE  END_DTTM IS NULL           -- for the subquery
      ) AS b
    ON  a.LIST_ID = b.LIST_ID ;           -- and the join

and 2nd:

-- 2nd query
SELECT  
    a.LIST_ID
FROM    
      ( SELECT LIST_ID 
        FROM   DBO.PATIENT_LISTS
        WHERE  LIST_ID = @LIST_ID
      ) AS a
    JOIN  
      ( SELECT LIST_ID                    -- the filtered index
        FROM   DBO.PATIENT_LIST_BESPOKE   -- can be used
        WHERE  END_DTTM IS NULL           -- for the subquery
      ) AS b
    ON  a.LIST_ID = b.LIST_ID             -- and the join

UNION ALL

SELECT  
    a.LIST_ID
FROM    
      ( SELECT LIST_ID 
        FROM   DBO.PATIENT_LISTS
        WHERE  LIST_ID = @LIST_ID
      ) AS a
WHERE NOT EXISTS  
      ( SELECT *
        FROM   DBO.PATIENT_LIST_BESPOKE AS b
        WHERE  a.LIST_ID = b.LIST_ID         -- but not for this
      ) ;

I think it's now pretty obvious that for the 2nd part of the 2nq query, the filtered index cannot be used.


In detail, regarding these queries, there are 4 types of LIST_ID values in the first table:

  • (a) values that have matching rows in the second table, all with END_DTTM IS NULL.

  • (b) values that have matching rows in the second table, both with END_DTTM IS NULL and with END_DTTM IS NOT NULL .

  • (c) values that have matching rows in the second table, all with END_DTTM IS NOT NULL.

  • (d) values that have no matching rows in the second table.

Now, the 1st query will return all values of type (a) and (b) possibly many times (as many as they have a matching row in second table with END_DTTM IS NULL) and all rows of type (c) and (d) exactly once (that's the non-matching part of the outer join).

The 2nd query will return all values of type (a) and (b) possibly many times (as many as they have a matching row in second table with END_DTTM IS NULL) and all rows of type (d) exactly once.
It will not return any value of type (c) because the join will find matching rows in second table (but these will have END_DTTM IS NOT NULL) and they will be removed by the subsequent WHERE clause.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.