Spatial Index ignored when using additional non-spatial conditions

Question

Why does SQL Server ignore my spatial index when I use additional conditions? If I add an INDEX hint it will use the correct index, and runs a lot faster. I would like to avoid using the hint if possible.

Here is my SQL:

        PRINT CHAR(13) + 'Distance (No Hint)'

        SET STATISTICS TIME ON;

        SELECT p1.profileId, p2.profileId
        FROM profiles AS p1 WITH (NOLOCK)
            LEFT JOIN dbo.profiles AS p2 WITH (NOLOCK)
                ON p1.location.STDistance(p2.location) < 50 * 1609.344
        WHERE p1.profileId BETWEEN 1000 AND 1010

        SET STATISTICS TIME OFF;

        PRINT CHAR(13) + 'Distance + Gender (No Hint)'

        SET STATISTICS TIME ON;

        SELECT p1.profileId, p2.profileId
        FROM profiles AS p1 WITH (NOLOCK)
            LEFT JOIN dbo.profiles AS p2 WITH (NOLOCK)
                ON p1.location.STDistance(p2.location) < 50 * 1609.344
                AND p1.isMale = ~p2.isMale
        WHERE p1.profileId BETWEEN 1000 AND 1010

        SET STATISTICS TIME OFF;

        PRINT CHAR(13) + 'Distance + Gender (Hint)'

        SET STATISTICS TIME ON;

        SELECT p1.profileId, p2.profileId
        FROM profiles AS p1 WITH (NOLOCK)
            LEFT JOIN dbo.profiles AS p2 WITH (NOLOCK INDEX(IX_location))
                ON p1.location.STDistance(p2.location) < 50 * 1609.344
                AND p1.isMale = ~p2.isMale
        WHERE p1.profileId BETWEEN 1000 AND 1010

        SET STATISTICS TIME OFF;

And here are the results:

Distance (No Hint)

(2206 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 359 ms,  elapsed time = 295 ms.

Distance + Gender (No Hint)

(1000 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 3323 ms,  elapsed time = 9183 ms.

Distance + Gender (Hint)

(1000 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 234 ms,  elapsed time = 307 ms.

Answer 1

It would appear bitwise not in p1.isMale = ~p2.isMale causes the optimiser to pick wrong. Try p1.isMale <> p2.isMale instead