Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Ydcir-0007M1-UW for pgsql-performance@arkaria.postgresql.org; Thu, 02 Apr 2015 10:51:42 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Ydcir-0000Jr-9Z for pgsql-performance@arkaria.postgresql.org; Thu, 02 Apr 2015 10:51:41 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Ydciq-0000Jl-Dx for pgsql-performance@postgresql.org; Thu, 02 Apr 2015 10:51:40 +0000 Received: from mail-wi0-x22a.google.com ([2a00:1450:400c:c05::22a]) by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Ydcim-00066T-Vk for pgsql-performance@postgresql.org; Thu, 02 Apr 2015 10:51:38 +0000 Received: by wiaa2 with SMTP id a2so100319109wia.0 for ; Thu, 02 Apr 2015 03:51:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=content-type:mime-version:subject:from:in-reply-to:date:cc :message-id:references:to; bh=2qhoJleGg4IIRAl6j6tHvUGeqTeU2+uOGsiFtwIYpH0=; b=phhWTI5MI0A5OhoTuMgRFvcBPpMKEOEGL2A6Tznb14ufyUxHaVWP9H34X/++moPFQY AHrQhhadwEIiI7k/gTJxXJamkEXxwTKTURF77HWPY8yfd7Oyy37WyRv1oz4ifxFtjaKn 9XCxjX7I3PndCiUsy8bpH9HBYwwAN3i6mNg9u2fPI01G1mXHzRTGsbaDWge104REs2yP j/SpvGpoBp80JLiOxKW8XCTvBSPG/fB8csnlyVFMGzcQQ1uv7IwFTI6Tqfiw8XBL0z2b SdzNvUSPto5I31ZA5/5ew4S1s/ESHSWJ+6BLGJd86pSBdMUXw64tGVS8mnaoyAXwtzIn j7CA== X-Received: by 10.194.95.133 with SMTP id dk5mr89980500wjb.143.1427971895415; Thu, 02 Apr 2015 03:51:35 -0700 (PDT) Received: from [192.168.2.2] (2-230-140-245.ip202.fastwebnet.it. [2.230.140.245]) by mx.google.com with ESMTPSA id vq9sm6708621wjc.6.2015.04.02.03.51.34 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Thu, 02 Apr 2015 03:51:34 -0700 (PDT) Content-Type: multipart/alternative; boundary="Apple-Mail=_D7B49AF7-8FF4-4B70-8E8E-0ADC3E14B2FB" Mime-Version: 1.0 (Mac OS X Mail 7.3 \(1878.6\)) Subject: Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL From: Pietro Pugni In-Reply-To: Date: Thu, 2 Apr 2015 12:51:33 +0200 Cc: Aidan Van Dyk , pgsql-performance@postgresql.org Message-Id: References: <732B95A3-FEC8-4623-9AC5-98FD30F00863@gmail.com> To: Wei Shan X-Mailer: Apple Mail (2.1878.6) X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --Apple-Mail=_D7B49AF7-8FF4-4B70-8E8E-0ADC3E14B2FB Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=windows-1252 Hi Wei Shan, Thank you for your response. Query B was run after initializing the DB ex-novo doing VACUUM ANALYZE = before and after creating and clustering indexes. By the way, these results are consistent through time and are = reproducible, so it=92s not a metter of statistic collector (I guess). Your observation is the same done at dba.stackexchange.com and this make = me think that the built-in Postgres of OS X Server is truly optimized.=20= Best regards, Pietro PS on the other response I reported both postgresql.conf=20 Il giorno 01/apr/2015, alle ore 16:44, Wei Shan = ha scritto: > Just looking at the 2 B_2 queries, I'm curious as to why is the = execution plan different between the 2 machines. Is the optimiser stats = updated on both databases? >=20 > Regards, > Wei Shan >=20 > On 1 April 2015 at 22:32, Aidan Van Dyk wrote: > On Wed, Apr 1, 2015 at 9:56 AM, Pietro Pugni = wrote: > =20 > Now let=92s propose some query profiling times. >=20 > B type set are transactions, so it's impossible for me to post EXPLAIN = ANALYZE results. I've extracted two querys from a single transactions = and executed the twos on both system. Here are the results: >=20 > T420 >=20 > Query B_1 [55999.649 ms + 0.639 ms] http://explain.depesz.com/s/LbM >=20 > Query B_2 [95664.832 ms + 0.523 ms] http://explain.depesz.com/s/v06 >=20 > MacMini >=20 > Query B_1 [56315.614 ms] http://explain.depesz.com/s/uZTx >=20 > Query B_2 [44890.813 ms] http://explain.depesz.com/s/y7Dk >=20 >=20 > Looking at the 2 B_2 queries (since they are so drastically = different), the in-memory quicksorts stand out on the Dell as being = *drastically* slower than the disk-based sorts on your mac-mini.... >=20 >=20 >=20 >=20 > --=20 > Regards, > Ang Wei Shan --Apple-Mail=_D7B49AF7-8FF4-4B70-8E8E-0ADC3E14B2FB Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=windows-1252
Hi Wei Shan,
Thank you for your = response.
Query B was run after initializing the DB ex-novo = doing VACUUM ANALYZE before and after creating and clustering = indexes.
By the way, these results are consistent through time = and are reproducible, so it=92s not a metter of statistic collector (I = guess).
Your observation is the same done at dba.stackexchange.com and this = make me think that the built-in Postgres of OS X Server is = truly optimized. 

Best = regards,
 Pietro

PS on the other = response I reported both = postgresql.conf 


Il giorno = 01/apr/2015, alle ore 16:44, Wei Shan <weishan.ang@gmail.com> ha = scritto:

Just looking at the 2 B_2 queries, I'm = curious as to why is the execution plan different between the 2 = machines. Is the optimiser stats updated on both = databases?

Regards,
Wei = Shan

On 1 April 2015 at 22:32, Aidan Van Dyk <aidan@highrise.ca> wrote:
On Wed, Apr 1, 2015 at = 9:56 AM, Pietro Pugni <pietro.pugni@gmail.com> = wrote:
 

Now = let=92s propose some query profiling times.

B type set are = transactions, so it's impossible for me to post EXPLAIN = ANALYZE results. I've extracted two querys from a single = transactions and executed the twos on both system. Here are the = results:

T420

Query B_1 [55999.649 ms + = 0.639 ms] http://explain.depesz.com/s/LbM

Query B_2 = [95664.832 ms + 0.523 ms] http://explain.depesz.com/s/v06

MacMin= i

Query B_1 [56315.614 ms] http://explain.depesz.com/s/uZTx

Query B_2 = [44890.813 ms] http://explain.depesz.com/s/y7Dk


Looking at the 2 = B_2 queries (since they are so drastically different), the in-memory = quicksorts stand out on the Dell as being *drastically* slower than the = disk-based sorts on your mac-mini....




--
Regards,
Ang Wei Shan

= --Apple-Mail=_D7B49AF7-8FF4-4B70-8E8E-0ADC3E14B2FB--