I have hundreds of queries like this in production (Go + Postgres + pgx), and don't have issues leveraging the right indexes. Make sure when using prepared statements, you have custom query plans per query for this via `SET plan_cache_mode = force_custom_plan`.
These optimizations are trivial for Postgres to make optimization/plan time, so there's no runtime hit. But as always, profile and double check. You're definitely right that assuming can get you in trouble.
I don't have experience with the other databases to speak to their quirks. But for my specific setup, I haven't had issues.
Assuming the MxN claim was accurate, yeah. The author of this post wrote a prior one [1], challenging whether MxN was truly the problem LSP was solving. It's also a good read!
That's part of the protocol with a Document highlight request. Basically if you click on a symbol in the client, it'll send the server side request where it can find all references and mark which ones are read and which are write. The client will then highlight those references with one color for read, one for write, and one for other textual occurrences.
These optimizations are trivial for Postgres to make optimization/plan time, so there's no runtime hit. But as always, profile and double check. You're definitely right that assuming can get you in trouble.
I don't have experience with the other databases to speak to their quirks. But for my specific setup, I haven't had issues.