I have a complex postgres query that I've optimised with pg_hint_plan. Planning time is about 150ms while query time is about 30ms. The plan should never change, therefore there's no point in gathering statistics each any every time for each query. The structural problem with the query is that it hits too many tables.

Tweaking the join collapse limit and from select collapse limit has limited effect.

Most 'enterprise' databases have a shared query cache, but as far as I can see Postgres does not.

What are ways around this? Prepared statements aren't really suitable as their lifetime is bound to the connection.

CodePudding user response：

There is no way around this. The best solution I can think of is to use connection pooling, so that your connections live for a long time, and use a prepared statement.

CodePudding user response：

No PostgreSQL does not have a plan cache area like Microsoft SQL Server or Oracle...

It is one of the many differences with professional RDBMS, like SQL Server... For the last one, a complete comparison can be read here