[Proposal] prepared statements support for insert_all with a query

[Michał Łępicki]

Hi!

Currently insert_all belongs to the Schema part of the Ecto Repo API and calls adapter.insert_all which seems to just call insert, even when called with the query (under binding called rows here). The Ecto Repo Query API uses adapter.execute which handles the usage of named prepared statements: (I guess that's handled somewhere here).

I think I encountered a performance oddity where my insert_all query is slow, but it would be faster if it used prepared statements (I know it's counterintuitive because a generic plan shouldn't be faster than a plan for a specific query+parameters, maybe there's something wrong in my postgresql stats the planner uses, I'm not an expert in postgresql performance tuning). I would like to run an insert_all query from Ecto so that it executes the way a regular query would, with prepared statements. Maybe I'm missing something, is there a way to do that currently? (Maybe with some clever fragment usage?)

I asked about this on Slack and on elixirforum first, but now I think it's just not supported. Could there be a change so that `insert_all` called with a query dispatches/belongs to the Query API and uses prepared statements? Or could a new API be introduced for this use-case?

Thanks,
- Michał

[José Valim]

I believe you can pass cache_statement: "foo_bar" to insert_all (and all other repo operations): https://hexdocs.pm/postgrex/Postgrex.html#query/4

Give it a unique name and make sure you change the name whenever you change the query in any way.

If it doesn't work, then we should make it work. If it works, PRs to improve the docs is welcome!

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/be3efa50-50dc-41ee-ac7c-72b8de9e4ee2n%40googlegroups.com.