Re: In progress INSERT wrecks plans on table

От: Tom Lane
Тема: Re: In progress INSERT wrecks plans on table
Дата: ,
Msg-id: 3833.1368192658@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: In progress INSERT wrecks plans on table  (Mark Kirkwood)
Ответы: Re: In progress INSERT wrecks plans on table  (Mark Kirkwood)
Список: 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, )

Mark Kirkwood <> writes:
> Unfortunately a trigger will not really do the job - analyze ignores in
> progress rows (unless they were added by the current transaction), and
> then the changes made by analyze are not seen by any other sessions. So
> no changes to plans until the entire INSERT is complete and COMMIT
> happens (which could be a while - too long in our case).

I'm not sure I believe the thesis that plans won't change at all.
The planner will notice that the physical size of the table is growing.
That may not be enough, if the table-contents statistics are missing
or completely unreflective of reality, but it's something.

It is true that *already cached* plans won't change until after an
ANALYZE is done (the key point there being that ANALYZE sends out a
shared-inval message to force replanning of plans for the table).
Conceivably you could issue concurrent ANALYZEs occasionally while
the INSERT is running, not so much to update the stats --- because
they wouldn't --- as to force cached-plan invalidation.

            regards, tom lane



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

От: Tom Lane
Дата:
Сообщение: Re: In progress INSERT wrecks plans on table
От: Robert Haas
Дата:
Сообщение: Re: PostgreSQL planner