I have a 8 GB database, and 2 GB table. In a query i use the 2 GB table and several other tables where it takes around 90 minutes for execution.
In different places, it takes drastically different time. Say everywhere i have the same, OS - Debian. Primary memory - 3 GB PostgreSQL configuration.
But in one machine it takes 3.5 minutes, and in other machine 90 minutes which confuses me much. So i did a test of hard disk read speed, in the machine where it takes 90 minutes resulted in the following.,
dd if=/var/lib/postgresql/8.1/main/base/16385/17283 of=/dev/null bs=1M count=1024 1024+0 records in 1024+0 records out 1073741824 bytes (1.1 GB) copied, 8.32928 seconds, 129 MB/s
Unfortunately, i cannot execute this same on that 3.5 minute execution machine.
But i had the previous write speed test, output which is dd if=/dev/zero of=/tmp/test bs=1M count=1024 1073741824 bytes (1.1 GB) copied, 2.37701 seconds, 452 MB/s
and the same write speed test in the 90 minutes machine is dd if=/dev/zero of=/tmp/test bs=1M count=1024 1024+0 records in 1024+0 records out 1073741824 bytes (1.1 GB) copied, 19.5375 seconds, 55.0 MB/s
So i assume that there should be a 9 times faster execution. Because 55 MB write per second, and 450 MB write per second. But am i doing some thing silly here. Or what i can do better confirm the problem ??
can some body give me ideas on what to do for confirming what is the issue for consuming much time for the query execution ?
