5 ms·

Its was actually very surprising to me to find PG does not cache query plans but instead replans on every execution! I am used to db's like MSSQL server that ca

Its was actually very surprising to me to find PG does not cache query plans but instead replans on every execution! I am used to db's like MSSQL server that cache and reuse plans making things like prepared statements redundant. My understanding is the planner is so simple that it is deemed not necessary, unless of course it is then do a prepared statement which only works on one connection... This would absolute trash performance on SQL server to not cache plans as its planner spends significant time optimizing.

deleted 5y ago
[deleted]
winrid 5y ago
I did not know PG lacked a plan cache. I guess if the planner is fast enough trying to build cache keys from a query will just slow things down.
- SigmundA 5y ago
  If the planner is faster than a cache lookup it makes me wonder how sophisticated it is and how much optimization is left on the table. Most apps I deal with queries are very regular and the plan cache makes it act like a prepared statement improving performance 99% of the time. Once in a while it works against you and you need to give it a hint to recompile or optimize for specific parameter values.
baq 5y ago
MSSQL plan cache is something you need to be aware of, it will work against you if statistics change but the plan remains the same. this is especially true if you start with a small amount of data, get a plan cached and then bulk load lots after testing. foot = shot
- SigmundA 5y ago
  Normally AUTO_UPDATE_STATISTICS will take care of recompiling plans if statistics changing significantly. I usually have the opposite problem with a good plan changing to a bad one due to statistics changing at some random time in production, which seems to be a common problem in PG as well and why everyone want hints. I prefer hinting for critical problematic queries that tend to have unstable plans due to statistics changes, but you can also lock a plan in SQL server to force a specific plan, another useful feature to prevent production db instability.
  - baq 5y ago
    Agree completely on the plan changing from good to bad after some kind of analyze going awry. Had this happen multiple times on mssql and postgres. Our workaround was to perform analyze periodically multiple times a day since we could afford it.
vbezhenar 5y ago
How does it work when parameter value changes plan? Simple example: `where name like ?`. If parameter is 'abc%', then you can use index. If parameter is '%abc', you can't use index.
- briHass 5y ago
  You can use a statment 'OPTION RECOMPILE' to force the query plan to be rebuilt with every execution. A similar thing can be done with stored procedures, which are also aggressively plan cached.
- SigmundA 5y ago
  It's uses the index either way, specifically it still index seeks but ends up doing the same thing as an index/table scan although slower. It normally makes a plan based on first execution and uses parameter sniffing, so it might optimize for no index seek if the first request has wildcard in front. You can pass it a hint OPTION (RECOMPILE) that will make it act like PG and make new plan every time, if its a query with wildly varying parameters that doesn't execute as often this can make sense, most of the time it doesn't due to planning overhead. There is also the OPTIMIZE FOR hint that can have it make a plan optimized for the more common of the parameter values if you don't want it planning every execution regardless of value passed on first execute.
brasetvik 5y ago
> Its was actually very surprising to me to find PG does not cache query plans but instead replans on every execution! If you used prepared statements, `plan_cache_mode` (since Postgres >=12) can be configured. It defaults to auto, which will generate a generic plan after 5 (iirc) executions. If set to `force_generic_plan`, it'll make a generic plan right away. If set to `force_custom_plan`, it'll always do planning. The latter can be useful for queries where the parameters of the prepared statement can have very different selectivities.
- SigmundA 5y ago
  Prepared statements are only on a single connection (Session), its actually surprising that PG doesn't even cache prepared statements always! In MS SQL server plans are always cached unless told not to this is across connections, if it sees the same sql statement its will skip planning, this goes way back. If you go back far enough it only did that for stored procedures so there was a push to do everything through sprocs for performance, not so much in the last 20 years. Using prepared statements is for backward compatibility as is has almost no performance advantage due to modern plan caching. I think PG finally has sprocs but still no plan caching even for them unless somethings changed. PG has no way to share plans between worker processes AKAIK. I believe the lack of plan caching is noticed more with heavy jit optimizing performance where PG will use LLVM now which has a relatively high cost.
  - okr 5y ago
    One could argue, always caching plans could be costly resource wise and you need to administer timeouts and cache sizes, while when associated with a session, you get the cleanup for free.

