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

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Cc: Vitalii Tymchyshyn <tivv00@gmail.com>
Cc: pgsql-performance@postgresql.org, Gavin Flower <gavinflower@archidevsys.co.nz>
Cc: Simon Riggs <simon@2ndquadrant.com>
Subject: Re: In progress INSERT wrecks plans on table
Date: Fri, 10 May 2013 09:30:58 -0400
Message-ID: <3833.1368192658@sss.pgh.pa.us> (raw)
In-Reply-To: <518CED48.1030706@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>
	<CABWW-d3A3r6sQW71phLJqGkZBdbWOxo+WsrA3qwpx3F5KYRM1g@mail.gmail.com>
	<518CED48.1030706@catalyst.net.nz>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

Mark Kirkwood <mark.kirkwood@catalyst.net.nz> writes:
> Unfortunately a trigger will not really do the job - analyze ignores in 
> progress rows (unless they were added by the current transaction), and 
> then the changes made by analyze are not seen by any other sessions. So 
> no changes to plans until the entire INSERT is complete and COMMIT 
> happens (which could be a while - too long in our case).

I'm not sure I believe the thesis that plans won't change at all.
The planner will notice that the physical size of the table is growing.
That may not be enough, if the table-contents statistics are missing
or completely unreflective of reality, but it's something.

It is true that *already cached* plans won't change until after an
ANALYZE is done (the key point there being that ANALYZE sends out a
shared-inval message to force replanning of plans for the table).
Conceivably you could issue concurrent ANALYZEs occasionally while
the INSERT is running, not so much to update the stats --- because
they wouldn't --- as to force cached-plan invalidation.

			regards, tom lane


--
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: <3833.1368192658@sss.pgh.pa.us>
Permalink:  ../3833.1368192658@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3833.1368192658@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, mark.kirkwood@catalyst.net.nz, tivv00@gmail.com, gavinflower@archidevsys.co.nz, simon@2ndquadrant.com
  Subject: Re: In progress INSERT wrecks plans on table
  In-Reply-To: <3833.1368192658@sss.pgh.pa.us>

* 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