On 10/01/2012 3:10 AM, Jason Buberel wrote:
"Select median price for every zip code as of 2012-01-06" (customer exports)"Select median price for 94086 from 2005-01-01 through 2012-01-06" (charting apps)
So by partitioning in one dimension we impact queries in the other.
Well, I know of *theoretical* solutions that use n-dimensional partitioning of data, but Pg doesn't have automatic and easy-to-administrate 1-dimensional partitioning, so I think going for 2D is a bit of stretch ;-) .
I wonder how hard it'd be to extend constraint exclusion to let it use different constraints for different queries, or even combine constraints? The hard bit would be making it efficient. Even if the answer is "not very", the hard bit would be maintaining a 2D (or more) partitioned table structure. That, and making constraint exclusion *efficient* over that many tables.
If the data being partitioned by is not only ordinal but interval too, then it'd be possible to define regular intervals for partitioning and build a bitmap for matching tables. That'd be a fun honours / masters project, but hardly something suitable for trying to hack into a production setup.
--
Craig Ringer