Postgres really really needs index hints.
I feel like the evolution in regarding hot standby was somewhat similar: people had objections, people pointed out weaknesses in this family of systems, people pointed at Slony or Skytools, but when someone cleared their schedule and looked like they'd account for bugs for a good while, it wasn't such a big fight in the end.
Just yesterday i spent time debugging a whole query thing cuz heuristics made the planner plan the wrong thing.
The planner is pretty amazing black magic that can just make life easy (just like compilers do a bunch of cool stuff.
But I think SQL perf would be a lot easier for people to grok if the low level involved specifying “hey, system, go through this btree, pull these items, merge with items from this other btree, etc”
I guess that there must be a good number of people who would use it in PostgreSQL. Anybody analyzed previous tries to implement it?
What I've always wanted in Postgres in one quote expect to also allow other index types (e.g. Hash/BRIN/etc) in its language.
Side benefit: It also allows dev's to understand the pro's/con's of these constructs when using each one and build up their knowledge on what is really just algo's and data structures.
SELECT * FROM js_query('...');
Where the query is a JS snippet that has access to low level access methods, e.g.:
var results = [];
var cursor = myTable.indexes.foo.seek('item-23');
while (cursor.next()) {
results.push(cursor.row());
}
return results;
Since I didn't have enough time I dropped the idea, but it'd be awesome if somebody hacked this up at some point : ).
https://news.ycombinator.com/item?id=31067059#31068194
> There's also an extension if you want just the hints: https://pghintplan.osdn.jp/pg_hint_plan.html
if we're going to talk about index functionality that would be good and effective for Postgres, an index across all partitioned tables (both normal and unique) would be very much welcomed.
the problem is finding someone to maintain it for life.
Adding hints might hinder performance improvements when migrating to a newer version of a database, as the newer version might have a better way to execute your query than what the hint is suggesting. You'd have to revisit all your hints when upgrading.
So PG doesn't like hints and instead makes non standard syntax for upsert instead of standard merge syntax with a hint.
Now PG is adding merge anyway and probably could have just extended it with non standard syntax to get an atomic upsert instead it has two different approaches.
IMO it probably would have been better to go with the SQL standard MERGE with a non-standard hint syntax so its more readable between systems instead of adding non standard syntax then adding the standard much later.
Not sure if there is any movement on adding the upsert approach to the SQL standard instead.
> PostgreSQL doesn't have a good way to lock access to a key value that doesn't exist yet--what other databases call key range locking (SQL Server for example). Improvements to the index implementation are needed to allow this feature.
as of 2017 at least the plan was to implement merge using the underlying insert on conflict mechanism and I think everyone agrees that the only reasonable expected behavior from merge is atomic upserts (and the pk violations from concurrent merges on sql server and oracle really should be recognized as bugs!) so maybe postgres merge will be the first non-buggy implementation w/o hints. reading the related threads exposes a surprising amount of complexity though so who knows.
That was something that was discussed at length at the time, but ultimately rejected. And so the implementation of MERGE in Postgres has essentially the same limitations as every other MERGE implementation that I'm familiar with.
Postgres clearly stresses the trade-off that MERGE makes in the docs, though. Which probably sets it apart.
I was the primary author of the feature. That decision wasn't related to hints at all. MERGE just doesn't address the problem that I wanted to address.
I think that you must be referring to Oracle's ignore_row_on_dupkey_index. Is that really in widespread use? While it is technically a hint, it is unlike any other hint it at least one important way: it changes the semantics of the query!
I was thinking of the hints you used to have to use with a merge statement in order to get an atomic upsert - holdlock in sql server and lock table in oracle. My recollection of the terribly old newsgroup thread where it was (presumably you) said merge doesn't perform atomic upserts without these hints and since people were primarily wanting merge to perform atomic upserts and postgres doesn't support hints it made more sense to just make something for upserts hence the on conflict update stuff. Do I have it wrong?
"Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view.
This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements."
MERGE doesn't claim to work as an atomic statement, and doesn't work as an atomic statement in practice. In other words you can get a unique violation error where a "true upsert" would update the row instead. You can work around this "limitation" in MERGE by retrying the statement until it succeeds, but at that point you might as well use a regular INSERT instead.
The syntax for INSERT .. ON CONFLICT constrains the problem in certain ways, which is quite deliberate. It's a pretty faithful representation of what's really going on. Hiding that creates a lot of subtle but real problems.
Statement-level atomicity doesn't mean that you cannot get race conditions like the one that I described - it just means that the statement succeeds or fails as a whole.
It's quite clear that you can get those race conditions in both Oracle and SQL Server's MERGE (same with the new Postgres implementation). Last I checked neither SQL Server nor Oracle address the fact that MERGE pretty much doesn't do what many of its users imagine it can do. (There is a narrow theoretical sense in which it does meet expectations provided serializable isolation level is used, but in practice that isn't worth much to users.)
> anyway my experience has been that adding the lock hints "fixes" the problem with pk violations during concurrent upserts and it seemed to be commonly known and widely used afaict
That's not my impression:
https://www.mssqltips.com/sqlservertip/3074/use-caution-with...
Because of all the potential edge cases that MERGE didn't handle properly when introduced to SQL Server (2008?), I've avoided its use. Despite several revisions over the years there are apparently still significant behaviours that I'd consider bugs¹, some of which have been closed as WONTFIX² so are a fact of life you just have to accept if you use MERGE in SQL Server.
[1] see https://sqlsunday.com/2021/05/04/how-merge-can-deadlock-you/ & https://michaeljswart.com/2021/08/what-to-avoid-if-you-want-... and many other similar articles
[2] https://www.mssqltips.com/sqlservertip/3074/use-caution-with...
> Interference with upgrades: today's helpful hints become anti-performance after an upgrade.
> Encouraging bad DBA habits slap a hint on instead of figuring out the real issue. Does not scale with data size: the hint that's right when a table is small is likely to be wrong when it gets larger.
> Failure to actually improve query performance: most of the time, the optimizer is actually right.
> Interfering with improving the query planner: people who use hints seldom report the query problem to the project.
I like Postgresql and use it when I can and I respect the Postgreql core dev team's adherence to principles but in this particular case I believe they are just inventing arguments to justify their unreasonable stance against allowing query hinting.
my favorite response to someone complaining about a query doing a sequential scan is to tell them to `set enable_seqscan = false;` and rerun the query. usually, Postgres is right. it's rare that it is wrong, but it's nice to have a workaround for those few times that it's wrong - at least on fairly static data.
Test from both warm and cold states. Warm because most queries you are optimising are those that run often so usually do have some or all of the required data in RAM, and cold because sometimes they will run cold in production (and some queries just touch so much data, and/or cause spooling to disk of intermediate steps, so that IO will always be involved).
Ignore anyone who says always test warm because that is the most common state by far: if your query is reading far more than it really needs to when IO is involved, it will be using much more CPU than it needs to at other times and will harm concurrency potential (directly by eating CPU when other things could use it instead, or through holding more read locks than needed and holding them longer than necessary).
complex cases, otoh, usually aren't approached with that much naivety.
I don't like being woken up to debug why the query planner has decided to do something new. Yes, there's always a "good reason" but does it need to communicate that by taking down production at 3am?
Even if it's a better plan, I don't need my anomaly detection alerts going off because something is now running in half the time...
Having a system send warnings about CPU or query time are great when I am awake. They are hints to a problem, but not always a problem themselves.
This statement is shockingly user-hostile and paints the users overall as selfish and uninterested in the improvement of the postgresql project. Certainly some users will use their hint and go home, but you can't know that this would result in a net decrease in performance reports. Maybe with hints different users would be empowered to submit more reports like "The query planner creates a slow plan for query X, I know because when I use hint Z it's faster". Not implementing hints just to spite selfish users while ignoring the potential additional reports submitted by different caring users is shortsighted and antisocial.
Maybe there are a bunch of good reasons why hints should not be implemented, but this is not one of them.
My past commentary: https://news.ycombinator.com/item?id=29987139
Often people reach for index hints when they should be fixing issues in the query or the available indexes, swapping a scan for many seeks which on small data does give a benefit but once code goes into production and the data grows performance falls through the floor. Also if they are named in the hint then you open up a new family of errors if the index gets altered later (though you could perhaps implement the hint as “use an index on columns x & y” rather than “use this specific named index” to get around this).
There are circumstances where they are a genuinely useful tool, of course, but not nearly as many as people seem to think.
Beyond that I assume implementation will have difficult points. If the hints are taken as instructions it may require significant changes to the rest of the generated query plan, if they are sometimes ignored then people will complain bitterly…