Skip site navigation (1) Skip section navigation (2)

Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT

From: John Papandriopoulos <dr(dot)jpap(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
Date: 2010-12-05 11:10:04
Message-ID: idfruc$aiq$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: John PapandriopoulosDate: 2010-12-05 11:14:46
Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
Previous:From: John PapandriopoulosDate: 2010-12-05 11:06:39
Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group