Re: In progress INSERT wrecks plans on table

От: Matt Clarkson
Тема: Re: In progress INSERT wrecks plans on table
Дата: ,
Msg-id: 1367871532.31789.22.camel@oldgreg.wgtn.cat-it.co.nz
(см: обсуждение, исходный текст)
Ответ на: Re: In progress INSERT wrecks plans on table  (Simon Riggs)
Список: 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, )

>
> (2) seems fairly hard generically, since we'd have to keep track of
> the tids returned from the IndexScan to allow us to switch to a
> different plan and avoid re-issuing rows that we've already returned.
> But maybe if we adapted the IndexScan plan type so that it adopted a
> more page oriented approach internally, it could act like a
> bitmapscan. Anyway, that would need some proof that it would work and
> sounds like a fair task.
>
> (1) sounds more easily possible and plausible. At the moment we have
> enable_indexscan = off. If we had something like
> plan_cost_weight_indexscan = N, we could selectively increase the cost
> of index scans so that they would be less likely to be selected. i.e.
> plan_cost_weight_indexscan = 2 would mean an indexscan would need to
> be half the cost of any other plan before it was selected. (parameter
> name selected so it could apply to all parameter types). The reason to
> apply this weighting would be to calculate "risk adjusted cost" not
> just estimated cost.
>
> --
>  Simon Riggs                   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services


Another option would be for the bulk insert/update/delete to track the
distribution stats as the operation progresses and if it detects that it
is changing the distribution of data beyond a certain threshold it would
update the pg stats accordingly.

--
Matt Clarkson
Catalyst.Net Limited





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

От: Igor Neyman
Дата:
Сообщение: Re: Deterioration in performance when query executed in multi threads
От: Simon Riggs
Дата:
Сообщение: Re: In progress INSERT wrecks plans on table