This is how I learned of the existence of the `EXPLAIN` keyword.
Somehow I managed to miss its existence all this time, and it immediately sews up my biggest frustration using relational DBs: the method by which they evaluate a request is completely opaque and more-or-less disjoint from the SQL language (i.e. the solution they use is completely open-ended), so it's nearly impossible to just guess whether a given query will be super expensive.
You might be interested in sites like https://explain.dalibo.com/ which make the output a bit nicer to read. I use these quite often to quickly identify bottlenecks.
I think it’s a good habit to EXPLAIN all your production code. But watch out for the fact that the same query can optimise differently on different instances of the “same” database. Because the optimiser uses table statistics to find the “best” execution plan so if your dev/test have different datasets, or the stats are not properly maintained, you can get completely different execution plans between each system.
Somehow I managed to miss its existence all this time, and it immediately sews up my biggest frustration using relational DBs: the method by which they evaluate a request is completely opaque and more-or-less disjoint from the SQL language (i.e. the solution they use is completely open-ended), so it's nearly impossible to just guess whether a given query will be super expensive.
Very good to know.