Re: Partition performance causing ddl commands to slow down significantly

On 4/12/2013 12:10 PM, fburgess(at)radiantblue(dot)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