public inbox for [email protected]help / color / mirror / Atom feed
Dataset 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]> 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:54 Dave Stibrany <[email protected]> parent: 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 04:35 Sumeet Shukla <[email protected]> parent: Dave Stibrany <[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 04:50 Tom Lane <[email protected]> parent: Sumeet Shukla <[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 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
* Re: Dataset is fetched from cache but still takes same time to fetch records as first run @ 2017-06-23 12:09 Adam Brusselback <[email protected]> parent: Tom Lane <[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 16:55 [email protected] <[email protected]> parent: Adam Brusselback <[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
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