4 ms·

Looks like ecto went too far by using named prepared statement by default? Should work just fine with unnamed prepared statement: https://github.com/elixir-ecto

Looks like ecto went too far by using named prepared statement by default? Should work just fine with unnamed prepared statement: https://github.com/elixir-ecto/postgrex/blob/master/README.md#pgbouncer https://github.com/elixir-ecto/postgrex/blob/master/README.m... Postgres planner is very fast, so there will be no noticeable performance difference by having to plan the same query again and again. This is unlike, say MSSQL, where a semi-complex query may take more than 0.5s to parse/plan, thus requiring the rather lousy query plan cache.

anarazel 7y ago
> Postgres planner is very fast, so there will be no noticeable performance difference by having to plan the same query again and again. This is unlike, say MSSQL, where a semi-complex query may take more than 0.5s to parse/plan, thus requiring the rather lousy query plan cache. That's not true. For a simple read only oltp workload (postgres' pgbench -S), the difference between prepared and non-prepared is significant: pgbench -M simple -P1 -n -S -T 10 -j72 -c72 ... tps = 585582.911949 (excluding connections establishing) vs pgbench -M prepared -P1 -n -S -T 10 -j72 -c72 ... tps = 1010152.078646 (excluding connections establishing) For more complicated queries the difference can be considerably bigger.
- throwdbaaway 7y ago
  Thanks for the numbers. Can you run it for -M extended as well? That would be the case when using unnamed prepared statement. Anyway, as there is no way for a query plan cache to be one-size-fit-all, as shown by both TFA and some comments in this HN discussion, that slight overhead is more than acceptable IMO, especially when compared to MSSQL.
  - anarazel 7y ago
    > Thanks for the numbers. Can you run it for -M extended as well? That would be the case when using unnamed prepared statement. Those are worse, due to the increased number of protocol messages (parse/bind/execute vs exec). tps = 491218.039129 (excluding connections establishing) > that slight overhead is more than acceptable IMO, especially when compared to MSSQL. I wouldn't call a 2x performance difference slight... That's also with a lot of context switching overhead - if you have a client using pipelined execution, the difference also gets bigger.
    - throwdbaaway 7y ago
      Good point about the "slight" wording, though I don't necessary agree that it can get much worse than 2x, with actual workload that: - could be more I/O bound than when just doing PK lookups with pgbench - could be getting a wrong plan from the query plan cache if named prepared statement is used

anarazel 7y ago

> Postgres planner is very fast, so there will be no noticeable performance difference by having to plan the same query again and again. This is unlike, say MSSQL, where a semi-complex query may take more than 0.5s to parse/plan, thus requiring the rather lousy query plan cache. That's not true. For a simple read only oltp workload (postgres' pgbench -S), the difference between prepared and non-prepared is significant: pgbench -M simple -P1 -n -S -T 10 -j72 -c72 ... tps = 585582.911949 (excluding connections establishing) vs pgbench -M prepared -P1 -n -S -T 10 -j72 -c72 ... tps = 1010152.078646 (excluding connections establishing) For more complicated queries the difference can be considerably bigger.

throwdbaaway 7y ago
Thanks for the numbers. Can you run it for -M extended as well? That would be the case when using unnamed prepared statement. Anyway, as there is no way for a query plan cache to be one-size-fit-all, as shown by both TFA and some comments in this HN discussion, that slight overhead is more than acceptable IMO, especially when compared to MSSQL.
- anarazel 7y ago
  > Thanks for the numbers. Can you run it for -M extended as well? That would be the case when using unnamed prepared statement. Those are worse, due to the increased number of protocol messages (parse/bind/execute vs exec). tps = 491218.039129 (excluding connections establishing) > that slight overhead is more than acceptable IMO, especially when compared to MSSQL. I wouldn't call a 2x performance difference slight... That's also with a lot of context switching overhead - if you have a client using pipelined execution, the difference also gets bigger.
  - throwdbaaway 7y ago
    Good point about the "slight" wording, though I don't necessary agree that it can get much worse than 2x, with actual workload that: - could be more I/O bound than when just doing PK lookups with pgbench - could be getting a wrong plan from the query plan cache if named prepared statement is used