[rockwotj]

We've had a issue similar here due to using SERIALIZABLE transactions, and postgres chosing an index that caused it to lock the whole relation due to how locks are upgraded if you scan for too much [1]

Every change to our prod DB requires running EXPLAIN and EXPLAIN ANALYZE on some data to make sure the queries are doing the right thing (we use GCP Query Insights to watch for regressions [2]).

The cast majority of our queries are single index scans. I wish there was a database that we could fix the plan when our app is deployed. For the most part our schema is fairly denormalized so we don't need very complex queries. The flexibility/power of SQL is really for debugging, analytics and other one off queries.

Hot take: I wish there was a DB that didn't force SQL. At least for the application, instead you just told it what scan you wanted to do (basically the embed plan directly in the query you send). There could be a reporting mechanism if the DB detected a more efficient plan for the query or something. You could still have a SQL layer for your debug and one off sessions.

I would vastly prefer the predictability over occasional performance spikes or in our case a spike of transaction failures due to a predicate lock being grabbed for a whole table.

[1]: the default here is 32 rows (https://www.postgresql.org/docs/current/runtime-config-locks...)

[2]: https://cloud.google.com/sql/docs/postgres/using-query-insig...

[jonatron]

Postgres is the odd one out. MySQL [1] has plenty of index hints, including FORCE. In the proprietary world, MS SQL Server and Oracle also have query hints. I don't know if there's anything other than a wiki page [2] that hasn't been updated since 2015 that justifies it.

[1]: https://dev.mysql.com/doc/refman/8.0/en/index-hints.html [2]: https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion

[rockwotj]

We did try pghintplan and it didn't seem to work for us unfortunately

https://pghintplan.osdn.jp/pg_hint_plan.html