Why does a Postgres query have drastically different performance on two modern machines?

Question

I have two machines:

Machine #1:

• Desktop
• Intel E5200 CPU (2.5Ghz)
• 2GB RAM (DDR2)
• Spinning hard drive (OS) and SSD Hard drive (data)

Machine #2:

• Laptop
• AMD E-300 (1.3GHz) CPU
• 8GB RAM (DDR3)
• Spinning hard drive

Both machines are running Ubuntu 11 and Postgres 9.1.

I have a large Postgres database (>100GB) and am running an advanced query. As I watch the query, all I see is 100% CPU on both machines..only one core seems to be used. iotop shows very little hard drive activity (maybe 1MB/s bursts).

Machine #1 finishes in 20 minutes. Machine #2 is still running after 5 hours. I figured that Machine #2 would be about half the speed of Machine #1 (1.3Ghz vs 2.5 Ghz)..maybe a little better due to more RAM.

I ran this as a quick test to see if buying more RAM would make a huge difference on Machine #1, but it seems that CPU plays a much bigger role. Am I right or is there something else going on?

A few questions:

1. What factors on Machine #2 are making it so slow?
2. Could it be that the SSD is making a huge difference but I can't see it in iotop?
3. Given a limited budget..which upgrades could I do to Machine #2 to make it even faster? In particular is RAM or CPU a better investment?

Answer 1

Your E-300 CPU is about 3 times slower in synthetic benchmarks vs the E5200 (http://www.cpubenchmark.net/cpu_lookup.php?cpu=AMD+E-300+APU vs http://www.cpubenchmark.net/cpu_lookup.php?cpu=Pentium+Dual-Core+E5200+%40+2.50GHz)

That's one of the reasons why the query is slower on the second machine. The SSD drive storing the data is likely making a huge impact, especially if your spinning drive is at 5400RPM instead of 7200, which I suspect is the case.

Both of these processors are desktop grade and relatively slow, but you cannot compare two processors clock for clock, even from the same manufacturer, as the difference in manufacturing process and technologies available in the microprocessor can heavily change the scales.

Since you already have 8GB of RAM, you'll probably want to upgrade the CPU on your second machine, but I feel that you will still be waiting for disk I/O.

You should seriously consider the following:

• Optimizing your query
• Postgres performance tuning
• Using a server on which you can run queries against this database, or at least a more powerful desktop configuration