No this is a fundamental concept in postgres. If you do EXPLAIN ANALYZE on a query, you get the query plan, which is influenced by the query, indexes, table structure, etc. But the QP may decide to do a silly thing like a sequential scan where a better path exists, and adding an index to avoid the scan would be cost prohibitive. So if you could just override the QP and say "Use this index and do this type of sort and then this type of scan, damn the consequences!" it would give the query writer full control. I don't understand why you can't just force the system to follow a path - you as the developer pay for it regardless.
Nobody does actually. Postgres not having deterministic query plans is a big pain and a good reason not to use it. The same query may use different query plans depending on the estimated number of affected rows, very frustrating.
It's been many years since I've had to use an Oracle db, but they definitely allowed SQL developers to forcibly specify a query-plan - called "query hints", wherein you could specify it to prefer using (or avoiding) certain indexes, certain join-strategies (hash, loop, etc) - this was done via comments immediately before the SQL query - see https://docs.oracle.com/cd/B13789_01/server.101/b10752/hints...
Postgresql doesn't have this, however, I've rarely missed this feature - tables with good indexes, regular db hygiene, etc, almost always perform excellently. The query planner is very, very smart nowadays.
The query hints thing is actually quite useful in an educational context.
There's DB internals/performance course which I'm a TA for and we lean heavily on being able to force Oracle's rule-based or cost-based optimisers --- because then we can get the students to analyse and compare the plans.
I still find myself comparing it to oracle on these points.
As a user, postgres is a far more ergonomic database, but things like this show good old insane oracle is still strongly ahead on some of these points.
In practice, postgres is not as bad as you'd expect in the large, but it can still be anoying.
Oh that’s unfortunate, thanks for explaining it. Postgres has been in my list to check out but haven’t done any personal projects that I’d need it for… yet
Just make sure you test for your scale and with representative data and queries. There will be various tipping points with any technology. But you can find them with experimentation.
The way I see it traditional databases are frameworks, and we need to switch to something more like libraries - use the high-level interface when we need it, but be able to dig underneath. Postgres has taken some small steps in this direction with e.g. making the parser available as a separate library; some newer systems (e.g. distributed-first datastores that combine LevelDB with some higher-level layer) go further.