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
