In the universe in which I "know" what index to use (an assumption that can be contested!), you saying "well silly you, the planner is too stupid to figure this out" is not a great defense of the system!
My serious belief is that all the SQL variants are generally great, but I just want this to be incremented with some lower-level language that I can be more explicit with, from time to time. If only because sometimes there are operational needs.
The fact that the best we get with this is planner _hints_ is still to this day surprising to me. Hints! I am in control of the machine, why shouldn't it just listen to me! (and to stop the "but random data analyst could break thing", this is why we have invented permission systems)
> you saying "well silly you, the planner is too stupid to figure this out" is not a great defense of the system!
I don’t think that’s what I said. At least, it wasn’t what I meant.
The planner needs accurate stats on the tables to produce an optimal output. So would you, as a human, if you were writing a query to be executed precisely as written.
It also needs to know information about the system it’s operating in – the amount of RAM it can expect to use being the most important one, but also things like the relative performance of a random disk seek vs. sequential.
The planner also has a huge leg up on most people, in that it is aware of all of the kinds of joins and lookups it can do. Off the top of my head, Postgres can scan in the following ways: sequential scan, parallel sequential scan, index scan, parallel index scan, index-only scan, and bitmap scan. For joins, it can use: nested loop, merge, hash. Do you know when all of those are ideal? What about when you have a query joining multiple tables, keeping in mind that the intermediate contents of a table that has been joined once may look nothing like what it started as?
> Off the top of my head, Postgres can scan in the following ways: sequential scan, parallel sequential scan, index scan, parallel index scan, index-only scan, and bitmap scan.
I don't think that last one is a thing. There is both "bitmap heap scan" and "bitmap index scan" though.
It’s the generalized parent of both of those (since you will not see one without the other). You’re technically correct, but I’m also not the only one [0] who uses this term.
My serious belief is that all the SQL variants are generally great, but I just want this to be incremented with some lower-level language that I can be more explicit with, from time to time. If only because sometimes there are operational needs.
The fact that the best we get with this is planner _hints_ is still to this day surprising to me. Hints! I am in control of the machine, why shouldn't it just listen to me! (and to stop the "but random data analyst could break thing", this is why we have invented permission systems)