Message-ID: <5188B8AF.6010003@catalyst.net.nz>
Date: Tue, 07 May 2013 20:17:51 +1200
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/20130404 Thunderbird/17.0.5
MIME-Version: 1.0
To: Simon Riggs <simon@2ndQuadrant.com>
CC: Tom Lane <tgl@sss.pgh.pa.us>,
	Gavin Flower <gavinflower@archidevsys.co.nz>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: In progress INSERT wrecks plans on table
References: <5179E77B.7050601@catalyst.net.nz>
	<5179ECC5.2010206@archidevsys.co.nz>
	<5179F259.3010509@catalyst.net.nz>
	<5179F5C6.8070702@archidevsys.co.nz>
	<5180E3C8.3010201@catalyst.net.nz> <2121.1367417215@sss.pgh.pa.us>
	<5181B80D.70404@catalyst.net.nz>
	<CA+U5nMKYYZDekhtxz1E1B11dAm9UJoHBQHrB6DiiZDJo3fLd0Q@mail.gmail.com>
	<916bff05a5ab130c76d7835abbaf7762.squirrel@mail.catalyst.net.nz>
	<CA+U5nMJHjQccVSB3SkAHdRn2AfuuO49wA8-ZGB366cddTynjdQ@mail.gmail.com>
	<CA+U5nMKMfZUEimZ9=oF2wJ-WkiGhMw3mQjUFT6PBGofUpmXL8A@mail.gmail.com>
	<51870CB6.6090404@catalyst.net.nz>
	<CA+U5nMJHgHczmWrKzst=PJeP+9BHJAsTgRNXyVZX5Oyys2zn_g@mail.gmail.com>
	<feb60a4b1d9e3af7b646b0274064a1d9.squirrel@mail.catalyst.net.nz>
	<CA+U5nMJqUHE-t5eBuMZGu-_0rJbfoqrYJ=oi_xvKhrq8_31F2A@mail.gmail.com>
	<51889FF4.7000306@catalyst.net.nz>
	<CA+U5nMJkCKreyNbTJ_vui3PQgd0Mf7aDA_RK72cgOtS552Pr_A@mail.gmail.com>
In-Reply-To: 
 <CA+U5nMJkCKreyNbTJ_vui3PQgd0Mf7aDA_RK72cgOtS552Pr_A@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance