It's not a superpower at all--instead, I'd argue that it's the most important skillset (after understanding the relational model itself) for interacting with RDBMSes at scale.
You don't necessarily have to know whether an index scan is going to be parallelized or whether the CBO is going to switch to a loop because its heuristics think your result set is likely tiny to think about things like "is this data being accessed via the right indexes? Is the data likely in memory or not? Will my joins, given my consistency level, impose locks or concurrency considerations on the tables I expect?"
Oh, I'm not generally correct, but my process of "writing" generally goes:
- Guess what I think is going to happen
- EXPLAIN ANALYZE
- Ohhh, it did [thing]. Lemme tweak if I can convince it to do [other thing] that should work better
- <Repeat>
I'd argue testing against the DB is a core part of the process for writing a query.
You must be psychic if you can figure out what the DB engine is going to do without running it.
Bitmap scan, parallel index scan, merge join, nested loop join, etc. I always have to try and see.