From: | "Jerry Champlin" <jchamplin(at)absolute-performance(dot)com> |
---|---|
To: | "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Is there a way to SubPartition? |
Date: | 2008-08-27 20:21:41 |
Message-ID: | 006f01c90882$84f80ff0$8ee82fd0$@com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
If it were implemented in such a way that when the top level pruning
happens, a set of 3 sub partitions is selected from say 18 total and then at
the next level is selects the 3 matching sub partitions from each matched
group of 30 then you are only looking at 18+3*30 = 108 instead of 548 checks
to evaluate <example assumes monthly first level partitioning and daily sub
partitioning>. If this is not supported, then we will need to solve the
problem a different way - probably weekly partitions and refactor the code
to decrease updates by at least an order of magnitude. While we are in the
process of doing this, is there a way to make updates faster? Postgresql is
spending a lot of CPU cycles for each HOT update. We have
synchronous_commit turned off, commit siblings set to 5, commit_delay set to
50,000. With synchronous_commit off does it make any sense to be grouping
commits? Buffers written by the bgwriter vs checkpoint is 6 to 1. Buffers
written by clients vs buffers by checkpoint is 1 to 6. Is there anything
obvious here?
-Jerry
-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, August 27, 2008 8:02 AM
To: Jerry Champlin
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Is there a way to SubPartition?
Jerry Champlin <jchamplin(at)absolute-performance(dot)com> writes:
> Is there a way to use multi-level inheritance to achieve sub
> partitioning that the query optimizer will recognize?
No, I don't think so. How would that make things any better anyway?
You're still going to end up with the same very large number of
partitions.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2008-08-27 20:32:34 | Re: Is there a way to SubPartition? |
Previous Message | Shane Ambler | 2008-08-27 16:45:52 | Re: select on 22 GB table causes "An I/O error occured while sending to the backend." exception |