4 ms·

I got 5/5 as well. My only hesitations were, "Is this a trick question? Like, is there some little-know feature where they've special cased this so that somet

I got 5/5 as well. My only hesitations were, "Is this a trick question? Like, is there some little-know feature where they've special cased this so that something that doesn't seem like it ought to work actually does?"

3pt14159 9y ago
I was on the fence for the "not enough information" question because I thought that for outlandish examples I thought there might be a chance the second one was faster, but I thought that the 99.5% case was covered. I feel kinda robbed.
- rockostrich 9y ago
  Yea, I was a little unsure about that one, but realized that the columns in the `SELECT` were the indexed column and a `COUNT` making it index-only scan. Even if the `SELECT` included `b` it still would've slowed down things a tiny bit, but I think I would've chosen "Doesn't really change performance" in that case.
  - endymi0n 9y ago
    Playing devil's advocate here, especially for Postgres this isn't a clear cut case, as because of Postgres' MVCC implementation, depending on whether the rows were already frozen or not (caused by lots of mutations ongoing and not enough VACUUMs), it might have either used an index-only scan (only from 9.2 on and only if most pages are visible-for-all, so the query scanner allows the optimization). In the other cases, it would have had to fetch all rows from disk anyway, in which case "not enough information" would have been true...
    - anarazel 9y ago
      Nit-Pick: Freezing of rows isn't relevant here, just whether they're all-visible.
  - jve 9y ago
    The thing is, SQL Server still needs to get all of those rows from index. Job done in first case, but on the second: Given loop join, for EVERY of those rows returned you must now lookup clustered index row and see if b matches the case or not. Each lookup may take few logical reads, depending on index depth, which depends on table size. Say you have 5 logical reads for tables with millions of entries to lookup clustered index row. Multiply returned rows which still must be filtered by b. If you get 200 rows, then 200*5 would be 1000 additional logical reads. The result is the lower the selectivity for parameter a, the slower is the query (orders of magnitudes slower). For a low execution count query it doesn't matter that much, but if that one gets executed hundreds or thousands of times per minute, well, better to have b column either in index key column or included column - that would dramatically speed up things.

3pt14159 9y ago

I was on the fence for the "not enough information" question because I thought that for outlandish examples I thought there might be a chance the second one was faster, but I thought that the 99.5% case was covered. I feel kinda robbed.

rockostrich 9y ago
Yea, I was a little unsure about that one, but realized that the columns in the `SELECT` were the indexed column and a `COUNT` making it index-only scan. Even if the `SELECT` included `b` it still would've slowed down things a tiny bit, but I think I would've chosen "Doesn't really change performance" in that case.
- endymi0n 9y ago
  Playing devil's advocate here, especially for Postgres this isn't a clear cut case, as because of Postgres' MVCC implementation, depending on whether the rows were already frozen or not (caused by lots of mutations ongoing and not enough VACUUMs), it might have either used an index-only scan (only from 9.2 on and only if most pages are visible-for-all, so the query scanner allows the optimization). In the other cases, it would have had to fetch all rows from disk anyway, in which case "not enough information" would have been true...
  - anarazel 9y ago
    Nit-Pick: Freezing of rows isn't relevant here, just whether they're all-visible.
- jve 9y ago
  The thing is, SQL Server still needs to get all of those rows from index. Job done in first case, but on the second: Given loop join, for EVERY of those rows returned you must now lookup clustered index row and see if b matches the case or not. Each lookup may take few logical reads, depending on index depth, which depends on table size. Say you have 5 logical reads for tables with millions of entries to lookup clustered index row. Multiply returned rows which still must be filtered by b. If you get 200 rows, then 200*5 would be 1000 additional logical reads. The result is the lower the selectivity for parameter a, the slower is the query (orders of magnitudes slower). For a low execution count query it doesn't matter that much, but if that one gets executed hundreds or thousands of times per minute, well, better to have b column either in index key column or included column - that would dramatically speed up things.