Re: Very Poor Insert 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