Message-ID: <518CED48.1030706@catalyst.net.nz>
Date: Sat, 11 May 2013 00:51:20 +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: Vitalii Tymchyshyn <tivv00@gmail.com>
CC: pgsql-performance@postgresql.org,
	Gavin Flower <gavinflower@archidevsys.co.nz>,
	Tom Lane <tgl@sss.pgh.pa.us>, Simon Riggs <simon@2ndquadrant.com>
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>
	<CABWW-d3A3r6sQW71phLJqGkZBdbWOxo+WsrA3qwpx3F5KYRM1g@mail.gmail.com>
In-Reply-To: 
 <CABWW-d3A3r6sQW71phLJqGkZBdbWOxo+WsrA3qwpx3F5KYRM1g@mail.gmail.com>
Content-Type: text/plain; charset=KOI8-U; format=flowed
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

(See below for the reply)

On 10/05/13 22:48, Vitalii Tymchyshyn wrote:
> Well, could you write a trigger that would do what you need? AFAIR
> analyze data is stored no matter transaction boundaries. You could store
> some counters in session vars and issue an explicit analyze when enough
> rows were added.
>
> 7 =D4=D2=C1=D7. 2013 08:33, "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz
> <mailto:mark.kirkwood@catalyst.net.nz>> =CE=C1=D0=C9=D3.
>
>     On 07/05/13 18:10, Simon Riggs wrote:
>
>         On 7 May 2013 01:23,  <mark.kirkwood@catalyst.net.nz
>         <mailto: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 chun=
ks?
>
>
>     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 :-)
>
>     I'm still thinking that making postgres smarter about having current
>     stats for getting the actual optimal plan is the best solution.

Unfortunately a trigger will not really do the job - analyze ignores in=20
progress rows (unless they were added by the current transaction), and=20
then the changes made by analyze are not seen by any other sessions. So=20
no changes to plans until the entire INSERT is complete and COMMIT=20
happens (which could be a while - too long in our case).

Figuring out how to improve on this situation is tricky.


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