Why are PostgreSQL queries slower in the first query after the first new connection than during subsequent queries?
Using several different technologies to connect to the postgresql database. The first request may take 1.5 seconds. The exact request will take 0.3 seconds a second time. Open the second instance of my application (connecting to the same database) and the first request will take 1.5 seconds and the second .03 seconds.
Due to the different technologies that we use, they connect at different points and use different connection methods, so I really don't think this has anything to do with any code I wrote.
I think opening a connection does not do everything until the first request, so the request has some overhead.
Since I used the database and supported the server, everything is in memory, so the index, etc. should not be a problem.
Edit Explanation - tells me about the request, and frankly, the request looks good (indexed, etc.). I really think postgresql has some overhead on the first request for a new connection.
I do not know how to prove / disprove it. If I used PG Admin III (pgAdmin version 1.12.3), all requests seem to be fast. Any of the other tools, I have a first request, slow. In most cases, this is not noticeably slower, and if I did, I always did this to update the ram with the index. But this is clearly NOT. If I open my tool and make any other query that returns results, the second query is executed flawlessly. If the first query does not return results, the second is still slow and the third is fast.
edit 2 Although I don’t think the request has anything to do with the delay (every first request is slow), there are two results: Explain (EXPLAIN ANALYZE)
EXPLAIN ANALYZE select * from company where company_id = 39
Output:
"Seq Scan on company (cost=0.00..1.26 rows=1 width=54) (actual time=0.037..0.039 rows=1 loops=1)" " Filter: (company_id = 39)" "Total runtime: 0.085 ms"
and
EXPLAIN ANALYZE select * from group_devices where device_name ilike 'html5_demo' and group_id in ( select group_id from manager_groups where company_id in (select company_id from company where company_name ='TRUTHPT'))
exit:
"Nested Loop Semi Join (cost=1.26..45.12 rows=1 width=115) (actual time=1.947..2.457 rows=1 loops=1)" " Join Filter: (group_devices.group_id = manager_groups.group_id)" " -> Seq Scan on group_devices (cost=0.00..38.00 rows=1 width=115) (actual time=0.261..0.768 rows=1 loops=1)" " Filter: ((device_name)::text ~~* 'html5_demo'::text)" " -> Hash Semi Join (cost=1.26..7.09 rows=9 width=4) (actual time=0.297..1.596 rows=46 loops=1)" " Hash Cond: (manager_groups.company_id = company.company_id)" " -> Seq Scan on manager_groups (cost=0.00..5.53 rows=509 width=8) (actual time=0.003..0.676 rows=469 loops=1)" " -> Hash (cost=1.26..1.26 rows=1 width=4) (actual time=0.035..0.035 rows=1 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 1kB" " -> Seq Scan on company (cost=0.00..1.26 rows=1 width=4) (actual time=0.025..0.027 rows=1 loops=1)" " Filter: ((company_name)::text = 'TRUTHPT'::text)" "Total runtime: 2.566 ms"