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