From: | Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> |
---|---|
To: | Simon Riggs <simon(at)2ndQuadrant(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Gavin Flower <gavinflower(at)archidevsys(dot)co(dot)nz>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: In progress INSERT wrecks plans on table |
Date: | 2013-05-07 08:17:51 |
Message-ID: | 5188B8AF.6010003@catalyst.net.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Langote | 2013-05-07 09:20:18 | XLogFlush invoked about twice as much after 9.2 group commit enhancement |
Previous Message | Stefan Kaltenbrunner | 2013-05-07 08:09:39 | Re: spoonbill vs. -HEAD |
From | Date | Subject | |
---|---|---|---|
Next Message | Anne Rosset | 2013-05-07 17:53:41 | Re: Deterioration in performance when query executed in multi threads |
Previous Message | Simon Riggs | 2013-05-07 07:33:36 | Re: In progress INSERT wrecks plans on table |