Re: Time to move table to new tablespace

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