Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1T3yMW-00053b-Rf for pgsql-performance@postgresql.org; Tue, 21 Aug 2012 23:59:57 +0000 Received: from mail-pz0-f46.google.com ([209.85.210.46]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1T3yMU-00005G-LK for pgsql-performance@postgresql.org; Tue, 21 Aug 2012 23:59:55 +0000 Received: by dady13 with SMTP id y13so270212dad.19 for ; Tue, 21 Aug 2012 16:59:53 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=date:from:to:message-id:in-reply-to:references:subject:x-mailer :mime-version:content-type:x-gm-message-state; bh=iqnkoCPoiT3rvvcwjL+UlPwPg/Wp7vRdL1tE2/WLGzE=; b=ixCi3DCBOnXI7aDLwRIeim5Y+sdVL0UtuI9HKlPbIv5fx88is3k9BG2NQGGdEmznOL JeQd5yDNkTvK2d6pNqlfKj3+nO8bzNiqvImED/RU7+lGWYmnrbt/yZY+sDieMjxHob0m DCUi/Em25HPgpvGeMul6d33QZ3DA9W6Lno+1eBa3jSwN9Q7sydq4UFD1leZ6E5nhPVy4 0yDnCeX63sEN0zCNTaXrmRsIsIoX/1RGOZvGALGGtLs02muOVLikrgrP6OSdr5ikDw5s 1IS4yv09ZUxRAcpRwkjn3iQLbqOjx1trwZkzyMVwk/Mt/NaN0hoYknBSyslkidiASgKQ 9kNw== Received: by 10.66.9.2 with SMTP id v2mr41747907paa.65.1345593593495; Tue, 21 Aug 2012 16:59:53 -0700 (PDT) Received: from [10.0.1.3] (S01060026bb736d9c.vc.shawcable.net. [24.86.23.100]) by mx.google.com with ESMTPS id mu8sm2409083pbc.49.2012.08.21.16.59.52 (version=TLSv1/SSLv3 cipher=OTHER); Tue, 21 Aug 2012 16:59:52 -0700 (PDT) Date: Tue, 21 Aug 2012 16:59:51 -0700 From: Matt Daw To: pgsql-performance@postgresql.org Message-ID: <5673F06B4FCF43B281B25DC9BB758298@shotgunsoftware.com> In-Reply-To: <5B826B9835204EFD8E3A3DD6D14A0235@shotgunsoftware.com> References: <5B826B9835204EFD8E3A3DD6D14A0235@shotgunsoftware.com> Subject: Re: Performance of Seq Scan from buffer cache X-Mailer: sparrow 1.6.3 (build 1172) MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="503420f7_43773132_169" X-Gm-Message-State: ALoCoQkVS3uD9rJkfJPEnENTCIsloMQbRe9QMW+/KY0WTO5EtKN8YP2DrV6kn+Fjsp7I4qAngI4s X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201208/149 X-Sequence-Number: 47687 --503420f7_43773132_169 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Ugh, never mind. I ran ltrace and it's spending 99% of its time in gettim= eofday. =20 select count(*) from notes; count =20 --------- 1926207 (1 row) Time: 213.950 ms explain analyze select count(*) from notes; QUERY PLAN = =20 -------------------------------------------------------------------------= ----------------------------------------------- Aggregate (cost=3D99274.59..99274.60 rows=3D1 width=3D0) (actual time=3D= 2889.325..2889.325 rows=3D1 loops=3D1) -> Seq Scan on notes (cost=3D0.00..94459.07 rows=3D1926207 width=3D0= ) (actual time=3D0.005..1475.218 rows=3D1926207 loops=3D1) Total runtime: 2889.360 ms (3 rows) Time: 2889.842 ms =20 On Tuesday, 21 August, 2012 at 3:57 PM, Matt Daw wrote: > Howdy. I'm curious what besides raw hardware speed determines the perfo= rmance of a Seq Scan that comes entirely out of shared buffers=E2=80=A6 I= ran the following on the client's server I'm profiling, which is otherwi= se idle: > =20 > EXPLAIN (ANALYZE ON, BU=46=46ERS ON) SELECT * =46ROM notes; > =20 > Seq Scan on notes (cost=3D0.00..94004.88 rows=3D1926188 width=3D862) (= actual time=3D0.009..1673.702 rows=3D1926207 loops=3D1) > Buffers: shared hit=3D74743 > Total runtime: 3110.442 ms > (3 rows) > =20 > =20 > =E2=80=A6 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 r= easonable (8644.1985 MB/s with 1 core -> 25017 MB/s with 12 cores). The b= ox is running 2.6.26.6-49 and postgresql 9.0.6. > =20 > I'm stumped as to why it's so much slower, any ideas on what might expl= ain it=E2=80=A6 or other benchmarks I could run to try to narrow down the= cause=3F > =20 > Thanks=21 > =20 > Matt =20 --503420f7_43773132_169 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Ugh, never mind.= I ran ltrace and it's spending 99% of its time in gettimeofday.

select count(*) fr= om notes;
  count  
---------
&nb= sp;1926207
(1 row)

Time: 213.950 ms

explain analyze select count(*) from notes;
<= div>                   =                     &n= bsp;              QUERY PLAN   &n= bsp;                    = ;                     &= nbsp;         
-----------------------= -------------------------------------------------------------------------= ------------------------
 Aggregate  (cost=3D99274.59= ..99274.60 rows=3D1 width=3D0) (actual time=3D2889.325..2889.325 rows=3D1= loops=3D1)
   ->  Seq Scan on notes  (c= ost=3D0.00..94459.07 rows=3D1926207 width=3D0) (actual time=3D0.005..1475= .218 rows=3D1926207 loops=3D1)
 Total runtime: 2889.360 ms=
(3 rows)

Time: 2889.842 ms
=20

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=E2=80=A6 I ran the following o= n the client's server I'm profiling, which is otherwise idle:

EXPLAIN (ANALYZE ON, BU=46=46ERS ON) = SELECT * =46ROM notes;

Seq Scan on notes =  (cost=3D0.00..94004.88 rows=3D1926188 width=3D862) (actual time=3D0= .009..1673.702 rows=3D1926207 loops=3D1)
   Buffers: = shared hit=3D74743
 Total runtime: 3110.442 ms
(= 3 rows)

=E2=80=A6 and that's about 9x slower than what I get on my laptop wit= h 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 cor= es). 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=E2=80=A6 or other benchmarks I could= run to try to narrow down the cause=3F

Thanks=21
=
Matt
=20 =20 =20 =20
=20

--503420f7_43773132_169--