plsql how to use pgx rewrit？plsql的存过怎么用pgx重写？

[wonb168]

现在用plsql写的跑的不快，想试试rust写。但不知道这种用rust咋写？ 里面的逻辑大概是： My greenplum's functions like following code, and is very slowly, I want to use pgx to rewrite it, but how to rewrite these sqls use rust?

create temp table a as ...;
create temp table b as select * from a join ... ; --table b need table a
... a lot of steps......
insert into t select * from b;

[roseduan]

中式英语，博大精深

[eeeebbbbrrrr]

pgx provides access to Postgres' SPI. You can try using that. An example of using SPI is here: https://github.com/tcdi/pgx/tree/master/pgx-examples/spi

[workingjubilee]

@roseduan: 非常. @wonb168: Using SPI is a starting option, but Rust may not make it faster. PostgreSQL can reach optimal performance on many queries, and products like Greenplum DB unlock even more parallelism. PGX may let you overcome speed blocks in Postgres, but it will only help if you know where the blockages are. You should try using EXPLAIN ANALYZE on some experimental queries first, maybe in a transaction, like maybe

SET auto_explain.log_nested_statements = ON;
BEGIN TRANSACTION;
EXPLAIN ANALYZE SELECT
    --your SQL here
;
ROLLBACK;

Then you can get to know what roadblocks PostgreSQL might be hitting. Then you may be able to find ways to make it go faster. Also see PostgreSQL's own performance tips.

[workingjubilee]

I've seen a few variations on this question appear over time. It's common enough that we should at least provide this much information in our documentation, especially as a form of expectation management: in many cases PGX can get bottlenecked on Postgres APIs and thus we should caution people to not automatically expect it to go faster than Postgres itself.