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 7014CD1B55D for ; Wed, 29 Oct 2003 14:24:26 +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 33087-06 for ; Wed, 29 Oct 2003 10:23:58 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 0220AD1B545 for ; Wed, 29 Oct 2003 10:23:38 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h9TENJit003668; Wed, 29 Oct 2003 09:23:19 -0500 (EST) To: Damien Dougan Cc: pgsql-performance@postgresql.org Subject: Re: Very Poor Insert Performance In-reply-to: <200310291022.24035.damien.dougan@mobilecohesion.com> References: <200310271626.52839.damien.dougan@mobilecohesion.com> <2493.1067285528@sss.pgh.pa.us> <200310291022.24035.damien.dougan@mobilecohesion.com> Comments: In-reply-to Damien Dougan message dated "Wed, 29 Oct 2003 10:22:24 +0000" Date: Wed, 29 Oct 2003 09:23:19 -0500 Message-ID: <3667.1067437399@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/723 X-Sequence-Number: 4471 Damien Dougan writes: > Now, our problem seems to be the delays introduced by reading from the > public views. Your initial message stated plainly that the problem was in INSERTs; it's not surprising that you got unhelpful advice. > View definition: > [ huge view full of LEFT JOINs ] > As you can see, the PublicView is resulting in a huge nested loop, > with an index scan of the contact only occurring at the end. I would > have expected something more like: > (1) An index scan of the contact table to determine the correct contact > (2) An index scan of the address table using the rel_contact_has_address.address_id to obtain the (relatively small - max 16, and typically 2) addresses > (3) A number of joins - at the same level rather than looping - to obtain the detailnames for the new column names of the public view 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. regards, tom lane