On 4/12/2013 12:10 PM, fburg...@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
