4 ms·

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

by rtpg 4y ago

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”

kevan 4y ago
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.
- ako 4y ago
  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.
- daigoba66 4y ago
  MSSQL mostly has this feature with Query Store.
- iracic 4y ago
  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?
- protomyth 4y ago
  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.
throw868788 4y ago
> "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.
Blackthorn 4y ago
That would be a huge compatibility nightmare any time you upgraded database versions.
- branko_d 4y ago
  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.
felixge 4y ago
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 : ).

by rtpg 4y ago

kevan 4y ago

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.

ako 4y ago
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.
daigoba66 4y ago
MSSQL mostly has this feature with Query Store.
iracic 4y ago
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?
protomyth 4y ago
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.

throw868788 4y ago

> "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.

Blackthorn 4y ago

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

branko_d 4y ago
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.

felixge 4y ago

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 : ).