pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance of Seq Scan from buffer cache
3+ messages / 2 participants
[nested] [flat]
* Performance of Seq Scan from buffer cache
@ 2012-08-21 22:57 Matt Daw <matt@shotgunsoftware.com>
2012-08-21 23:59 ` Re: Performance of Seq Scan from buffer cache Matt Daw <matt@shotgunsoftware.com>
0 siblings, 1 reply; 3+ messages in thread
From: Matt Daw @ 2012-08-21 22:57 UTC (permalink / raw)
To: pgsql-performance
Howdy. I'm curious what besides raw hardware speed determines the performance of a Seq Scan that comes entirely out of shared buffers… I ran the following on the client's server I'm profiling, which is otherwise idle:
EXPLAIN (ANALYZE ON, BUFFERS ON) SELECT * FROM notes;
Seq Scan on notes (cost=0.00..94004.88 rows=1926188 width=862) (actual time=0.009..1673.702 rows=1926207 loops=1)
Buffers: shared hit=74743
Total runtime: 3110.442 ms
(3 rows)
… and that's about 9x slower than what I get on my laptop with the same data. I ran stream-scaling on the machine and the results seem reasonable (8644.1985 MB/s with 1 core -> 25017 MB/s with 12 cores). The box is running 2.6.26.6-49 and postgresql 9.0.6.
I'm stumped as to why it's so much slower, any ideas on what might explain it… or other benchmarks I could run to try to narrow down the cause?
Thanks!
Matt
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Performance of Seq Scan from buffer cache
2012-08-21 22:57 Performance of Seq Scan from buffer cache Matt Daw <matt@shotgunsoftware.com>
@ 2012-08-21 23:59 ` Matt Daw <matt@shotgunsoftware.com>
2012-08-23 13:32 ` Re: Performance of Seq Scan from buffer cache Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Matt Daw @ 2012-08-21 23:59 UTC (permalink / raw)
To: pgsql-performance
Ugh, never mind. I ran ltrace and it's spending 99% of its time in gettimeofday.
select count(*) from notes;
count
---------
1926207
(1 row)
Time: 213.950 ms
explain analyze select count(*) from notes;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=99274.59..99274.60 rows=1 width=0) (actual time=2889.325..2889.325 rows=1 loops=1)
-> Seq Scan on notes (cost=0.00..94459.07 rows=1926207 width=0) (actual time=0.005..1475.218 rows=1926207 loops=1)
Total runtime: 2889.360 ms
(3 rows)
Time: 2889.842 ms
On Tuesday, 21 August, 2012 at 3:57 PM, Matt Daw wrote:
> Howdy. I'm curious what besides raw hardware speed determines the performance of a Seq Scan that comes entirely out of shared buffers… I ran the following on the client's server I'm profiling, which is otherwise idle:
>
> EXPLAIN (ANALYZE ON, BUFFERS ON) SELECT * FROM notes;
>
> Seq Scan on notes (cost=0.00..94004.88 rows=1926188 width=862) (actual time=0.009..1673.702 rows=1926207 loops=1)
> Buffers: shared hit=74743
> Total runtime: 3110.442 ms
> (3 rows)
>
>
> … and that's about 9x slower than what I get on my laptop with the same data. I ran stream-scaling on the machine and the results seem reasonable (8644.1985 MB/s with 1 core -> 25017 MB/s with 12 cores). The box is running 2.6.26.6-49 and postgresql 9.0.6.
>
> I'm stumped as to why it's so much slower, any ideas on what might explain it… or other benchmarks I could run to try to narrow down the cause?
>
> Thanks!
>
> Matt
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Performance of Seq Scan from buffer cache
2012-08-21 22:57 Performance of Seq Scan from buffer cache Matt Daw <matt@shotgunsoftware.com>
2012-08-21 23:59 ` Re: Performance of Seq Scan from buffer cache Matt Daw <matt@shotgunsoftware.com>
@ 2012-08-23 13:32 ` Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Merlin Moncure @ 2012-08-23 13:32 UTC (permalink / raw)
To: Matt Daw <matt@shotgunsoftware.com>; +Cc: pgsql-performance
On Tue, Aug 21, 2012 at 6:59 PM, Matt Daw <matt@shotgunsoftware.com> wrote:
> Ugh, never mind. I ran ltrace and it's spending 99% of its time in
> gettimeofday.
yeah -- this is a fairly common report. some systems (windows) have a
less accurate but much faster gettimeofday().
merlin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-08-23 13:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-08-21 22:57 Performance of Seq Scan from buffer cache Matt Daw <matt@shotgunsoftware.com>
2012-08-21 23:59 ` Matt Daw <matt@shotgunsoftware.com>
2012-08-23 13:32 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox