X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 29FA83A1CF7 for ; Thu, 20 Jan 2005 16:31:34 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23163-07 for ; Thu, 20 Jan 2005 16:31:30 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.197]) by svr1.postgresql.org (Postfix) with ESMTP id B553C3A4804 for ; Thu, 20 Jan 2005 16:31:29 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 50so154047wri for ; Thu, 20 Jan 2005 08:31:29 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=GosVeqmGWYoanRTfh4AsxCzG+a6vxASotYPFjvfEj0zcoc76+BMqr+Ikc0yrdHKwZCEdUR0AVkEcqcckK06AqHxfWHpeSAmBtIuivfEZPLq2HvCr+i77+0KKZlBOD4WPMvV0T+FBVhBr8YUkoehlfxyaUw3Nn3LPGJ8dtDtfmKo= Received: by 10.54.10.12 with SMTP id 12mr23386wrj; Thu, 20 Jan 2005 08:31:29 -0800 (PST) Received: by 10.54.18.72 with HTTP; Thu, 20 Jan 2005 08:31:29 -0800 (PST) Message-ID: <33c6269f050120083177fa0e83@mail.gmail.com> Date: Thu, 20 Jan 2005 11:31:29 -0500 From: Alex Turner Reply-To: alex@neteconomist.com To: Matt Casters , pgsql-performance@postgresql.org Subject: Re: In-Reply-To: <20050120142603.GM10437@ns.snowman.net> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <16153.193.190.212.113.1106213675.squirrel@193.190.212.113> <20050120142603.GM10437@ns.snowman.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.046 tagged_above=0 required=5 tests=AWL, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200501/313 X-Sequence-Number: 10003 I am curious - I wasn't aware that postgresql supported partitioned tables, Could someone point me to the docs on this. Thanks, Alex Turner NetEconomist On Thu, 20 Jan 2005 09:26:03 -0500, Stephen Frost wrote: > * Matt Casters (Matt.Casters@advalvas.be) wrote: > > I have the go ahead of a customer to do some testing on Postgresql in a couple of weeks as a > > replacement for Oracle. > > The reason for the test is that the number of users of the warehouse is going to increase and this > > will have a serious impact on licencing costs. (I bet that sounds familiar) > > Rather familiar, yes... :) > > > We're running a medium sized data warehouse on a Solaris box (4CPU, 8Gb RAM) on Oracle. > > Basically we have 2 large fact tables to deal with: one going for 400M rows, the other will be > > hitting 1B rows soon. > > (around 250Gb of data) > > Quite a bit of data. There's one big thing to note here I think- > Postgres will not take advantage of multiple CPUs for a given query, > Oracle will. So, it depends on your workload as to how that may impact > you. Situations where this will be unlikely to affect you: > > Your main bottle-neck is IO/disk and not CPU. > You run multiple queries in parallel frequently. > There are other processes on the system which chew up CPU time anyway. > > Situations where you're likely to be affected would be: > > You periodically run one big query. > You run a set of queries in sequential order. > > > My questions to the list are: has this sort of thing been attempted before? If so, what where the > > performance results compared to Oracle? > > I'm pretty sure it's been attempted before but unfortunately I don't > have any numbers on it myself. My data sets aren't that large (couple > million rows) but I've found PostgreSQL at least as fast as Oracle for > what we do, and much easier to work with. > > > I've been reading up on partitioned tabes on pgsql, will the performance benefit will be > > comparable to Oracle partitioned tables? > > In this case I would think so, except that PostgreSQL still won't use > multiple CPUs for a given query, even against partitioned tables, aiui. > > > What are the gotchas? > > See above? :) Other issues are things having to do w/ your specific > SQL- Oracle's old join syntax isn't supported by PostgreSQL (what is it, > something like select x,y from a,b where x=%y; to do a right-join, > iirc). > > > Should I be testing on 8 or the 7 version? > > Now that 8.0 is out I'd say probably test with that and just watch for > 8.0.x releases before you go production, if you have time before you > have to go into production with the new solution (sounds like you do- > changing databases takes time anyway). > > > Thanks in advance for any help you may have, I'll do my best to keep pgsql-performance up to date > > on the results. > > Hope that helps. Others on here will correct me if I misspoke. :) > > Stephen > > >