From owner-freebsd-questions@FreeBSD.ORG Fri Feb 23 18:17:15 2007 Return-Path: X-Original-To: freebsd-questions@freebsd.org Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [69.147.83.52]) by hub.freebsd.org (Postfix) with ESMTP id BFC8316A401 for ; Fri, 23 Feb 2007 18:17:15 +0000 (UTC) (envelope-from wmoran@collaborativefusion.com) Received: from mx00.pub.collaborativefusion.com (mx00.pub.collaborativefusion.com [206.210.89.199]) by mx1.freebsd.org (Postfix) with ESMTP id 847B813C4A6 for ; Fri, 23 Feb 2007 18:17:15 +0000 (UTC) (envelope-from wmoran@collaborativefusion.com) Received: from vanquish.pgh.priv.collaborativefusion.com (vanquish.pgh.priv.collaborativefusion.com [192.168.2.61]) (SSL: TLSv1/SSLv3,256bits,AES256-SHA) by wingspan with esmtp; Fri, 23 Feb 2007 13:17:13 -0500 id 00056403.45DF2FA9.0000A04E Date: Fri, 23 Feb 2007 13:17:13 -0500 From: Bill Moran To: Harald Schmalzbauer Message-Id: <20070223131713.39a96d18.wmoran@collaborativefusion.com> In-Reply-To: <200702231842.17878.h.schmalzbauer@omnisec.de> References: <200702231729.15068.h.schmalzbauer@omnisec.de> <20070223120504.bef97395.wmoran@collaborativefusion.com> <200702231842.17878.h.schmalzbauer@omnisec.de> Organization: Collaborative Fusion X-Mailer: Sylpheed 2.3.0 (GTK+ 2.10.9; i386-portbld-freebsd6.1) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Cc: freebsd-questions@freebsd.org Subject: Re: PotsgreSQL question (differences for 7.4 ,8.1 and 8.2) X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 23 Feb 2007 18:17:15 -0000 In response to Harald Schmalzbauer : > Am Freitag, 23. Februar 2007 18:05 schrieb Bill Moran: > > In response to Harald Schmalzbauer : > > > Hello, > > > > > > I'm new to PostgreSQL and see that there are 3 versions in the Ports and > > > bsd.mk.ports defaults to 7.4 to resolve pgsql dependencies. > > > I yahood/msnd/googled a lot but couldn't find a comprehensive comparison > > > regarding features/performance. There are severaly MySQL comparisions but > > > that aren't of interest any more since that's what I've been using > > > before. > > > > > > So please can one explain me in short words what the main featrue > > > difference is between 7.4, 8.1, 8.2? (I found out that 7.4 and 8.2 are > > > incompatible regarding php5-pgsql conjunction) > > > > 7.4 is good. 8.1 is better. 8.2 is best. > > > > Feature changes are minimal. 8.2 has significant performance improvements > > over previous version. > > Version or versions? I mean, even better than 7.4? I read some tests that > performance of 8.x is in some statements a big regression (runtime 25ms on > 7.4 while 8.x needs 1000ms (1s!)) Where did you read this? A good example is to try SELECT count(*) FROM xxx on both systems. 8.2 is _significantly_ faster than 7.x. There have been optimizations added for this specific case. I'm not aware of any significant regressions. I'd be interested to be proven wrong, but every time I've seen it come up in conversation or actual testing, the latest version is always the fastest. One specific thing is that versions newer than 7 have much better management of shared memory. This more efficient usage means you can allocate more shared memory to Postgres and get additional performance gains (due to caching) than were possible in 7.x. > It sounds that you have done some real world and sensible tests, that's why > I'm asking. I don't give too much for synthetic benchmark results, but a > factor of 40 surely would be noticable in real world... I don't have any real-world tests that could give any real numbers between specific versions. My answer is based on personal "feel" over the last few years, as well as involvement with the postgres-perform mailing lists. -- Bill Moran Collaborative Fusion Inc.