From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Simon Riggs <simon@2ndQuadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Gavin Flower <gavinflower@archidevsys.co.nz>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: In progress INSERT wrecks plans on table
Date: Tue, 07 May 2013 20:17:51 +1200
Message-ID: <5188B8AF.6010003@catalyst.net.nz> (raw)
In-Reply-To: <CA+U5nMJkCKreyNbTJ_vui3PQgd0Mf7aDA_RK72cgOtS552Pr_A@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.kirkwood@catalyst.net.nz, simon@2ndQuadrant.com, tgl@sss.pgh.pa.us, gavinflower@archidevsys.co.nz
Subject: Re: In progress INSERT wrecks plans on table
In-Reply-To: <5188B8AF.6010003@catalyst.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox