Re: [PERFORM] Understanding PostgreSQL query execution time

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.