Re: Very Poor Insert Performance

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Very Poor Insert Performance
Дата
Msg-id 3667.1067437399@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: Very Poor Insert Performance  (Damien Dougan <damien.dougan@mobilecohesion.com>)
Ответы Re: Very Poor Insert Performance  (Damien Dougan <damien.dougan@mobilecohesion.com>)
Список 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 по дате отправления:

Предыдущее
От: Robert Treat
Дата:
Сообщение: redundent index?
Следующее
От: Damien Dougan
Дата:
Сообщение: Re: Very Poor Insert Performance