5 ms·

Hmm, I got 5 out of 5 for the Postgres, but the questions seem pretty easy for anyone who has dealt with trying to create useful indexes in different databases

Hmm, I got 5 out of 5 for the Postgres, but the questions seem pretty easy for anyone who has dealt with trying to create useful indexes in different databases and debugged slow running queries on an index. `EXPLAIN` is probably the most useful word in all of SQL. I didn't know how to read `EXPLAIN` results for the first few months of first starting to work with raw SQL for the first time. It made life so much easier when I could just take a query, throw an `EXPLAIN` in front of it, and have an answer as to what was being used to fetch my data.

aetherson 9y ago
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.
oeuviz 9y ago
5/5 as well. Even though I have only very limited experience in database design being a project/application manager professionally.
logicallee 9y ago
> the questions seem pretty easy for anyone who has dealt with trying to create useful indexes in different databases and debugged slow running queries on an index. "Should be obvious to the most dim-witted individual..." https://www.youtube.com/watch?v=j4F8jfeLog4 https://www.youtube.com/watch?v=j4F8jfeLog4
anotherevan 9y ago
I also got 5 out of 5 for PostgreSQL, although I'll freely admit it was pretty much guessing on question 4.

aetherson 9y ago

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.

oeuviz 9y ago

5/5 as well. Even though I have only very limited experience in database design being a project/application manager professionally.

logicallee 9y ago

> the questions seem pretty easy for anyone who has dealt with trying to create useful indexes in different databases and debugged slow running queries on an index. "Should be obvious to the most dim-witted individual..." https://www.youtube.com/watch?v=j4F8jfeLog4 https://www.youtube.com/watch?v=j4F8jfeLog4

anotherevan 9y ago

I also got 5 out of 5 for PostgreSQL, although I'll freely admit it was pretty much guessing on question 4.