Databases · Lesson 2.1
One page, a hundred queries
Say Ada opens her dashboard on Simplified, and server A, over its open connection, asks the database server for what it shows.
It lists Simplified's 100 lessons.
And beside each one, how many questions she has answered in it.
Say she has answered questions in every lesson: 3 in lesson 1, 2 in lesson 2, and so on.
Every time a learner answers a question, Simplified adds one row to the attempts table.
That row records who answered, and in which lesson.
Ada's user ID is 1, so her rows have user ID 1.
The row with user ID 2 is Ben's.
Ada's count for lesson 1 is how many of her rows have lesson ID 1.
She has three of them, so her dashboard shows 3.
So how does server A get Ada's counts for all 100 lessons?
The simplest way starts with one query, for the list of lessons.
Select ID and title from lessons sends back 100 rows, one per lesson.
Then server A loops over that list, one lesson at a time.
For lesson 1, it sends one more query, counting Ada's rows in attempts.
It counts the rows where user ID is 1, which is Ada, and lesson ID is 1.
Postgres sends back 3.
Then the loop moves on, and sends the same query for lesson 2.
Postgres sends back 2.
And so on, one query for each lesson, down to lesson 100.
How many queries does Ada's dashboard take, then?
One for the list of lessons.
Then one for each of the 100 lessons on it: 100 more.
1 + 100 makes 101 queries.
This is called an N+1 query.
An N+1 query is one query for a list, then one more for each of its N items.
Here the list has 100 lessons, so N is 100, and N+1 is 101.
Now what might Instagram's feed send?
Say Instagram's server loads your feed with one query, for 10 posts.
Under each post, the feed shows how many comments it has.
Then, in a loop, the server sends one more query per post, counting its comments.
That's 1 for the posts, plus 10, which makes 11 queries.
It's an N+1 query too, with N of 10.
The longer the list, the more queries: every item on it adds one.
Say Ben opens a page on Simplified that lists the 12 lessons of one chapter, each with how many questions he has answered in it. Server A sends one query for the list of 12 lessons. Then, in a loop, it sends one more query per lesson, counting Ben's rows in attempts for that lesson.
How many queries does server A send for Ben's page?
Explanation
First, one query gets the list of 12 lessons.
Then, the loop sends one more query for each of the 12 lessons: 12 more.
Therefore, 1 + 12 makes 13 queries.
Ada's dashboard lists every lesson on Simplified, with how many questions she has answered in each. Server A sends one query for the list of lessons, then, in a loop, one more query per lesson. Simplified has 100 lessons, then adds 20 more.
How many more queries does her dashboard send than before?
Databases · Lesson 2.2
Why every query adds to the wait
So Ada's dashboard takes 101 queries: one for the list of lessons, then one for each lesson.
Each query is small: just a count of her rows for one lesson.
But how long does Ada wait while server A sends all 101?
Each query is a message from server A to the database server.
And its result is a message back, from the database server to server A.
So every query takes one round trip: there to the database server and back.
A round trip to a replica took about 5 milliseconds.
This database server sits close to server A, so say a round trip takes about 1 millisecond.
So does server A send all 101 queries at once?
No: server A's loop handles one lesson at a time.
It sends a query, and waits for its result before it sends the next.
Because each turn of the loop needs its query's result before it moves on to the next lesson.
At 0 milliseconds, server A sends the query for the list of lessons.
The 100 rows are back at 1 millisecond, after one round trip.
Then lesson 1's query goes out, and 3 comes back at 2 milliseconds.
Then lesson 2's, and 2 comes back at 3 milliseconds.
Each query adds another millisecond, down to lesson 100's.
So 101 queries take 101 round trips, one after another.
101 round trips × 1 millisecond is 101 milliseconds.
That's 101 milliseconds of waiting, just for messages to go back and forth.
And what happens as Simplified adds lessons?
Say Simplified grows to 500 lessons.
Then the dashboard takes 1 query for the list, plus 500: 501 queries.
501 round trips × 1 millisecond is 501 milliseconds.
So Ada would wait about half a second for her dashboard, just for the messages.
With an N+1 query, every item on the list adds one more round trip to the wait.
Say a round trip between server A and the database server takes 2 milliseconds. Ben opens a page on Simplified that lists 20 lessons. Server A sends one query for the list, then, in a loop, one query per lesson, each waiting for the one before it to come back.
How many milliseconds of round trips does Ben's page wait?
Explanation
First, the page takes 1 query for the list, plus 20, one per lesson: 21 queries.
Then, each query is one round trip, one after another: 21 × 2 milliseconds.
Therefore, Ben's page waits 42 milliseconds.
Say Instagram's server loads your feed with one query, for 10 posts. Then, in a loop, it sends one more query per post, counting that post's comments, each waiting for the one before it to come back. The feed waits 44 milliseconds for round trips.
How many milliseconds is one round trip?
Ada's dashboard on Simplified sends 101 queries: one for the list of lessons, then one per lesson, in a loop. A round trip between server A and the database server takes about 1 millisecond.
Why does she wait about 101 milliseconds, not about 1?
A page on Simplified sends one query for a list of lessons, then, in a loop, one more query per lesson, each waiting for the one before it to come back. A round trip takes 1 millisecond, and the page waits 31 milliseconds for round trips.
How many lessons are on the list?
Ada's dashboard lists every lesson on Simplified. Server A sends one query for the list, then, in a loop, one query per lesson, each waiting for the one before it to come back. A round trip takes 1 millisecond. Simplified grows from 100 lessons to 300.
How many milliseconds longer does Ada wait for round trips?
Ada's dashboard lists every lesson on Simplified. Server A sends one query for the list, then, in a loop, one query per lesson, each waiting for the one before it to come back. Simplified adds one new lesson.
How many round trips does that add to the dashboard's wait?
Ada's dashboard lists Simplified's 100 lessons: server A sends one query for the list, then, in a loop, one query per lesson, each waiting for the one before it to come back. Say a round trip between server A and the database server grows from 1 millisecond to 20 milliseconds.
How many milliseconds longer does Ada wait for round trips?
Databases · Lesson 2.3
One query instead of a hundred
So Ada's dashboard sends 101 queries, one round trip each, one after another.
At about 1 millisecond a round trip, she waits 101 milliseconds.
And with 500 lessons, she'd wait 501.
But how can server A get all 100 counts without a round trip for each one?
With one query that counts every lesson at once.
GROUP BY makes one group per lesson, and COUNT star counts each group.
So server A sends one query that groups Ada's rows by lesson ID.
It selects lesson ID and COUNT star from attempts, where user ID is 1.
Where user ID is 1 keeps only Ada's rows, so Ben's row is left out.
Then GROUP BY lesson ID splits Ada's rows into one group per lesson.
Her three rows for lesson 1 make one group.
And her two rows for lesson 2 make another.
COUNT star counts each group: 3 for lesson 1, and 2 for lesson 2.
Ada has answered in all 100 lessons, so there are 100 groups, down to lesson 100's.
So Postgres sends back 100 rows, each a lesson ID and its count, in one result.
Postgres still goes through Ada's rows, one at a time, putting each in its lesson's group.
But it does that looping on the database server, right next to the rows.
So there's no message for each lesson, and no round trip for each lesson.
How long does Ada wait now?
Server A still sends one query for the list of lessons, for their titles: one round trip.
Then this one query for all 100 counts: one more round trip.
So that's 2 round trips in all, about 2 milliseconds, instead of 101.
And with 500 lessons, it's still 2 round trips, not 501.
So one query that groups the rows replaces a round trip for every lesson.
Say a round trip between server A and the database server takes 2 milliseconds. Ada's dashboard lists Simplified's 100 lessons, and she has answered questions in every one. Server A sends one query for the list of lessons. Then, instead of a loop, it sends SELECT lesson_id, COUNT(*) FROM attempts WHERE user_id = 1 GROUP BY lesson_id;.
How many milliseconds of round trips does Ada's dashboard wait?
Explanation
First, the list of lessons takes one query, and all 100 counts take one more: 2 queries.
Then, each query is one round trip: 2 × 2 milliseconds.
Therefore, Ada's dashboard waits 4 milliseconds.
Simplified's attempts table has one row for every answer a learner gives, with columns user_id and lesson_id. Ben's user ID is 2. His dashboard lists every lesson, with how many questions he has answered in each.
Which one query gets all of his counts at once?
Simplified's attempts table has one row for every answer a learner gives, with columns user_id and lesson_id. Dee's user ID is 4, and she has answered questions in 40 of Simplified's 100 lessons. Server A sends SELECT lesson_id, COUNT(*) FROM attempts WHERE user_id = 4 GROUP BY lesson_id;.
How many rows come back?
Say Instagram's server loads your feed with one query, for 10 posts. It used to send, in a loop, one more query per post, counting that post's comments. Now, after the query for the posts, it sends one query that groups the comments by post ID, and gets all 10 counts at once.
How many queries does the feed take now?
Ada's dashboard lists Simplified's 100 lessons. Server A now gets all her counts with one query: SELECT lesson_id, COUNT(*) FROM attempts WHERE user_id = 1 GROUP BY lesson_id;.
Where are her rows now split by lesson and counted?
Ada's dashboard sends one query for the list of lessons, then one GROUP BY query that gets all her counts at once. Say Simplified grows from 100 lessons to 1,000, and Ada has answered questions in all of them.
How many more round trips does her dashboard take than before?
A round trip between server A and the database server takes 3 milliseconds. Ada's dashboard lists Simplified's 100 lessons. With an N+1 query, it sent one query for the list, then one per lesson, one after another. Now it sends one query for the list, then one GROUP BY query for all her counts.
How many milliseconds less does she wait for round trips?