pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Matt Clarkson <mattc@catalyst.net.nz>
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Cc: 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 19:19:25 +1200
Message-ID: <1367911165.32561.7.camel@matt-ultrabook> (raw)
In-Reply-To: <51889FF4.7000306@catalyst.net.nz>
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>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>


On Tue, 2013-05-07 at 18:32 +1200, Mark Kirkwood 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 :-)

We also had the same problem with an UPDATE altering the data
distribution in such a way that trivial but frequently executed queries
cause massive server load until auto analyze sorted out the stats.

-- 
Matt Clarkson
Catalyst.Net Limited




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



view thread (42+ messages)  latest in thread

Message-ID: <1367911165.32561.7.camel@matt-ultrabook>
Permalink:  ../1367911165.32561.7.camel@matt-ultrabook/
Also on:    postgresql.org/message-id/1367911165.32561.7.camel@matt-ultrabook

 · 

reply
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: mattc@catalyst.net.nz, 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: <1367911165.32561.7.camel@matt-ultrabook>

* 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