Re: Partition performance causing ddl commands to slow down significantly

On 4/12/2013 12:10 PM, fburgess@radiantblue.com wrote:
> The SQL query should be: select count(*) from dna_strands where cid = 1;

ah.  I suspected as much.    its so hard to analyze problems with
incorrect information and so easy to make wrong assumptions.

> I  just realize don't think this is not going to work. if for the sake
> of argument that cid = 1 is much more likely be be found in a more
> recent partition, any inverse search mechanism in the planner will
> find that match first but then continue through all of the other
> partitions, Right? The only way to optimize this is to find some way
> of adding the partition key date to the where clause.

yeah, pretty much.   all partitions HAVE to be scanned in case there's a
row with that condition, there's no way of avoiding that.

and yeah, abstraction layers like Hibernate make things even harder to
manage

btw, I think you said 268 child partitions.  that is, in my experience,
way too many.   we try and keep partitions under a few dozen even on our
multi-terabyte tables.   6 months by week is about as far as we go.
with your 10 year data, I'd probably partition by quarter, or something.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast