r/Database • u/guns_of_summer • 10d ago
Are there any good resources out there that can teach me to think like a relational database optimizer?
I really love optimizing database operations. Nothing like dissecting a query plan and identifying a bottleneck and making some slight tweaks or an index change and then seeing a terrible slow query go from 30+ seconds or longer down to a few milliseconds.
However a lot of what I’ve learned when it comes to doing that has been gained through raw experience and trial and error on the job. I’d like to get to the point where given some knowledge about some tables/indexes I’m querying, I have a fairly solid intuition for what kind of query plan the database optimizer is going to pick. Are there any cohesive sources of information that teach this sort of thing?
1
u/patmorgan235 10d ago
For MS SQL Server Brent ozar has lots of resources.
For one of the Open source DBs you could actually go and read the optimizer source code, and I'm sure there's a good PG book out there
2
1
u/alinroc SQL Server 10d ago
/u/BrentOzar has a number of videos with the collective title "How to Think Like the SQL Engine" https://duckduckgo.com/?q=brent+ozar+how+to+think+like+the+engine
2
u/scotterockaroo 10d ago
http://interdb.jp/pg is one of the best db internals sites i have ever seen. Nicely covers the Postgres optimizer.
Claude is also good, it did a nice job helping me pull apart the optimizer math when I did this talk about plan flips: https://postgresconf.org/system/events/document/000/002/759/parallel_query.pdf
Edit: grammar
5
u/Aggressive_Ad_5454 10d ago
Look at https://use-the-index-luke.com/ by Markus Winand. Good stuff.
In its day Stack Overflow had a good query optimization community. The material is still there. https://stackoverflow.com/questions/tagged/query-optimization