Postgres Pro
Downloads
Home   >   mailing lists

Thread: [PERFORM] Dataset is fetched from cache but still takes same time to fetchrecords as first run

[PERFORM] Dataset is fetched from cache but still takes same time to fetchrecords as first run

From
Sumeet Shukla
Date:
Both the first run and subsequent run takes same amount of time.

First Run:

"Seq Scan on d_payer (cost=0.00..8610.40 rows=121788 width=133) (actual time=8.760..98.582 rows=121788 loops=1)"
" Buffers: shared read=2521"
"Planning time: 16.820 ms"
"Execution time: 108.626 ms"


Second Run:

"Seq Scan on d_payer (cost=0.00..8610.40 rows=121788 width=133) (actual time=0.010..18.456 rows=121788 loops=1)"
" Buffers: shared hit=2521"
"Planning time: 0.083 ms"
"Execution time: 27.288 ms"


Can anyone please help me understand and fix this.


Thanks & Regards,
Sumeet Shukla

Re: [PERFORM] Dataset is fetched from cache but still takes same timeto fetch records as first run

From
Dave Stibrany
Date:
The numbers you posted look exactly as I would expect. The first read hits disk and takes 108ms, the second read hits the cache and takes 27ms.

On Thu, Jun 22, 2017 at 8:40 PM, Sumeet Shukla <sumeet.k.shukla@gmail.com> wrote:
Both the first run and subsequent run takes same amount of time.

First Run:

"Seq Scan on d_payer (cost=0.00..8610.40 rows=121788 width=133) (actual time=8.760..98.582 rows=121788 loops=1)"
" Buffers: shared read=2521"
"Planning time: 16.820 ms"
"Execution time: 108.626 ms"


Second Run:

"Seq Scan on d_payer (cost=0.00..8610.40 rows=121788 width=133) (actual time=0.010..18.456 rows=121788 loops=1)"
" Buffers: shared hit=2521"
"Planning time: 0.083 ms"
"Execution time: 27.288 ms"


Can anyone please help me understand and fix this.


Thanks & Regards,
Sumeet Shukla


Re: [PERFORM] Dataset is fetched from cache but still takes same timeto fetch records as first run

From
Sumeet Shukla
Date:
Yes, but when I actually execute the query in pgAdmin3, it takes exactly the same time of 19.5 secs.
How is that possible?

Thanks & Regards,
Sumeet Shukla


On Thu, Jun 22, 2017 at 7:54 PM, Dave Stibrany <dstibrany@gmail.com> wrote:
The numbers you posted look exactly as I would expect. The first read hits disk and takes 108ms, the second read hits the cache and takes 27ms.

On Thu, Jun 22, 2017 at 8:40 PM, Sumeet Shukla <sumeet.k.shukla@gmail.com> wrote:
Both the first run and subsequent run takes same amount of time.

First Run:

"Seq Scan on d_payer (cost=0.00..8610.40 rows=121788 width=133) (actual time=8.760..98.582 rows=121788 loops=1)"
" Buffers: shared read=2521"
"Planning time: 16.820 ms"
"Execution time: 108.626 ms"


Second Run:

"Seq Scan on d_payer (cost=0.00..8610.40 rows=121788 width=133) (actual time=0.010..18.456 rows=121788 loops=1)"
" Buffers: shared hit=2521"
"Planning time: 0.083 ms"
"Execution time: 27.288 ms"


Can anyone please help me understand and fix this.


Thanks & Regards,
Sumeet Shukla



Re: [PERFORM] Dataset is fetched from cache but still takes same time to fetch records as first run

From
Tom Lane
Date:
Sumeet Shukla <sumeet.k.shukla@gmail.com> writes:
> Yes, but when I actually execute the query in pgAdmin3, it takes exactly
> the same time of 19.5 secs.

pgAdmin is well known to be horribly inefficient at displaying large
query results (and 121788 rows qualifies as "large" for this purpose,
I believe).  The circa-tenth-of-a-second savings on the server side
is getting swamped by client-side processing.

It's possible that pgAdmin4 has improved matters in this area.

            regards, tom lane

Re: [PERFORM] Dataset is fetched from cache but still takes same timeto fetch records as first run

From
Adam Brusselback
Date:
On Fri, Jun 23, 2017 at 12:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
It's possible that pgAdmin4 has improved matters in this area.

Sadly, not in my experience.  It's actually considerably worse than pgAdminIII in my experience when selecting a lot of rows, especially when very wide (20+ columns).

Re: [PERFORM] Dataset is fetched from cache but still takes same timeto fetch records as first run

From
"ldh@laurent-hasson.com"
Date:
ditto here... much slower, and crashes too often. We run an evergreen shop where I work, but everyone has moved back to III.

Sent from my BlackBerry KEYone - the most secure mobile device
From: adambrusselback@gmail.com
Sent: June 23, 2017 8:11 AM
To: tgl@sss.pgh.pa.us
Cc: sumeet.k.shukla@gmail.com; dstibrany@gmail.com; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Dataset is fetched from cache but still takes same time to fetch records as first run

On Fri, Jun 23, 2017 at 12:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
It's possible that pgAdmin4 has improved matters in this area.

Sadly, not in my experience.  It's actually considerably worse than pgAdminIII in my experience when selecting a lot of rows, especially when very wide (20+ columns).