Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT

От: Tom Lane
Тема: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
Дата: ,
Msg-id: 26275.1291568163@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos)
Ответы: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
 Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
  Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
   Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
    Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
     Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
      Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Mladen Gogala, )
       Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Jochen Erwied, )
        Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
       Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
      Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
       Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
        Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
         Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
          Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
           Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
    Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Mladen Gogala, )

John Papandriopoulos <> writes:
> On 12/4/10 2:40 PM, Tom Lane wrote:
>> [ pokes at that for a bit ... ]  Ah, I had forgotten that UPDATE/DELETE
>> go through inheritance_planner() while SELECT doesn't.  And
>> inheritance_planner() makes a copy of the querytree, including the
>> already-expanded range table, for each target relation.  So the memory
>> usage is O(N^2) in the number of child tables.

> Thanks for the pointer to the code and explanation.

> In inheritance_planner(...) I see the memcpy of the input query tree, but for my example constraint exclusion would
onlyresult in one child being included.  Or is the O(N^2) memory usage from elsewhere? 

It's copying the whole range table, even though any one child query only
needs one of the child table entries.  There might be some way to
finesse that, but it's not clear how else to end up with a final plan
tree in which each child table has the correct RT index number.

You could get rid of the memory growth, at the cost of a lot of
tree-copying, by doing each child plan step in a discardable memory
context.  I'm not sure that'd be a win for normal sizes of inheritance
trees though --- you'd need to copy the querytree in and then copy the
resulting plantree out again, for each child.  (Hm, but we're doing the
front-end copy already ...)

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: Rob Wultsch
Дата:
Сообщение: Group commit and commit delay/siblings
От: Jignesh Shah
Дата:
Сообщение: Re: Group commit and commit delay/siblings