Query Exercise Challenge: Why Is This Index Getting Smaller?
This exercise can be done on any version of Postgres, and doesn’t require any other data – just runs by itself. Let’s start by creating a table with an index:…
Query Exercise: Find Sister Locations to Help Each Other
For this week’s Query Exercise, let’s start by taking a look at the Users table to see what we’re dealing with: PgSQL select u.location, count(*) as population, avg(u.reputation) as AvgReputation…
Query Exercise: Finding Long Values Faster
Our developers have come to us with a problem query in the Stack Overflow database that isn’t as fast as they’d like. PgSQL select * from users where length(displayname) >…
Query Exercise: Improving Cardinality Estimation
Your challenge for this week is to tune a query. Say Stack Overflow has a dashboard that shows the top-ranking users in their most popular location. The Stack Overflow database…
Query Exercise: Find Tagged Questions Faster.
For this week’s Query Exercise, we’re going to start with an existing query that has performance problems from time to time, depending on how it’s used. This query’s goal is…
Query Exercise: Find the Best Time for Maintenance
If we’ve gotta take the database down for maintenance – perhaps a version upgrade, perhaps upgrading our own code, maybe scaling up the hardware – when’s the best time to…
Query Exercise: Find Posts with the Wrong CommentCount
For this week’s query challenge, we’re going to do some data validation. Pop open the Stack Overflow database and check out the commentcount column on the posts table: In theory,…
Query Exercise: Find Foreign Key Problems
Let’s say we’ve decided to implement foreign keys, and we need to find data that’s going to violate our desired keys. We’re going to use the Stack Overflow database, and…