Thanks to all for the help - and the sanity check. The problem was in
the test and not in the configuration.
We were using a particularly difficult query as a reference (and fully
understanding that it is a two-dimensional alternative to a proper
benchmark). On our test system each run was with empty caches. The
test on the Mac was with caches loaded. Once we started running the
tests with loaded caches, the tuning parameters started behaving as
expected. In the end we took a 880 second query to 3.4 seconds
(compared to 95 seconds on the Mac).
The key was the fact that large configuration changes drew no measurable
change in performance. And that is when you know you are turning the
wrong knobs!
Scott Marlowe wrote:
> On Nov 9, 2007 10:55 PM, Mark Niedzielski <min@xxxxxxxxxxxxxxxxxxxx> wrote:
>
>> Our developers run on MacBook Pros w/ 2G memory and our production
>> hardware is dual dual-Core Opterons w/ 8G memory running CentOS 5. The
>> Macs perform common and complex Postgres operations in about half the
>> time of our unloaded production hardware. We've compared configurations
>> and the production hardware is running a much bigger configuration and
>> faster disk.
>>
>> What are we missing? Is there a trick to making AMDs perform? Does
>> Linux suck compared to BSD?
>>
>
> It's quite possible that either you've got some issue with poor
> hardware / OS integration (think RAID controllers that have bad
> drivers, etc) or that you've de-tuned postgresql on your CentOS
> machines when you thought you were tuning it. A common mistake is to
> set work_mem or shared_buffers so high that they are slower than they
> would be if they were smaller.
>
> Also, if your data sets in production are hundreds of millions of
> rows, and the test set on your lap top is 100,000 rows, then of course
> the laptop is going to be faster, it has less data to wade through.
>
> So, the key question is what, exactly, is different between your dev
> laptops and your production machines.
>
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
