Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 313C44770AD for ; Wed, 15 Jan 2003 16:03:35 -0500 (EST) Received: from mail.libertyrms.com (unknown [209.167.124.227]) by postgresql.org (Postfix) with ESMTP id 0C1E2477070 for ; Wed, 15 Jan 2003 15:10:12 -0500 (EST) Received: from andrew by mail.libertyrms.com with local (Exim 3.22 #3 (Debian)) id 18YtrY-0005zb-00 for ; Wed, 15 Jan 2003 15:10:12 -0500 Date: Wed, 15 Jan 2003 15:10:12 -0500 From: Andrew Sullivan To: pgsql-performance@postgresql.org Subject: Re: 7.3.1 New install, large queries are slow Message-ID: <20030115151012.A21930@mail.libertyrms.com> Mail-Followup-To: Andrew Sullivan , pgsql-performance@postgresql.org References: <9B1C77393DED0D4B9DAA1AA1742942DA0E4BF9@pos_pdc.posportal.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <9B1C77393DED0D4B9DAA1AA1742942DA0E4BF9@pos_pdc.posportal.com>; from rfail@posportal.com on Wed, Jan 15, 2003 at 10:00:04AM -0800 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/123 X-Sequence-Number: 770 On Wed, Jan 15, 2003 at 10:00:04AM -0800, Roman Fail wrote: > I have a query that does many joins (including two very big tables) > which is slow on Postgres. On PGSQL the query takes 19 minutes, There are three things I can think of right off the bat. First, the performance of foreign keys is flat-out awful in Postgres. I suggest avoiding them if you can. Second, ordering joins explicitly (with the JOIN keyword) constrains the planner, and may select bad plan. The explain analyse output was nice, but I didn't see the query, so I can't tell what the plan maybe ought to be. Third, I didn't see any suggestion that you'd moved the WAL onto its own disk. That will mostly help when you are under write load; I guess it's not a problem here, but it's worth keeping in mind. A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada M2P 2A8 +1 416 646 3304 x110