Why doesn't DISCARD PLANS/ALL work for queries inside PLPGSQL functions?
0
votes
1
answer
185
views
I'm experiencing unexpected behavior with query plan caching in PostgreSQL when using different function types. I have a dashboard query that exhibits significantly different performance characteristics based on how it's implemented:
- Raw SQL Query: ~200ms execution time
- SQL Function: ~2s execution time
- PLPGSQL Function: First 5 calls: ~200ms, subsequent calls: ~2s
The most puzzling part is that
DISCARD PLANS/ALL
doesn't help with the PLPGSQL function performance degradation, even though it should clear the plan cache.
Each implementation uses the same SQL query, but in functions the query is parametrized. For example in functions there is condition like this: (_types is null or type = any (_types))
, which in plain SQL tests traslates to (null is null or type = any (null::int[]))
Environment
- database functions are used as a database interface for Node REST API
- API uses connection pooling in session mode (each qurey has its own session)
- After each query, the connection is returned to the pool and DISCARD ALL is executed
- PostgreSQL v13.13 running on CentOS 8
Questions
- Why doesn't DISCARD PLANS/ALL help with the PLPGSQL function's performance? It seems that it does not clear session state completely.
- Is there a way to maintain the original performance while keeping the query inside a PLPGSQL function?
Asked by DavidP
(203 rep)
Nov 6, 2024, 12:57 PM
Last activity: Nov 6, 2024, 01:19 PM
Last activity: Nov 6, 2024, 01:19 PM