Hacker News new | ask | show | jobs
by tacone 819 days ago
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.
2 comments
antonchekhov 818 days ago
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.

link
aldonius 814 days ago
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.

link
hyperman1 818 days ago
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.

link