From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-performance@postgresql.org
Subject: Re: pg9.0.3 explain analyze running very slow compared to a different
	box with much less configuration
Date: Thu, 24 Mar 2011 11:11:03 +0200
User-Agent: KMail/1.9.7
References: <AANLkTinqz7XpmQHr-Utr4GAR1Ld0PKObxdvF+95xtgbW@mail.gmail.com>
In-Reply-To: <AANLkTinqz7XpmQHr-Utr4GAR1Ld0PKObxdvF+95xtgbW@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201103241111.03360.achill@matrix.gatewaynet.com>

You might take a look here:
http://archives.postgresql.org/pgsql-admin/2011-01/msg00050.php
My problem had to do with the speed of gettimeofday. You might want to do s=
ome special setting regarding
your box's way of reading time for the hw clock.

=CE=A3=CF=84=CE=B9=CF=82 Thursday 24 March 2011 04:04:21 =CE=BF/=CE=B7 DM =
=CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> Hi All,
>=20
> pg9.0.3 explain analyze running very slow compared to old box with much l=
ess
> configuration.
>=20
> But actual query is performing much better than the old server.
>=20
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3Dold Server=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D
> OS: CentOS release 5.4 (Final)
> Linux Server 2.6.18-164.6.1.el5 #1 SMP Tue Nov 3 16:12:36 EST 2009 x86_64
> x86_64 x86_64 GNU/Linux
>=20
> RAM - 16GB
> CPU - 8 Core
> disk - 300GB
> RAID10 on the disk
>=20
> Postgresql 9.0.3
>=20
> Postgres Config:
> shared_buffers =3D 6GB
> work_mem =3D 32MB
> maintenance_work_mem =3D 512MB
> effective_cache_size =3D 12GB
>=20
> #explain analyze select * from photo;
>                                                          QUERY PLAN
> -------------------------------------------------------------------------=
=2D--------------------------------------------------
>  Seq Scan on photo  (cost=3D0.00..8326849.24 rows=3D395405824 width=3D168=
) (actual
> time=3D5.632..157757.284 rows=3D395785382 loops=3D1)
>  Total runtime: 187443.850 ms
> (2 rows)
>=20
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3DnewServer=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D
>=20
> CentOS release 5.4 (Final)
> Linux Server 2.6.18-164.6.1.el5 #1 SMP Tue Nov 3 16:12:36 EST 2009 x86_64
> x86_64 x86_64 GNU/Linux
>=20
> RAM - 64GB
> CPU - 12 Core
> disk - 1TB
> RAID10 on the disk
>=20
> Postgresql 9.0.3
> Postgres Config:
> shared_buffers =3D 16GB
> work_mem =3D 32MB
> maintenance_work_mem =3D 1024MB
> effective_cache_size =3D 12GB
>=20
>=20
> # explain analyze select * from photo;
>                                                         QUERY PLAN
> -------------------------------------------------------------------------=
=2D--------------------------------------------------
>  Seq Scan on photo  (cost=3D0.00..8326810.24 rows=3D395579424 width=3D165=
) (actual
> time=3D0.051..316879.928 rows=3D395648020 loops=3D1)
>  Total runtime: 605703.206 ms
> (2 rows)
>=20
>=20
> I read other articles about the same issue but could not find the exact
> solution.
>=20
>=20
> I ran gettimeofday() on both machines and got the below results:
>=20
> Results:
>=20
> *[Old Server]# time /tmp/gtod*
>=20
> real  0m0.915s
>=20
> user  0m0.914s
>=20
> sys   0m0.001s
>=20
> *[New Server]#  time /tmp/gtod*
>=20
> real  0m7.542s
>=20
> user  0m7.540s
>=20
> sys   0m0.001s
>=20
>=20
> I am not sure how to fix this issue, any help would be in great assistanc=
e.
>=20
>=20
> Thanks
>=20
> Deepak
>=20


=2D-=20
Achilleas Mantzios