3 ms·

Looks like the plan caching is only per session which is a big bummer if you call the same statements over and over from multiple sessions. Plus you have manua

Looks like the plan caching is only per session which is a big bummer if you call the same statements over and over from multiple sessions. Plus you have manually deal with this which is weird coming from MSSQL where it just does it with a global cache based on statement text hash all the time. Brings me back to early 2000's when it was recommended to always use stored procedures because either MSSQL 7 or 2000 can't remember didn't cache ad-hoc statements only sprocs.

deleted 7y ago
[deleted]
manigandham 7y ago
Plan caching is different from prepared statements, but yes PG is not as advanced as other commercial databases in execution. Usually the database client drivers will handle this for you. If you're using .NET, the npgsql driver maintains a connection pool and creates prepared statements for each new session. If you have something like PGBouncer in the middle then it gets more complicated.
- SigmundA 7y ago
  There is almost no point is using prepared statements in MSSQL anymore, not for a long time. The only benefit comes from not sending the prepared statement over the wire each time and not having to hash it to do a plan lookup. That benefit is so very minor compared to the time taken to parse and plan the statement that almost none uses it, for more details: https://dba.stackexchange.com/questions/129659/what-is-the-sense-and-benefit-of-using-sqlcommand-prepare/129721#129721 https://dba.stackexchange.com/questions/129659/what-is-the-s... In PG its seems to be the only choice and on top of that isn't even shared among connections, this honestly blows my mind and will have to remember if I move anything serious over to PG. I like PG a lot and it does a lot of thing MSSQL doesn't but this is a big missing performance piece.
  - manigandham 7y ago
    I'm very familiar with both (and we use both) but plan caching isn't going to make that much of a difference for many scenarios. PG also has simpler planning than MSSQL anyway. I doubt you'd notice a difference unless you're extremely complex queries or a very high rate of transactions.

deleted 7y ago

[deleted]

manigandham 7y ago

Plan caching is different from prepared statements, but yes PG is not as advanced as other commercial databases in execution. Usually the database client drivers will handle this for you. If you're using .NET, the npgsql driver maintains a connection pool and creates prepared statements for each new session. If you have something like PGBouncer in the middle then it gets more complicated.

SigmundA 7y ago
There is almost no point is using prepared statements in MSSQL anymore, not for a long time. The only benefit comes from not sending the prepared statement over the wire each time and not having to hash it to do a plan lookup. That benefit is so very minor compared to the time taken to parse and plan the statement that almost none uses it, for more details: https://dba.stackexchange.com/questions/129659/what-is-the-sense-and-benefit-of-using-sqlcommand-prepare/129721#129721 https://dba.stackexchange.com/questions/129659/what-is-the-s... In PG its seems to be the only choice and on top of that isn't even shared among connections, this honestly blows my mind and will have to remember if I move anything serious over to PG. I like PG a lot and it does a lot of thing MSSQL doesn't but this is a big missing performance piece.
- manigandham 7y ago
  I'm very familiar with both (and we use both) but plan caching isn't going to make that much of a difference for many scenarios. PG also has simpler planning than MSSQL anyway. I doubt you'd notice a difference unless you're extremely complex queries or a very high rate of transactions.