5 ms·

Although absolute sacrilege to many, but I would honestly support a really explicit syntax to compose particular tables and indices. Not even in top of regular

Although absolute sacrilege to many, but I would honestly support a really explicit syntax to compose particular tables and indices. Not even in top of regular SQL query, I mean its own first class language.. E.g. "Take this index, restrict to this subrange; for each row, pick a row using this other index on the foreign key" (but it wouldn't have to be imperative like I've phrased it). There are times when I already know how I want the query to execute, and I have to iteratively fiddle with the query to indirectly trick the planner into doing that. There's just no excuse for that arrangement. Even if I've made a mistake and my plan couldn't work efficiently – at least let me execute it inefficiently so I can discover and debug that.

SigmundA 5y ago
Yes I would too. Over time I have come to appreciate at least being able to control order of operations, just forcing join order is many times enough of hint to keep things working properly. Having used Linq a lot I would actually prefer more of that kind of chained statement approach that is more hands on without having to explicitly loop.
derefr 5y ago
Or, to put that another way: Postgres's query planner should be emitting an explicit bytecode that is then interpreted by a query-execution abstract machine. That same layer should also be exposed directly to clients over the network, allowing pre-planned query bytecode to be submitted directly in place of a textual query. I've heart the rebuttal to this idea before, that even minor DB schema/configuration changes that should be "invisible" to the application (e.g. an index being created — or even refreshed — on the table) would result in the resulting bytecode being different; and so you'd never be able to ship such bytecode as a static artifact in your client. But: who said anything about a static artifact? I'm more imagining a workflow like this: 1. user submits a query with leading command "PLAN" (e.g. "PLAN SELECT ...") 2. Postgres returns planned bytecode (as a regular single-column bytea rowset) 3. if desired, the user modifies the received bytecode arbitrarily (leaving alone anything the client doesn't understand — much like how optimization passes leave alone generated intrinsics in LLVM IR) 4. user later submits a low-level query-execution command over the binary wire protocol, supplying the (potentially modified) bytecode 5. if the bytecode's assumptions are still valid, the query executes. Otherwise, the query gives a "must re-plan" error. The client is expected to hold onto the original SQL and re-supply it in this case. In other words, do it similarly to how Redis's EVAL + EVALSHA works; but instead of opaque handles (SHA hashes), the client receives a white-box internal representation. Or: do it similarly to how compiling a shader on a game-console with unikernel games + unified RAM/VRAM works. The driver's shader compiler spits you back a buffer full of some GPU object code — which, running in kernel-mode, you're free to modify before running.
- SigmundA 5y ago
  PostgreSQL doesn't even cache its own byte code, it replans every time!
  - mst 5y ago
    Depending on the placeholder values the optimal plan may be very different. You -can- use a server side prepared statement to force it to -ahem- plan ahead but that's -usually- not actually worth it.
    - SigmundA 5y ago
      Thats why other databases have parameter sniffing. And using prepared statements only works on the same connection so of limited use. Luckily PostgreSQL's optimizer is very primitive and so planning doesn't take too much time, as it gets more advanced the lack of plan reuse will become more of an issue. Its already an issue with the LLVM JIT compilation time.
- tshaddox 5y ago
  You can't ship SQL queries and then do arbitrary schema changes either, right?
  - lazide 5y ago
    Not arbitrary changes, but nearly so - if you add new columns (and your existing queries name the columns they use explicitly), then nothing changes as far as the client is concerned. If you remove or rename one, only those clients who refer to it need to change, and then only if you don’t do a interposing view/schema/etc. The server is supposed to take the logical intent specified by the queries and do the work to mapping that into concrete retrievals in a stable and performant way, so a query explicitly telling the server to do something in a specific way breaks the model. The more specific, the more broken. DDL changes for instance ideally would not have to tell the server how to structure things on disk. The server should know how to do it efficiently. That said, real life intrudes and sometimes (but not usually) it’s important to do this for stability or performance reasons. The more magic involved, the more unpredictable a system can be, and the closer a system runs to redline, the more chaos/downtime that can cause.
gfody 5y ago
something like sql server's "use plan" it really comes in handy when you need it (https://forrestmcdaniel.com/2018/07/19/how-to-use-the-use-plan-hint https://forrestmcdaniel.com/2018/07/19/how-to-use-the-use-pl...)
orthoxerox 5y ago
Clickhouse doesn't go that way all the way, but its approach to SQL support is very similar: it does exactly what you have written. It doesn't rewrite your query in any way: if you want to filter table A before you join it with table B, you write a subquery.
magicalhippo 5y ago
There's no technical reason why one couldn't just select or join from an index as if it was a table or a view is there? We also write queries knowing it will use a specific index, or we will create an index because of a specific query. And then we have to have a scheduled task to periodically recalculate statistics just so the DB server doesn't get silly ideas. Of course it could be abused, but I'm in favor of having ways of letting programmers tell computers exactly what to do. Sometimes we really do know best.

SigmundA 5y ago

Yes I would too. Over time I have come to appreciate at least being able to control order of operations, just forcing join order is many times enough of hint to keep things working properly. Having used Linq a lot I would actually prefer more of that kind of chained statement approach that is more hands on without having to explicitly loop.

derefr 5y ago

Or, to put that another way: Postgres's query planner should be emitting an explicit bytecode that is then interpreted by a query-execution abstract machine. That same layer should also be exposed directly to clients over the network, allowing pre-planned query bytecode to be submitted directly in place of a textual query. I've heart the rebuttal to this idea before, that even minor DB schema/configuration changes that should be "invisible" to the application (e.g. an index being created — or even refreshed — on the table) would result in the resulting bytecode being different; and so you'd never be able to ship such bytecode as a static artifact in your client. But: who said anything about a static artifact? I'm more imagining a workflow like this: 1. user submits a query with leading command "PLAN" (e.g. "PLAN SELECT ...") 2. Postgres returns planned bytecode (as a regular single-column bytea rowset) 3. if desired, the user modifies the received bytecode arbitrarily (leaving alone anything the client doesn't understand — much like how optimization passes leave alone generated intrinsics in LLVM IR) 4. user later submits a low-level query-execution command over the binary wire protocol, supplying the (potentially modified) bytecode 5. if the bytecode's assumptions are still valid, the query executes. Otherwise, the query gives a "must re-plan" error. The client is expected to hold onto the original SQL and re-supply it in this case. In other words, do it similarly to how Redis's EVAL + EVALSHA works; but instead of opaque handles (SHA hashes), the client receives a white-box internal representation. Or: do it similarly to how compiling a shader on a game-console with unikernel games + unified RAM/VRAM works. The driver's shader compiler spits you back a buffer full of some GPU object code — which, running in kernel-mode, you're free to modify before running.

SigmundA 5y ago
PostgreSQL doesn't even cache its own byte code, it replans every time!
- mst 5y ago
  Depending on the placeholder values the optimal plan may be very different. You -can- use a server side prepared statement to force it to -ahem- plan ahead but that's -usually- not actually worth it.
  - SigmundA 5y ago
    Thats why other databases have parameter sniffing. And using prepared statements only works on the same connection so of limited use. Luckily PostgreSQL's optimizer is very primitive and so planning doesn't take too much time, as it gets more advanced the lack of plan reuse will become more of an issue. Its already an issue with the LLVM JIT compilation time.
tshaddox 5y ago
You can't ship SQL queries and then do arbitrary schema changes either, right?
- lazide 5y ago
  Not arbitrary changes, but nearly so - if you add new columns (and your existing queries name the columns they use explicitly), then nothing changes as far as the client is concerned. If you remove or rename one, only those clients who refer to it need to change, and then only if you don’t do a interposing view/schema/etc. The server is supposed to take the logical intent specified by the queries and do the work to mapping that into concrete retrievals in a stable and performant way, so a query explicitly telling the server to do something in a specific way breaks the model. The more specific, the more broken. DDL changes for instance ideally would not have to tell the server how to structure things on disk. The server should know how to do it efficiently. That said, real life intrudes and sometimes (but not usually) it’s important to do this for stability or performance reasons. The more magic involved, the more unpredictable a system can be, and the closer a system runs to redline, the more chaos/downtime that can cause.

gfody 5y ago

something like sql server's "use plan" it really comes in handy when you need it (https://forrestmcdaniel.com/2018/07/19/how-to-use-the-use-plan-hint https://forrestmcdaniel.com/2018/07/19/how-to-use-the-use-pl...)

orthoxerox 5y ago

Clickhouse doesn't go that way all the way, but its approach to SQL support is very similar: it does exactly what you have written. It doesn't rewrite your query in any way: if you want to filter table A before you join it with table B, you write a subquery.

magicalhippo 5y ago

There's no technical reason why one couldn't just select or join from an index as if it was a table or a view is there? We also write queries knowing it will use a specific index, or we will create an index because of a specific query. And then we have to have a scheduled task to periodically recalculate statistics just so the DB server doesn't get silly ideas. Of course it could be abused, but I'm in favor of having ways of letting programmers tell computers exactly what to do. Sometimes we really do know best.