So much inefficiency, too much yellow!
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!
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!
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...
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
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...
After 6 years of hoping someone else would write this, I've finally succumbed to the little marketing devil on my shoulder and written about Postgres query plan visualization tools.
It's hard to imagine someone more biased about any topic, but here goes: www.pgmustard.com/blog/postgre...
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
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...
This is huge, congrats!
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
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...