Re: In progress INSERT wrecks plans on table

От: Mark Kirkwood
Тема: Re: In progress INSERT wrecks plans on table
Дата: ,
Msg-id: 51889FF4.7000306@catalyst.net.nz
(см: обсуждение, исходный текст)
Ответ на: Re: In progress INSERT wrecks plans on table  (Simon Riggs)
Ответы: Re: In progress INSERT wrecks plans on table  (Matt Clarkson)
Re: In progress INSERT wrecks plans on table  (Simon Riggs)
Re: In progress INSERT wrecks plans on table  (Vitalii Tymchyshyn)
Список: pgsql-performance

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

In progress INSERT wrecks plans on table  (Mark Kirkwood, )
 Re: In progress INSERT wrecks plans on table  (Mark Kirkwood, )
 Re: In progress INSERT wrecks plans on table  (Mark Kirkwood, )
  Re: In progress INSERT wrecks plans on table  (Tom Lane, )
   Re: In progress INSERT wrecks plans on table  (Mark Kirkwood, )
    Re: In progress INSERT wrecks plans on table  (Simon Riggs, )
     Re: In progress INSERT wrecks plans on table  (, )
      Re: In progress INSERT wrecks plans on table  (Thomas Kellerer, )
       Re: In progress INSERT wrecks plans on table  (, )
      Re: In progress INSERT wrecks plans on table  (Simon Riggs, )
       Re: In progress INSERT wrecks plans on table  (Simon Riggs, )
        Re: In progress INSERT wrecks plans on table  (Mark Kirkwood, )
         Re: In progress INSERT wrecks plans on table  (Simon Riggs, )
          Re: In progress INSERT wrecks plans on table  (Matt Clarkson, )
          Re: In progress INSERT wrecks plans on table  (, )
           Re: In progress INSERT wrecks plans on table  (Simon Riggs, )
            Re: In progress INSERT wrecks plans on table  (Mark Kirkwood, )
             Re: In progress INSERT wrecks plans on table  (Matt Clarkson, )
             Re: In progress INSERT wrecks plans on table  (Simon Riggs, )
              Re: In progress INSERT wrecks plans on table  (Mark Kirkwood, )
             Re: In progress INSERT wrecks plans on table  (Vitalii Tymchyshyn, )
              Re: In progress INSERT wrecks plans on table  (Mark Kirkwood, )
               Re: In progress INSERT wrecks plans on table  (Tom Lane, )
                Re: In progress INSERT wrecks plans on table  (Mark Kirkwood, )
          Re: In progress INSERT wrecks plans on table  (Jeff Janes, )
           Re: In progress INSERT wrecks plans on table  (Ants Aasma, )
         Re: In progress INSERT wrecks plans on table  (Heikki Linnakangas, )
          Re: In progress INSERT wrecks plans on table  (Tom Lane, )
          Re: In progress INSERT wrecks plans on table  (Simon Riggs, )
          Re: In progress INSERT wrecks plans on table  (Jeff Janes, )
       Re: In progress INSERT wrecks plans on table  (Heikki Linnakangas, )
        Re: In progress INSERT wrecks plans on table  (Simon Riggs, )
     Re: In progress INSERT wrecks plans on table  (Gavin Flower, )

On 07/05/13 18:10, Simon Riggs wrote:
> On 7 May 2013 01:23,  <> wrote:
>
>> I'm thinking that a variant of (2) might be simpler to inplement:
>>
>> (I think Matt C essentially beat me to this suggestion - he originally
>> discovered this issue). It is probably good enough for only *new* plans to
>> react to the increased/increasing number of in progress rows. So this
>> would require backends doing significant numbers of row changes to either
>> directly update pg_statistic or report their in progress numbers to the
>> stats collector. The key change here is the partial execution numbers
>> would need to be sent. Clearly one would need to avoid doing this too
>> often (!) - possibly only when number of changed rows >
>> autovacuum_analyze_scale_factor proportion of the relation concerned or
>> similar.
>
> Are you loading using COPY? Why not break down the load into chunks?
>

INSERT - but we could maybe workaround by chunking the INSERT. However
that *really* breaks the idea that in SQL you just say what you want,
not how the database engine should do it! And more practically means
that the most obvious and clear way to add your new data has nasty side
effects, and you have to tip toe around muttering secret incantations to
make things work well :-)

I'm still thinking that making postgres smarter about having current
stats for getting the actual optimal plan is the best solution.

Cheers

Mark




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

От: Mark Kirkwood
Дата:
Сообщение: Re: In progress INSERT wrecks plans on table
От: Anne Rosset
Дата:
Сообщение: Re: Deterioration in performance when query executed in multi threads