How to remove Hash Match Inner Join and Index Scan to Index Seek?
Here is my Query:
Select pv.ProductID, v.VendorID, v.Name
From Purchasing.ProductVendor PV
Join Purchasing.Vendor v on PV.VendorID = v.VendorID
Where PV.StandardPrice > 10
Order by PV.ProductID, v.VendorID
The tables appear to be very small so there isn't a guarantee this will work, but you could put an index on
ProductVendor (StandardPrice, VendorId) INCLUDE (ProductID)
in an attempt to predict the plan, you would get an index seek on that new index followed by an NL Join to Vendor.
I am assuming a lot about the DDL on all of this (vendorid in the index would need to be there if it was part of the guessed PK (VendorID, ProductID) and clustered. productid also wouldn't need to be included either. Just guessing though. NL JOIN to Vendor should be an index seek assuming there is a covering index starting with VendorID.
This seems to be a fairly trivial statement and data volume to be optimizing. Is this just part of something larger?
Select pv.ProductID, v.VendorID, v.Name From Purchasing.ProductVendor PV Join Purchasing.Vendor v ON pv.VendorID = v.VendorID where pv.StandardPrice > 10 Order by pv.ProductID, v.VendorID OPTION (LOOP JOIN);
The execution plan is now:
Without the hint, the optimizer estimates that hash join and sort will be cheaper. This is primarily because the cost model assumes the seeks into the Vendor table will require random physical I/Os, whereas hash join results in larger, sequential I/Os. The estimated I/O savings more than make up for the estimated cost of the sort. Don't get me started on the cost model.
The nested loops join plan can be further improved by providing a filtered index. This index is optimal for this query (and its $10 value) only, you would need to assess its potential usefulness across your workload – assuming you do not in fact work for AdventureWorks Inc.
CREATE UNIQUE INDEX i ON Purchasing.ProductVendor (ProductID, VendorID) INCLUDE (StandardPrice) WHERE StandardPrice > $10.00;