From: John Papandriopoulos <dr.jpap@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
Date: Sun, 05 Dec 2010 03:10:04 -0800
Message-ID: <idfruc$aiq$1@news.hub.org> (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>
On 12/4/10 3:19 PM, Mladen Gogala wrote:
> Tom Lane wrote:
>> Partitioning using these techniques will work well with up to
>> perhaps a hundred partitions; don't try to use many thousands of
>> partitions.
>> regards, tom lane
> 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?
>
Even with 1k partitions, I don't have any issues any of the SELECT, UPDATE or DELETE queries and with 8GB RAM.
I suppose if you're using INSERT triggers, you'd want to make sure your plpgsql function is fast: I'm partitioning by power-of-two, so can use right-shift n-bits to quickly compute the insertion table name, rather than using an if-else-if chain.
John
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: dr.jpap@gmail.com
Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
In-Reply-To: <idfruc$aiq$1@news.hub.org>
* 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