Re: Understanding PostgreSQL query execution time

The first behavior is very likely just caching. The plan and results from the query are cached, so the second time, it's reused directly.

If you ran a bunch of other queries in the middle and effectively exhausted the cache, then back to your query, likely tou'd see the 'slow' behavior again.

As for AWS, not sure, but likely about memory and config more than latency.

Sent from my BlackBerry 10 smartphone.
From: Haider Ali
Sent: Friday, April 7, 2017 09:58
To: pgsql-performance(at)postgresql(dot)org
Subject: [PERFORM] Understanding PostgreSQL query execution time

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
3rd time => 3.0ms
4th time => 3.0ms
5th time => 0.8ms

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

1. Why first query on a table takes more time then queries followed by it ?
2. Why above behaviour doesn't reflect on Amazon RDS ?

Thank you for reading my post.

--
Haider Ali