CODEWITHSUNDEEP
postgresqlpostgresql-12
Werner Raath
Werner Raath

Reputation: 1492

Postgres EXPLAIN ANALYSE Planning Time slow for first query per connection

When running the query the first time in psql, it is a bit slow. The second time it's a lot faster since the Planning Time goes down substantially.

> EXPLAIN ANALYSE SELECT * FROM public.my_custom_function(10, 10, 'Personal');

The first time:

                                                                QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on my_custom_function  (cost=0.25..10.25 rows=1000 width=32) (actual time=4.900..4.901 rows=1 loops=1)
 Planning Time: 30.870 ms
 Execution Time: 3.410 ms
(3 rows)

All subsequent queries:

                                                                QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on my_custom_function  (cost=0.25..10.25 rows=1000 width=32) (actual time=4.900..4.901 rows=1 loops=1)
 Planning Time: 0.620 ms
 Execution Time: 4.920 ms
(3 rows)

This is the case any time I make a new connection to the DB, the first call has considerable Planning Time and all others are fine.

Additional Context

Deployment: Docker

Postgres version: 12

SQL logic: Does Indexed JOINs and WHERE lookups. I know logic there is fast and solid and it's not the query itself that needs to be optimised.

Whether I run the query by itself or via the function, the same Planning Time issue remains.

Problem:

I have an HTTP API making a connection per request, calling the function once and then returning. Hence every API request has the performance of a non-planned query.

Question:

How can I make this query be Planned for once and never again? Maybe using a PREPARE statement?

Upvotes: 0

Views: 912

Answers (1)

jjanes
jjanes

Reputation: 44137

While there might be ways to speed this up (if we could see your function), fundamentally if you are very sensitive to performance, then you need to choose some technology that doesn't make one connection per request. Like mod_perl or FastCGI or maybe pgbouncer.

Upvotes: 3

Related Questions