+1, planner is great and all, but I know better sometimes, let me tell it so and have it listen.

Nothing like the planner deciding it knows better at some random time in production because of data changes.

The argument from the camera folks goes like this:

Why should a camera's software written 5 years ago in Japan/China/Taiwan choose for me with the lighting conditions I have right now in Seoul at 2:30 in the morning?

That's why most professional prefer to use a manual mode. Auto is often used as a first suggestion (but not a very good first suggestion).

You usually can manage this by dividing your query into subqueries each creating some temp table, so you have control over how joining actually happens.

It's a shame, then, that there's no way to define at-first-purely-in-memory tables, which only "spill" to disk if they cause your query to exceed work_mem.

Within PL/pgSQL, CREATE TEMPORARY TABLE is still (sometimes a lot!) slower than just SELECTing an array_agg(...) INTO a variable, and then `SELECT ... FROM unnest(that_variable) AS t` to scan over it. (And CTEs with MATERIALIZED are really just CREATE TEMPORARY TABLE in disguise, so that's no help.)

> It's a shame, then, that there's no way to define at-first-purely-in-memory tables, which only "spill" to disk if they cause your query to exceed work_mem.

But that isn't this exactly how temporary tables work? A temp tabe lives in memory and only spills to disk if it exceeds temp_buffers[1].

[1] https://www.postgresql.org/docs/current/runtime-config-resou...

that one is for some access buffers and not actual temp tables?

No temp_buffers is for temp tables and materialized CTEs. Maybe you are confusing it with shared_buffers?

I am trying to say that your link says it is for access buffer for accessing temp table, and it doesn't say actual temp table is stored in that buffer and not flushed on the disk.

Huh, I think you're right... but it's still slower! I've definitely measured this effect in practice.

Just spitballing here — I think the difference might come from where the metadata required to treat the table "as a table" in queries has to be entered into, and the overhead (esp. in terms of locking) required to do so.

Or, perhaps, it might come from the serialization overhead of converting "view" row-tuples (whose contents might be merged together from several actual material tables / function results) into flattened fully-materialized row-tuples... which, presumably, emitting data into an array-typed PL/pgSQL variable might get to skip, since the handles to the constituent data can be held inside the array and thunked later on when needed.

Temp tables are per session, there shouldn't be any locking involved.

I believe the metadata about them is still written to various system catalog tables. Creating lots of temp tables will cause autovacuum activity on tables like pg_attr, for example.

Or there could just be a hint that says do it in source code order:

https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-tr...

There could also be hints that tell what kind of join to use and which indexes to use!

If you do this be careful what your temp_buffers is set to so that your temp tables don't spill to disk. If you are on a network file system, for example on AWS RDS, writing big (a few hundred MB) temp tables to disk will stall all transactions.

The same is of course true when you have big joins that don't fit in work_mem but default size of this will be much larger.

I can usually fix bad plans with CTEs, no need to get much fancier. And the problem is often caused by schema design where you have a mapping table of two tables in the middle and your join is N-M-M where the planner has no information about the relationship between the two outer tables.

> temp tables don't spill to disk

my setup is local nvme ssd raid, so I hope this part won't be bottle neck. Also, if you are doing heavy join, where join order and method is need to be controlled, you temp table likely will be large, so you will need to be ready to have disk io.

I would go further: give me a PL/ language (or an equivalent bytecode-abstract-machine abstraction) that lets me program directly against the access-method handles, such that table heap-file pages, index B-tree nodes, etc. are "objects" I can manipulate and call methods on.

The closest analogy I know of to that, is how you work with ETS tables in Erlang. I want to send the RDBMS code that operates at that level!

Actually, I presume that SQLite would necessarily have some low-level C interface that works like this — but few people seem to talk about it/be aware of it compared to its high-level SQL-level interface.

SQLite compiles SQL to bytecode, and then executes that bytecode against the database. However, there's no public interface for creating/running bytecode directly, instead of as a result of a compiled statement. You almost certainly COULD do what you're trying to achieve, but the SQLite author's have specifically called it out as a bad idea - https://sqlite.org/forum/info/c695cbe47b955076 - since bytecode representation can change from release to release in a way that would only matter to the compiler (or to your weird hacked in interface). Meaning, non-portable.

> since bytecode representation can change from release to release in a way that would only matter to the compiler (or to your weird hacked in interface). Meaning, non-portable.

IIRC JVM static-analysis libraries get around this by essentially forcefully pulling in and reflecting upon the particular compiler release's internals that are being built against. The result is "non-portable", but only in the sense that it's getting tailored to the particular compiler release that's already concretely available in the build environment.

Mind you, that's a bit different, because you don't usually ship the compiler parts of the JDK as part of your application JAR; while SQLite does ship this compiler as part of the library. Would be fine, though, as long as your executable's embedding SQLite statically (or in a Docker image, etc) — in other words, vendoring the particular version of SQLite that matches the version the application-layer codegen library was compiled against.

This is vaguely how the Microsoft "Jet" database engines work. Microsoft Exchange uses this low-level query authoring technique to achieve its scalability and performance goals. This generally makes its performance consistent and predictable.

There were some attempts back in the early 2000s to move Exchange over to use the SQL Server RDBMS engine, and they added a bunch of features to enable this kind of low-level control. Not just join hints: you could force specific query plans by specifying the plan XML document for that query. See: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-tr...

This wasn't good enough however, and Exchange still uses the Jet database.

Something that might be interesting is an RDBMS "as a library", where instead of poking it with ASCII text queries, you get a full programming API surface where you can do exactly the type of thing you propose: perform arbitrary walks through data structures, develop custom indexes, or whatever.

I would love this too, and looked into building an extension for it a few years ago. IIRC the main challenge was that many features such as row level security are built straight into the current query executor, making it difficult to build this as a production grade tool. I could try to dig up my notes if you’re interested.

Wow. That seems awesome. Do lots of people use it? Is it reliable?

Yes, it works. It's included by default in YugabyteDB as an example.

AWS-only, but if you're running in that environment, you've got the apg_plan_mgmt extension.

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide...

There is also the use case where the planner comes up with the correct plan in the end, but the process could be sped up by giving it additional hints that lead it to the right result faster by restricting the search space up front