Query Doctor
LightNews LightNews
banner
querydoctor.com
Query Doctor
@querydoctor.com
3 followers 2 following 6 posts
Posts Replies Media Videos
querydoctor.com
Indexing Challenge #1: Find the most efficient way to retrieve the two records for the game "Dig Dug" that have a score between 10,000 and 12,000. "Most efficient" means "the fewest yellow nodes". Deleting records is cheating 🤓

Playground: app.querydoctor.com/ixr/share/ua...

Good luck!
So much inefficiency, too much yellow!
March 20, 2025 at 11:21 PM
querydoctor.com
This @mattermost.bsky.social article is fascinating, row value comparisons are nifty! Interesting to see IndeX-Ray reveal the unbounded index scan. The other query correctly bounds the index scan to minimize it.

mattermost.com/blog/making-...

Playground: app.querydoctor.com/ixr/share/Zl...
Diagram showing the unbounded index scan with the original query. Yellow records were scanned and discarded, defeating the purpose of the index. The other query correctly users the index to minimize the work being done and maximize the efficiency of the query. Scalability at its best!
March 17, 2025 at 8:09 PM
querydoctor.com
Can the code underlining approach used by IDEs be applied to show indexing problems directly on SQL text? @simp.engineer thinks so!

See how his latest work magically surfaces scan type details from query execution on the relevant clauses in the query itself: app.querydoctor.com/ixr/share/w3...
An image of a SQL text which has filtering clauses decorated based on how, if at all, the database used the index when processing the query An image showing the visualization of a query and the `Index Scan` stage used to execute it. The index is on the `game` column, so it gets a green underline. The `date` column is NOT indexed so the database must retrieve and process records in order to apply that condition. That earns the associated text in the WHERE clause a red squiggly underline
March 6, 2025 at 4:40 PM
querydoctor.com
The conditions shown are also specific to the stage on the screen. A condition on games.genre is only shown when looking at the stage retrieving data from games and NOT for retrieving from the scores table. Bonus: conditions inherited from JOINs are there too!

app.querydoctor.com/ixr/share/Fb...
Diagram for an "Index Scan" on a games table showing the relevant conditions from the query: g.genre = 'Puzzle' AND g.release_date > '2018-12-31' Diagram for an "Index Scan" on a scores table showing the relevant conditions from the query: s.achieved_at < '2024-07-06'. Also shown is the JOIN condition of s.game_id = g.game_id along with its dynamically resolved value (8 in this iteration, 3 in another not shown) during processing as a nested loop join
February 7, 2025 at 5:48 PM
querydoctor.com
What better first post than to announce a feature? How the DB handles the WHERE clause of a query is now shown directly in the diagrams! Easily see how the logic of SQL is translated into a plan the DB can use to find the results.

Check it out an example here: app.querydoctor.com/ixr/share/QQ...
Diagram showing how the index (game) handles a query filtering on the date column. Diagram showing how the index (score, game) handles a query filtering on the date and score columns. Diagram showing how the index (score, game) handles a query filtering on the date and score columns. Diagram showing how the database has to filter all WHERE clauses when no index is being used
February 7, 2025 at 5:07 PM