On Thu, 10 Jul 2014 13:16:05 -0700 Jeff Janes <jeff.janes@xxxxxxxxx> wrote: > > > > In general, yes, given the information you provided. A parition on > > ParentID % $something should improve performance. > > PostgresSQL's constraint exclusion logic is not smart enough to turn a > simple equality into a mod equality. Which means every select query would > have to include "AND ParentID % something = (:1 % somthing)" in addition to > the primary clause "ParentID=:1", in order to benefit from constraint > exclusion. That would be very unnatural, annoying, and error prone. Range > partitioning would be better, if any partitioning is needed at all. I was remiss in pointing out the additional WHERE requirement -- I tend to assume that people are already aware of that, but it's likely that not everyone is. Thank you for pointing it out. As far as your comments against adding them: I'm not going to speculate as to what kind of queries people do or do not find annoying. The term "unnatural" is an odd choice of words, and the only thing I can think to respond with is "platypus." As far as error-prone is concerned, it's going to have to be the OPs decision on whether the additional work is worth the improvement. In the end, software isn't error-prone, programmers are error- prone. If you have a good QA process in place, then you don't worry about error-prone programmers, as the QA process catches their mistakes. If you don't have such a process in place, or you don't trust it; then things get harder and you make tradeoff decisions like, "I don't want to write complex code, even if it's better, because we don't have the ability to ensure it's error free." And I can't make those kinds of judgments because I don't know what your environment is like. There's no silver bullet. The OP doesn't seem to have any information about what he's planning for: How big is the data predicted to get? What is an acceptable level of performance? Has he even tested to see if the existing layout will scale acceptably to the expected data volume? (perhaps nothing needs to be changed at all) We don't know, so we can only speculate. -- Bill Moran <wmoran@xxxxxxxxxxxxxxxxx>
