Re: Very Poor Insert Performance

Damien Dougan <damien(dot)dougan(at)mobilecohesion(dot)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 - 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