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 228A4D1B1B9 for ; Tue, 22 Jun 2004 14:47:14 -0300 (ADT) 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 81790-08 for ; Tue, 22 Jun 2004 17:47:11 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 258B0D1B1C8 for ; Tue, 22 Jun 2004 14:47:09 -0300 (ADT) Received: (qmail 20271 invoked by uid 500); 22 Jun 2004 17:53:28 -0000 Date: Tue, 22 Jun 2004 12:53:28 -0500 From: Bruno Wolff III To: Bill Cc: Josh Berkus , pgsql-performance@postgresql.org Subject: Re: postgresql and openmosix migration Message-ID: <20040622175328.GA20086@wolff.to> Mail-Followup-To: Bill , Josh Berkus , pgsql-performance@postgresql.org References: <004001c45865$59cc9f20$dfc58780@billnotebook> <200406220931.23780.josh@agliodbs.com> <008201c4587e$b8856510$dfc58780@billnotebook> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <008201c4587e$b8856510$dfc58780@billnotebook> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/231 X-Sequence-Number: 7289 On Tue, Jun 22, 2004 at 12:31:15 -0500, Bill wrote: > Ok, so maybe someone on this group will have a better idea. We have a > database of financial information, and this has literally millions of > entries. I have installed indicies, but for the rather computationally > demanding processes we like to use, like a select query to find the > commodity with the highest monthly or annual returns, the computer generally > runs unacceptably slow. So, other than clustring, how could I achieve a > speed increase in these complex queries? Is this better in mysql or > postgresql? Queries using max (or min) can often be rewritten as queries using ORDER BY and LIMIT so that they can take advantage of indexes. Doing this might help with some of the problems you are seeing. If you commonly query on aggregated data it might be better to create derived tables of the aggregated data maintained by triggers, and query against them. If you do lots of selects relative to inserts and updates, this could be a big win.