X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 35791D1B53A for ; Wed, 29 Oct 2003 14:41:42 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 36999-01 for ; Wed, 29 Oct 2003 10:41:14 -0400 (AST) Received: from mailserv.mobilecohesion.com (picard.mobilecohesion.com [80.4.157.11]) by svr1.postgresql.org (Postfix) with ESMTP id C854BD1B523 for ; Wed, 29 Oct 2003 10:41:09 -0400 (AST) Received: from pestilence.mobilecohesion.com ([10.12.10.31] helo=pestilence) by mailserv.mobilecohesion.com with esmtp (Exim 4.10) id 1AErVT-0003c2-00; Wed, 29 Oct 2003 14:41:07 +0000 Content-Type: text/plain; charset="iso-8859-15" From: Damien Dougan Organization: Mobile Cohesion To: pgsql-performance@postgresql.org Subject: Re: Very Poor Insert Performance Date: Wed, 29 Oct 2003 14:40:06 +0000 User-Agent: KMail/1.4.1 Cc: Tom Lane References: <200310271626.52839.damien.dougan@mobilecohesion.com> <200310291022.24035.damien.dougan@mobilecohesion.com> <3667.1067437399@sss.pgh.pa.us> In-Reply-To: <3667.1067437399@sss.pgh.pa.us> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200310291440.06450.damien.dougan@mobilecohesion.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/724 X-Sequence-Number: 4472 On Wednesday 29 October 2003 2:23 pm, Tom Lane wrote: > Your initial message stated plainly that the problem was in INSERTs; > it's not surprising that you got unhelpful advice. But perhaps my use of the term "insert" to describe upload was a very bad c= all=20 given the domain of the list... I assure you I wasn't setting out to deceive anyone! The only location i us= ed=20 INSERT (ie as a Postgres keyword) was towards the end of my mail when I tri= ed=20 to highlight the fact we couldn't use COPY to upload our data because of th= e=20 difficulty in maintaining the code to generate inter-table relations ahead = of=20 time. The problem was showing itself during database upload - so I assumed (ASS o= ut=20 of U and ME and all that!) that the write delay was very large (hence the= =20 disappointing improvements by switching off fsync etc). It was only after= =20 further investigation that we discovered that simulated INSERTs were going= =20 fine, but the Read delays between INSERTs where holding us up. > Your LEFT JOINs are constraining the join order --- see > http://www.postgresql.org/docs/7.3/static/explicit-joins.html > You'll need to reorder the joins into something that does what you want. Thanks very much for the heads-up, we'll reorder the joins into something m= ore=20 effecient! Damien