Re: Very Poor Insert Performance - Mailing list pgsql-performance

From Tom Lane
Subject Re: Very Poor Insert Performance
Date
Msg-id 3667.1067437399@sss.pgh.pa.us
Whole thread Raw
In response to Re: Very Poor Insert Performance  (Damien Dougan <damien.dougan@mobilecohesion.com>)
Responses Re: Very Poor Insert Performance  (Damien Dougan <damien.dougan@mobilecohesion.com>)
List pgsql-performance
Damien Dougan <damien.dougan@mobilecohesion.com> 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 -
max16, 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
thepublic 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

pgsql-performance by date:

Previous
From: Robert Treat
Date:
Subject: redundent index?
Next
From: Damien Dougan
Date:
Subject: Re: Very Poor Insert Performance