From: Jochen Erwied <jochen@pgsql-performance.erwied.eu>
To: Mladen Gogala <mladen.gogala@vmsinfo.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: John Papandriopoulos <dr.jpap@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
Date: Sun, 5 Dec 2010 00:38:39 +0100
Message-ID: <1628298022.20101205003839@erwied.eu> (raw)
In-Reply-To: <4CFACC81.3050901@vmsinfo.com>
References: <idbo6g$92r$1@news.hub.org>
<14012.1291443654@sss.pgh.pa.us>
<idd14l$a4u$1@news.hub.org>
<23792.1291480923@sss.pgh.pa.us>
<idec54$2hpd$1@news.hub.org>
<13159.1291502400@sss.pgh.pa.us>
<4CFACC81.3050901@vmsinfo.com>
Sunday, December 5, 2010, 12:19:29 AM you wrote:
> Hmmm, what happens if I need 10 years of data, in monthly partitions? It
> would be 120 partitions. Can you please elaborate on that limitation?
> Any plans on lifting that restriction?
I'm running a partitioning scheme using 256 tables with a maximum of 16
million rows (namely IPv4-addresses) and a current total of about 2.5
billion rows, there are no deletes though, but lots of updates.
Using triggers or rules on the main table in my case showed to be not very
effective, so I reverted to updating the inherited tables directly. This
way you still can use a SELECT on the main table letting the optimizer do
it's work, but do not run into the problem of oversized shared memory usage
when doing DELETEs or UPDATEs
IMHO if you are using large partitioning schemes, handle the logic of which
table to update or delete in your application. In most cases extending the
underlying application will be much less work and more flexible than trying
to write a dynamic rule/trigger to do the same job.
--
Jochen Erwied | home: jochen@erwied.eu +49-208-38800-18, FAX: -19
Sauerbruchstr. 17 | work: joe@mbs-software.de +49-2151-7294-24, FAX: -50
D-45470 Muelheim | mobile: jochen.erwied@vodafone.de +49-173-5404164
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: jochen@pgsql-performance.erwied.eu, mladen.gogala@vmsinfo.com, tgl@sss.pgh.pa.us, dr.jpap@gmail.com
Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
In-Reply-To: <1628298022.20101205003839@erwied.eu>
* 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