MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII;
 format=flowed
Content-Transfer-Encoding: 7bit
Date: Mon, 10 Apr 2017 14:18:28 +0200
From: vinny <vinny@xs4all.nl>
To: Haider Ali <alihaider907@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Understanding PostgreSQL query execution time
In-Reply-To: 
 <CAMDfpCTxRFE_apC1-2PqRW9uXTtoP7XzN9wqoLbNgdGKUn5_DA@mail.gmail.com>
References: 
 <CAMDfpCTxRFE_apC1-2PqRW9uXTtoP7XzN9wqoLbNgdGKUn5_DA@mail.gmail.com>
Message-ID: <1e4f7641ac0b76f9b655117c291322c0@xs4all.nl>
User-Agent: XS4ALL Webmail
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 2017-04-07 16:56, Haider Ali wrote:
> 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

The first time a query is executed it is quite likely that the data it 
needs
is not in RAM yet, so it must fetch the data from disk, which is slow.

But, benchmarking is an art; did you execute these queries separately 
from the commandline?
Otherwise where may be other forces at work here...

> 
> 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)

The problem may go away entirely if the database/OS has enough RAM 
available,
and configured, for caching.

The problem on your local system may be simply a case of PostgreSQL or 
the OS
removing tuples/index data from RAM when it feels it can make better use 
of that RAM
space for other things if you don't access that data for a while.


Try spying on your system with iotop and such tools to see what the 
server is actually doing
during the first query. If there is a spike in disk-IO then you've found 
the cause;
the tuples where not in RAM.
You may also want to run an EXPLAIN to make sure that the fast queries 
are not purely the result
of some query-result cache.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance