My first experience with Oracle (this was a long time ago, and I suspect they didn't even call it EXPLAIN). Anyway, I found it significantly better and clearer than the EXPLAIN output from Postgres or MySQL.
There was a query being emitted by the anti-fraud software of a certain well-known US bank, that was taking 45 minutes to run, which didn't please the fraud team. This fraud team weren't highly-paid elites; they were drudge workers, as much at risk of burnout as anyone.
Anyway, I was tasked with fixing this (low-importance) problem. I managed to figure out what the problem was with the query, with the help of EXPLAIN. I reconstructed the query so it ran in less than 10 seconds, and submitted the patch.
It took two months for the DBAs to let me add the requisite index to production. They'd given me a copy of the production database, eventually, so I could test my change, and make sure it didn't break anything else; but they still held it back. DBAs were very anally-retentive in those days.
I got no credit for that (except from myself :-). I'm happy to pat myself on the back for reducing the query time from absurd to reasonable, and by a factor of 1/270.
I've never used Oracle since, by the way; it's always looked to me like a blind alley. But $FINCORP used to like Oracle, and the Oracle DBAs were treated like ghodz.
They all were, famously so. It attracts a certain type which I like to call tech-bureaucrat. It is a problem of incentives: if something goes wrong with the db, they get the blame. If something simply sucks, they don't get blame for that. This is the same with all these institutionalised gate-keepers, be it "quality", "security" or whatever. I'm not sure there is a general solution.
There may have been write latency SLAs on the table that they were concerned about. Or maybe they had to wait until a maintenance window to release it.
I know the blog post has MySQL in the title, but one point I'll add for those of us working with Postgres: writing it as `EXPLAIN (ANALYZE, BUFFERS)` adds buffer hits to each line of the output. I've found that valuable to reveal what parts of a query are hitting the hardware the hardest and there's seemingly no reason to not add buffers to the output each time.
Understanding query plans is a universal skill that mostly transcends database particulars, so even this Planetscale article will serve as a good primer too.
This series of blog posts is one option. But my impression is that most of this comes simply with experience. At first the main thing you'd look for is simply whether it uses the indexes you want it to use, or if it does full table scans.
I'd also recommend to simply read the Postgres manual, at least the parts that are not entirely about the syntax of various commands. There is a lot of information in there that will help to understand how Postgres works.
I personally prefer https://explain.dalibo.com over depesz's version, both from a visualization standpoint (I can more clearly see the hierarchy of operations) but also because I've had some not great experiences with depesz the individual. Great blog, but a really soured opinion of him.
Note that the official docs are not always the best resource for a given topic (in general - I don't know about Postgres specifically). "What is the best resource" is a totally reasonable question, and general opinion may be that the answer is the official docs.
I found that in general, it was OK for pretty obvious things but sometimes it missed stuff such as: running a LIKE with wild card at the start, ie: "LIKE '%Luke%'". Running that wildcard in the start tends to make the optimizer do a full scan.
Otherwise, it did okay. Just ensure you give ChatGPT the schema as well for its best suggestion.
There was a query being emitted by the anti-fraud software of a certain well-known US bank, that was taking 45 minutes to run, which didn't please the fraud team. This fraud team weren't highly-paid elites; they were drudge workers, as much at risk of burnout as anyone.
Anyway, I was tasked with fixing this (low-importance) problem. I managed to figure out what the problem was with the query, with the help of EXPLAIN. I reconstructed the query so it ran in less than 10 seconds, and submitted the patch.
It took two months for the DBAs to let me add the requisite index to production. They'd given me a copy of the production database, eventually, so I could test my change, and make sure it didn't break anything else; but they still held it back. DBAs were very anally-retentive in those days.
I got no credit for that (except from myself :-). I'm happy to pat myself on the back for reducing the query time from absurd to reasonable, and by a factor of 1/270.
I've never used Oracle since, by the way; it's always looked to me like a blind alley. But $FINCORP used to like Oracle, and the Oracle DBAs were treated like ghodz.