Re: In progress INSERT wrecks plans on table

On 7 May 2013 07:32, Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> wrote:
> On 07/05/13 18:10, Simon Riggs wrote:
>>
>> On 7 May 2013 01:23, <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> 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 :-)

Yes, we'd need to break up SQL statements into pieces and use external
transaction snapshots to do that.

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

I agree.

The challenge now is to come up with something that actually works;
most of the ideas have been very vague and ignore the many downsides.
The hard bit is the analysis and balanced thinking, not the
developing.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services