Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL

On Wed, Apr 1, 2015 at 6:56 AM, Pietro Pugni <pietro(dot)pugni(at)gmail(dot)com> wrote:

> This question was posted originally on
> http://dba.stackexchange.com/questions/96444/cant-get-dell-pe-t420-perc-h710-perform-better-than-a-macmini-with-postgresql
> and they suggested to post it on this mailing list.
>
> It's months that I'm trying to solve a performance issue with PostgreSQL.
> I’m able to give you all the technical details needed.
> *SYSTEM CONFIGURATION*
>
> Our deployment machine is a Dell PowerEdge T420 with a Perc H710 RAID
> controller configured in this way:
>
> - two Intel Xeon E5-2640 v2 @2Ghz
> - PostgreSQL 9.4 (updated to the latest available version)
>
> My personal low cost and low profile development machine is a MacMini
> configured in this way:
>
> - one Intel i7 @2.2Ghz
> - PostgreSQL 9.0.13 (the original built-in shipped with OS X Server)
>
>
Using such different versions of PostgreSQL seems like a recipe for
frustration.

> Here are two benchmarks generated using pg_test_fsync:
>

This is unlikely to be important for the type of workload you describe.
Fsyncs are the bottleneck for many short transactions, but not often the
bottleneck for very large transactions.

> *T420*
>
> Query B_2 [95664.832 ms + 0.523 ms] http://explain.depesz.com/s/v06
>
> *MacMini*
>
> Query B_2 [44890.813 ms] http://explain.depesz.com/s/y7Dk
>

What collation is used for both databases? Perhaps the T420 is using a
much slower collation.

How can you sort 2,951,191 but then materialize 4,458,971 rows out of
that? I've never seen that before. (Or, in the other plan, put 2,951,191
rows into the sort from the CTE but get 4,458,971 out of the sort?

Cheers,

Jeff