Why PostgreSQL queries are slower in the first request after first new connection than during the subsequent requests?
为什么PostgreSQL查询在第一次新连接后的第一个请求中比在后续请求中慢?
Using several different technologies to connect to a postgresql database. First request might take 1.5 seconds. Exact same query will take .03 seconds the second time. Open a second instance of my application (connecting to same database) and that first request takes 1.5 seconds and the second .03 seconds.
使用几种不同的技术连接到postgresql数据库。第一次请求可能需要1.5秒。完全相同的查询将第二次花费.03秒。打开我的应用程序的第二个实例(连接到同一个数据库),第一个请求需要1.5秒,第二个请求需要.03秒。
Because of the different technologies we are using they are connecting at different points and using different connection methods so I really don't think it has anything to do with any code I have written.
由于我们使用不同的技术,他们在不同的点连接并使用不同的连接方法,所以我真的不认为它与我编写的任何代码有任何关系。
I'm thinking that opening a connection doesn't do 'everything' until the first request, so that request has some overhead.
我认为在第一个请求之前打开连接不会做“一切”,所以请求有一些开销。
Because I have used the database, and kept the server up everything is in memory so index and the like should not be an issue.
因为我已经使用了数据库,并且保留了服务器所有内容都在内存中所以索引等应该不是问题。
Edit Explain - tells me about the query and honestly the query looks pretty good (indexed, etc). I really think postgresql has some kind of overhead on the first query of a new connection.
编辑说明 - 告诉我有关查询的信息,老实说查询看起来很不错(索引等)。我真的认为postgresql在新连接的第一个查询上有某种开销。
I don't know how to prove/disprove that. If I used PG Admin III (pgAdmin version 1.12.3) all the query's seem fast. Any of the other tools I have the first query is slow. Most the time its not noticeably slower, and if it was I always chalked it up to updating the ram with the index. But this is clearly NOT that. If I open my tool(s) and do any other query that returns results the second query is fast regardless. If the first query doesn't return results then the second is still slow, then third is fast.
我不知道如何证明/反驳这一点。如果我使用PG Admin III(pgAdmin版本1.12.3),所有查询看起来都很快。我第一次查询的任何其他工具都很慢。大部分时间它的速度并没有明显变慢,如果是这样的话,我总是将其用指数更新ram。但这显然不是那样的。如果我打开我的工具并执行任何其他返回结果的查询,则无论如何第二个查询都很快。如果第一个查询没有返回结果,那么第二个查询仍然很慢,那么第三个查询很快。
edit 2 Even though I don't think the query has anything to do with the delay (every first query is slow) here are two results from running Explain (EXPLAIN ANALYZE)
编辑2即使我不认为查询与延迟有任何关系(每个第一个查询都很慢),这里有两个运行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'))
output:
"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"
1 个解决方案
#1
-1
First request will read blocks from disk to buffers. Second request will read from buffers.
第一个请求将从磁盘读取块到缓冲区。第二个请求将从缓冲区读取。
It doesnt matter how many connections are made, the result is dependant on whether that query has already been parsed.
无论建立多少连接都无关紧要,结果取决于该查询是否已被解析。
Please note changing literals will reparse the query Also note that if the query hasnt been executed in a while then physical reads may still occur depending on many variables.
请注意,更改文字将重新解析查询还请注意,如果查询尚未执行一段时间,则可能仍会发生物理读取,具体取决于许多变量。
#1
-1
First request will read blocks from disk to buffers. Second request will read from buffers.
第一个请求将从磁盘读取块到缓冲区。第二个请求将从缓冲区读取。
It doesnt matter how many connections are made, the result is dependant on whether that query has already been parsed.
无论建立多少连接都无关紧要,结果取决于该查询是否已被解析。
Please note changing literals will reparse the query Also note that if the query hasnt been executed in a while then physical reads may still occur depending on many variables.
请注意,更改文字将重新解析查询还请注意,如果查询尚未执行一段时间,则可能仍会发生物理读取,具体取决于许多变量。