3 ms·

Join and index hints. I know the postgres devs don't like them, and that the query planner should be good enough that they're not needed, but it's not, and it

Join and index hints. I know the postgres devs don't like them, and that the query planner should be good enough that they're not needed, but it's not, and it regularly fucks up.

SigmundA 4y ago
+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.
- bb88 4y ago
  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).
riku_iki 4y ago
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.
- derefr 4y ago
  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.)
  - taffer 4y ago
    > 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 isn't this exactly how temp 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-resource.html#GUC-TEMP-BUFFERS https://www.postgresql.org/docs/current/runtime-config-resou...
    - riku_iki 4y ago
      that one is for some access buffers and not actual temp tables?
      - taffer 4y ago
        No temp_buffers is for temp tables and materialized CTEs. Maybe you are confusing it with shared_buffers?
        riku_iki 4y ago
        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.
    - derefr 4y ago
      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.
- SigmundA 4y ago
  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-transact-sql-query?view=sql-server-ver16#force-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!
- aeyes 4y ago
  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.
  - riku_iki 4y ago
    > 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.
derefr 4y ago
I would go further: give me a PL/ language (or an equivalent bytecode-abstract-machine abstraction) that lets me program — at least in a read-only capacity — directly against the access-method handles, such that the DB's table heap-file pages, index B-tree nodes, locks, etc. are "objects" I can manipulate, pass to functions, and navigate graph-wise (i.e. ask a table for its partitions; ask a partition for its access-method; type-assert the access-method as a heap file; ask for the pages as a random-access array; probe some pages for row-tuples; iterate those, de-serializing and de-toasting them as a type-cast; and then implement some efficient search or sampling on those tuples. Basically the code you'd write in a PG extension to interact with the storage engine on that level, but limited to only "safe" actions against the storage, and so able to be directly user-exposed.) 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.
- cdcarter 4y ago
  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 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.
  - derefr 4y ago
    > 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.
ddorian43 4y ago
https://github.com/ossc-db/pg_hint_plan https://github.com/ossc-db/pg_hint_plan
- biehl 4y ago
  Wow. That seems awesome. Do lots of people use it? Is it reliable?
  - ddorian43 4y ago
    Yes, it works. It's included by default in YugabyteDB as an example.
ttfkam 4y ago
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/AuroraPostgreSQL.Optimize.overview.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide...
hholzgra 4y ago
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

Join and index hints. I know the postgres devs don't like them, and that the query planner should be good enough that they're not needed, but it's not, and it regularly fucks up.

SigmundA 4y ago

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

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

riku_iki 4y ago

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.

derefr 4y ago
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.)
- taffer 4y ago
  > 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 isn't this exactly how temp 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-resource.html#GUC-TEMP-BUFFERS https://www.postgresql.org/docs/current/runtime-config-resou...
  - riku_iki 4y ago
    that one is for some access buffers and not actual temp tables?
    - taffer 4y ago
      No temp_buffers is for temp tables and materialized CTEs. Maybe you are confusing it with shared_buffers?
      - riku_iki 4y ago
        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.
  - derefr 4y ago
    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.
SigmundA 4y ago
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-transact-sql-query?view=sql-server-ver16#force-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!
aeyes 4y ago
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.
- riku_iki 4y ago
  > 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.

derefr 4y ago

I would go further: give me a PL/ language (or an equivalent bytecode-abstract-machine abstraction) that lets me program — at least in a read-only capacity — directly against the access-method handles, such that the DB's table heap-file pages, index B-tree nodes, locks, etc. are "objects" I can manipulate, pass to functions, and navigate graph-wise (i.e. ask a table for its partitions; ask a partition for its access-method; type-assert the access-method as a heap file; ask for the pages as a random-access array; probe some pages for row-tuples; iterate those, de-serializing and de-toasting them as a type-cast; and then implement some efficient search or sampling on those tuples. Basically the code you'd write in a PG extension to interact with the storage engine on that level, but limited to only "safe" actions against the storage, and so able to be directly user-exposed.) 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.

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

ddorian43 4y ago

https://github.com/ossc-db/pg_hint_plan https://github.com/ossc-db/pg_hint_plan

biehl 4y ago
Wow. That seems awesome. Do lots of people use it? Is it reliable?
- ddorian43 4y ago
  Yes, it works. It's included by default in YugabyteDB as an example.

ttfkam 4y ago

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/AuroraPostgreSQL.Optimize.overview.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide...

hholzgra 4y ago

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