Re: In progress INSERT wrecks plans on table

On 07/05/13 19:33, Simon Riggs wrote:
> 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.
>

Yeah - seeing likely downsides can be a bit tricky too. I'll have a play
with some prototyping ideas, since this is actually an area of postgres
(analyze/stats collector) that I've fiddled with before :-)

Cheers

Mark