Understanding PostgreSQL query execution time

From: Haider Ali <alihaider907(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Understanding PostgreSQL query execution time
Date: 2017-04-07 14:56:53
Message-ID: CAMDfpCTxRFE_apC1-2PqRW9uXTtoP7XzN9wqoLbNgdGKUn5_DA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message ldh@laurent-hasson.com 2017-04-07 15:03:18 Re: Understanding PostgreSQL query execution time
Previous Message vinny 2017-04-07 14:22:20 Re: Filter certain range of IP address.