4 ms·
Did you try using prepared statement so the compilation is done once and compiled results are reused each time that query is run?
by czl 3y ago
Did you try using prepared statement so the compilation is done once and compiled results are reused each time that query is run?
- fabian2k 3y agoAs far as I understand prepared statements don't help here as the JIT output is not saved but generated for each execution. In this case I'm also using an ORM (EF Core) which doesn't expose the ability to prepare statements.
- trollied 3y agoMost optimisers cache the execution plan based on a hash of the query, hence reuse when using prepared statements vs not. Oracle has an option to detect literals (CURSOR_SHARING) and essentially replace them with binds internally, in order to increase performance and stop the query pool getting filled up with the same statement(s).
- davidrowley 3y agoPostgres does not currently reuse JIT-compiled code. JIT will run each execution of the query. This may change in the next few years, likely starting with tuple deforming, as that's fairly reusable, per table for any query.
- dur-randir 3y agoThere's a separate can of worms with prepared statements. Two main are: - parameters are opaque to planner, so it prefers (or is even forced to?) to choose generic vs specific plans - it doesn't play nice with pg_bouncer in transaction mode
- jeltz 3y agoPgbouncer supports prepared statements these days.
- SigmundA 3y agoOnly in the same session...