deleted 5y ago

[deleted]

winrid 5y ago

I did not know PG lacked a plan cache. I guess if the planner is fast enough trying to build cache keys from a query will just slow things down.

SigmundA 5y ago
If the planner is faster than a cache lookup it makes me wonder how sophisticated it is and how much optimization is left on the table. Most apps I deal with queries are very regular and the plan cache makes it act like a prepared statement improving performance 99% of the time. Once in a while it works against you and you need to give it a hint to recompile or optimize for specific parameter values.

baq 5y ago

MSSQL plan cache is something you need to be aware of, it will work against you if statistics change but the plan remains the same. this is especially true if you start with a small amount of data, get a plan cached and then bulk load lots after testing. foot = shot

SigmundA 5y ago
Normally AUTO_UPDATE_STATISTICS will take care of recompiling plans if statistics changing significantly. I usually have the opposite problem with a good plan changing to a bad one due to statistics changing at some random time in production, which seems to be a common problem in PG as well and why everyone want hints. I prefer hinting for critical problematic queries that tend to have unstable plans due to statistics changes, but you can also lock a plan in SQL server to force a specific plan, another useful feature to prevent production db instability.
- baq 5y ago
  Agree completely on the plan changing from good to bad after some kind of analyze going awry. Had this happen multiple times on mssql and postgres. Our workaround was to perform analyze periodically multiple times a day since we could afford it.

vbezhenar 5y ago

How does it work when parameter value changes plan? Simple example: `where name like ?`. If parameter is 'abc%', then you can use index. If parameter is '%abc', you can't use index.

briHass 5y ago
You can use a statment 'OPTION RECOMPILE' to force the query plan to be rebuilt with every execution. A similar thing can be done with stored procedures, which are also aggressively plan cached.
SigmundA 5y ago
It's uses the index either way, specifically it still index seeks but ends up doing the same thing as an index/table scan although slower. It normally makes a plan based on first execution and uses parameter sniffing, so it might optimize for no index seek if the first request has wildcard in front. You can pass it a hint OPTION (RECOMPILE) that will make it act like PG and make new plan every time, if its a query with wildly varying parameters that doesn't execute as often this can make sense, most of the time it doesn't due to planning overhead. There is also the OPTIMIZE FOR hint that can have it make a plan optimized for the more common of the parameter values if you don't want it planning every execution regardless of value passed on first execute.

brasetvik 5y ago

> Its was actually very surprising to me to find PG does not cache query plans but instead replans on every execution! If you used prepared statements, `plan_cache_mode` (since Postgres >=12) can be configured. It defaults to auto, which will generate a generic plan after 5 (iirc) executions. If set to `force_generic_plan`, it'll make a generic plan right away. If set to `force_custom_plan`, it'll always do planning. The latter can be useful for queries where the parameters of the prepared statement can have very different selectivities.

SigmundA 5y ago
Prepared statements are only on a single connection (Session), its actually surprising that PG doesn't even cache prepared statements always! In MS SQL server plans are always cached unless told not to this is across connections, if it sees the same sql statement its will skip planning, this goes way back. If you go back far enough it only did that for stored procedures so there was a push to do everything through sprocs for performance, not so much in the last 20 years. Using prepared statements is for backward compatibility as is has almost no performance advantage due to modern plan caching. I think PG finally has sprocs but still no plan caching even for them unless somethings changed. PG has no way to share plans between worker processes AKAIK. I believe the lack of plan caching is noticed more with heavy jit optimizing performance where PG will use LLVM now which has a relatively high cost.
- okr 5y ago
  One could argue, always caching plans could be costly resource wise and you need to administer timeouts and cache sizes, while when associated with a session, you get the cleanup for free.