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

help / color / mirror / Atom feed
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 18:32:20 +1200
Message-ID: <51889FF4.7000306@catalyst.net.nz> (raw)
In-Reply-To: <CA+U5nMJqUHE-t5eBuMZGu-_0rJbfoqrYJ=oi_xvKhrq8_31F2A@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>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

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 :-)

I'm still thinking that making postgres smarter about having current 
stats for getting the actual optimal plan is the best solution.

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



view thread (42+ messages)  latest in thread

Message-ID: <51889FF4.7000306@catalyst.net.nz>
Permalink:  ../51889FF4.7000306@catalyst.net.nz/
Also on:    postgresql.org/message-id/51889FF4.7000306@catalyst.net.nz

 · 

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: 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: <51889FF4.7000306@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