Thread: [PERFORM] Understanding PostgreSQL query execution time

[PERFORM] Understanding PostgreSQL query execution time

From
Haider Ali
Date:
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

Re: [PERFORM] Understanding PostgreSQL query execution time

From
"ldh@laurent-hasson.com"
Date:
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@postgresql.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

Re: [PERFORM] Understanding PostgreSQL query execution time

From
Gerardo Herzig
Date:

----- Mensaje original -----
> De: "Haider Ali" <alihaider907@gmail.com>
> Para: pgsql-performance@postgresql.org
> Enviados: Viernes, 7 de Abril 2017 11:56:53
> Asunto: [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

That is the effects of the postgres/Linux cache for shure.
>
>
> 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 ?
>
Amazon provides you with SSD like disks, running close to memory speed. That would explain the little impact of having
aram cache. 

HTH
Gerardo
>
> Haider Ali


Re: [PERFORM] Understanding PostgreSQL query execution time

From
vinny
Date:
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.