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 some special setting regarding
your box's way of reading time for the hw clock.
Στις Thursday 24 March 2011 04:04:21 ο/η DM έγραψε:
> Hi All,
>
> pg9.0.3 explain analyze running very slow compared to old box with much less
> configuration.
>
> But actual query is performing much better than the old server.
>
> ============old Server===============
> 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
>
> RAM - 16GB
> CPU - 8 Core
> disk - 300GB
> RAID10 on the disk
>
> Postgresql 9.0.3
>
> Postgres Config:
> shared_buffers = 6GB
> work_mem = 32MB
> maintenance_work_mem = 512MB
> effective_cache_size = 12GB
>
> #explain analyze select * from photo;
> QUERY PLAN
>
----------------------------------------------------------------------------------------------------------------------------
> Seq Scan on photo (cost=0.00..8326849.24 rows=395405824 width=168) (actual
> time=5.632..157757.284 rows=395785382 loops=1)
> Total runtime: 187443.850 ms
> (2 rows)
>
> ============newServer===============
>
> 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
>
> RAM - 64GB
> CPU - 12 Core
> disk - 1TB
> RAID10 on the disk
>
> Postgresql 9.0.3
> Postgres Config:
> shared_buffers = 16GB
> work_mem = 32MB
> maintenance_work_mem = 1024MB
> effective_cache_size = 12GB
>
>
> # explain analyze select * from photo;
> QUERY PLAN
>
----------------------------------------------------------------------------------------------------------------------------
> Seq Scan on photo (cost=0.00..8326810.24 rows=395579424 width=165) (actual
> time=0.051..316879.928 rows=395648020 loops=1)
> Total runtime: 605703.206 ms
> (2 rows)
>
>
> I read other articles about the same issue but could not find the exact
> solution.
>
>
> I ran gettimeofday() on both machines and got the below results:
>
> Results:
>
> *[Old Server]# time /tmp/gtod*
>
> real 0m0.915s
>
> user 0m0.914s
>
> sys 0m0.001s
>
> *[New Server]# time /tmp/gtod*
>
> real 0m7.542s
>
> user 0m7.540s
>
> sys 0m0.001s
>
>
> I am not sure how to fix this issue, any help would be in great assistance.
>
>
> Thanks
>
> Deepak
>
--
Achilleas Mantzios