Hello
I want to understand execution time of a query in PostgreSQL then I want to relate it to the problem i am getting. According to my observation ( I can't explain why this happen ) whenever we query a table first time its execution will be high (sometimes very high) as compare to queries made on same table in a short period of time followed by first query on that table. For example query given below
SELECT "global_configs".* FROM "global_configs" ORDER BY "global_configs"."id" ASC LIMIT $1
executed multiple times instantaneous one after another have following execution time
1st time => 147.5ms
2nd time => 3.0ms
4th time => 3.0ms
I want to understand why there is a huge time difference between 1st and rest of the executions.
Relation to other problem
Having experience above behaviour of PostgreSQL now I am using PostgreSQL managed by Amazon RDS. Observation is no matter how many times I execute same query its execution times remain same ( although execution time of a query on RDS is comparatively high as compare to query running on local instance of PostgreSQL that I can understand is because of Network latency)
Questions
- Why first query on a table takes more time then queries followed by it ?
- Why above behaviour doesn't reflect on Amazon RDS ?
Thank you for reading my post.
--