public inbox for [email protected]
help / color / mirror / Atom feedDataset is fetched from cache but still takes same time to fetch records as first run
7+ messages / 6 participants
[nested] [flat]
* Dataset is fetched from cache but still takes same time to fetch records as first run
@ 2017-06-23 00:40 Sumeet Shukla <[email protected]>
2017-06-23 00:54 ` Re: Dataset is fetched from cache but still takes same time to fetch records as first run Dave Stibrany <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Sumeet Shukla @ 2017-06-23 00:40 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dataset is fetched from cache but still takes same time to fetch records as first run
2017-06-23 00:40 Dataset is fetched from cache but still takes same time to fetch records as first run Sumeet Shukla <[email protected]>
@ 2017-06-23 00:54 ` Dave Stibrany <[email protected]>
2017-06-23 04:35 ` Re: Dataset is fetched from cache but still takes same time to fetch records as first run Sumeet Shukla <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Dave Stibrany @ 2017-06-23 00:54 UTC (permalink / raw)
To: Sumeet Shukla <[email protected]>; +Cc: pgsql-performance
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 <[email protected]>
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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dataset is fetched from cache but still takes same time to fetch records as first run
2017-06-23 00:40 Dataset is fetched from cache but still takes same time to fetch records as first run Sumeet Shukla <[email protected]>
2017-06-23 00:54 ` Re: Dataset is fetched from cache but still takes same time to fetch records as first run Dave Stibrany <[email protected]>
@ 2017-06-23 04:35 ` Sumeet Shukla <[email protected]>
2017-06-23 04:50 ` Re: Dataset is fetched from cache but still takes same time to fetch records as first run Tom Lane <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Sumeet Shukla @ 2017-06-23 04:35 UTC (permalink / raw)
To: Dave Stibrany <[email protected]>; +Cc: pgsql-performance
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 <[email protected]> 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 <[email protected]>
> 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
>>
>>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dataset is fetched from cache but still takes same time to fetch records as first run
2017-06-23 00:40 Dataset is fetched from cache but still takes same time to fetch records as first run Sumeet Shukla <[email protected]>
2017-06-23 00:54 ` Re: Dataset is fetched from cache but still takes same time to fetch records as first run Dave Stibrany <[email protected]>
2017-06-23 04:35 ` Re: Dataset is fetched from cache but still takes same time to fetch records as first run Sumeet Shukla <[email protected]>
@ 2017-06-23 04:50 ` Tom Lane <[email protected]>
2017-06-23 12:09 ` Re: Dataset is fetched from cache but still takes same time to fetch records as first run Adam Brusselback <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2017-06-23 04:50 UTC (permalink / raw)
To: Sumeet Shukla <[email protected]>; +Cc: Dave Stibrany <[email protected]>; pgsql-performance
Sumeet Shukla <[email protected]> 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
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dataset is fetched from cache but still takes same time to fetch records as first run
2017-06-23 00:40 Dataset is fetched from cache but still takes same time to fetch records as first run Sumeet Shukla <[email protected]>
2017-06-23 00:54 ` Re: Dataset is fetched from cache but still takes same time to fetch records as first run Dave Stibrany <[email protected]>
2017-06-23 04:35 ` Re: Dataset is fetched from cache but still takes same time to fetch records as first run Sumeet Shukla <[email protected]>
2017-06-23 04:50 ` Re: Dataset is fetched from cache but still takes same time to fetch records as first run Tom Lane <[email protected]>
@ 2017-06-23 12:09 ` Adam Brusselback <[email protected]>
2017-06-23 16:55 ` Re: Dataset is fetched from cache but still takes same time to fetch records as first run [email protected] <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Adam Brusselback @ 2017-06-23 12:09 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Sumeet Shukla <[email protected]>; Dave Stibrany <[email protected]>; pgsql-performance
On Fri, Jun 23, 2017 at 12:50 AM, Tom Lane <[email protected]> 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).
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dataset is fetched from cache but still takes same time to fetch records as first run
2017-06-23 00:40 Dataset is fetched from cache but still takes same time to fetch records as first run Sumeet Shukla <[email protected]>
2017-06-23 00:54 ` Re: Dataset is fetched from cache but still takes same time to fetch records as first run Dave Stibrany <[email protected]>
2017-06-23 04:35 ` Re: Dataset is fetched from cache but still takes same time to fetch records as first run Sumeet Shukla <[email protected]>
2017-06-23 04:50 ` Re: Dataset is fetched from cache but still takes same time to fetch records as first run Tom Lane <[email protected]>
2017-06-23 12:09 ` Re: Dataset is fetched from cache but still takes same time to fetch records as first run Adam Brusselback <[email protected]>
@ 2017-06-23 16:55 ` [email protected] <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: [email protected] @ 2017-06-23 16:55 UTC (permalink / raw)
To: Adam Brusselback <[email protected]>; Tom Lane <[email protected]>; +Cc: Sumeet Shukla <[email protected]>; Dave Stibrany <[email protected]>; pgsql-performance
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: [email protected]
Sent: June 23, 2017 8:11 AM
To: [email protected]
Cc: [email protected]; [email protected]; [email protected]
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 <[email protected]<mailto:[email protected]>> 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).
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dataset is fetched from cache but still takes same time to fetch records as first run
@ 2017-06-23 07:52 Glyn Astill <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Glyn Astill @ 2017-06-23 07:52 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Sumeet Shukla <[email protected]>; +Cc: Dave Stibrany <[email protected]>; pgsql-performance
>From: Tom Lane <[email protected]>
>To: Sumeet Shukla <[email protected]>
>Cc: Dave Stibrany <[email protected]>; [email protected]
>Sent: Friday, 23 June 2017, 5:50
>Subject: Re: [PERFORM] Dataset is fetched from cache but still takes same time to fetch records as first run
> Sumeet Shukla <[email protected]> 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.
>
It's also possibly time taken for the results to be tranferred over a network if the data is large.
Glyn
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2017-06-23 16:55 UTC | newest]
Thread overview: 7+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-06-23 00:40 Dataset is fetched from cache but still takes same time to fetch records as first run Sumeet Shukla <[email protected]>
2017-06-23 00:54 ` Dave Stibrany <[email protected]>
2017-06-23 04:35 ` Sumeet Shukla <[email protected]>
2017-06-23 04:50 ` Tom Lane <[email protected]>
2017-06-23 12:09 ` Adam Brusselback <[email protected]>
2017-06-23 16:55 ` [email protected] <[email protected]>
2017-06-23 07:52 Re: Dataset is fetched from cache but still takes same time to fetch records as first run Glyn Astill <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox