index scan vs index seek, how it determine the cost

George Zhou 2017-03-23 20:14:02

for visitidx seek cost 21.3% returns 27,300 rows, but index scan probookingproidx scan cost only 2.% return 2077,000rows. per iteration means what?

Aaron Bertrand 2017-04-11 21:32:52
Well, a few things:

  1. Cost % is just an estimated cost %.
  2. Number of rows is one factor in determining costs, but it is not the only factor. Row size, number of executions, sort requirements, and other factors also contribute to cost-based optimization.
  3. Per iteration means that while you have an index seek on visit, it actually has to perform 27,000 singleton seeks to return those rows. So the cost is not the cost of "a seek," it is the cumulative cost of all of the seeks that needed to be performed.

As a side note, you currently have res_room in an outer join, but then you added a predicate in the where clause against that table, which effectively turns that into an inner join. If you find you are filtering out too many rows, you may want to move that filter to the ON clause of the outer join; if you are getting the right results, you might want to make that an INNER JOIN instead, so that the intent is unambiguous.