Hacker News Reader

Comment by rtpg | Hacker News Reader

I would go further and say that there should be an alternate language that lets you just provide a planned path.

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”

View on HN

Agreed. A while ago I looked into hacking something like this into plv8. You'd be able to do something like:

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;

However, it turns out this would be a lot of work and it's difficult to make it work with features like row level security.

Since I didn't have enough time I dropped the idea, but it'd be awesome if somebody hacked this up at some point : ).

> "hey, system, go through this btree, pull these items, merge with items from this other btree, etc”

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.

Do any relational DBs let you lock statistics or the query planner directly? Something like how ML pipelines are split between training and inference phases. Let me train the optimizer on a representative load (prod replay or synthetic) and then lock the query planner for my production traffic. Still take advantage of the black magic but control it so query plans don't unexpectedly change.

Problem with databases is that they have the tendency to grow over time, so the statistics change all the time, and thus the optimal query plan.

MSSQL mostly has this feature with Query Store.

Yes, some are able to verify that execution plan already exists (saved in gather run or by explicit command) before replaning. Also you can group them with one label and activate only when needed.

I guess that there must be a good number of people who would use it in PostgreSQL. Anybody analyzed previous tries to implement it?

Sybase 11 and 12 let you force a plan and declare what indexes to use including temp tables if they were declared in a stored procedure that was calling the stored procedure you used the indexes. The optimizer was not the best.

That would be a huge compatibility nightmare any time you upgraded database versions.

Not really. The basic concepts of "tables/indexes" and "access paths" has been stable for years (decades really). So if you wrote something like: MY_TABLE.MY_INDEX.SEEK(whatever), that would have the same meaning as it did 30-40 years ago. Same for range scans, nested loop / merge / hash joins, sorting etc.

Reply on news.ycombinator.com