Re: Partition Constraint Exclusion Limits

On 10/27/15 3:33 PM, Vitalii Tymchyshyn wrote:
> BTW: May be it could be feasible in future to perform partition
> exclusion during the execution? This would be very neat feature.

True exclusion? probably not. The problem is you can't completely
exclude something based on any value that could change during execution.

There has been some work done on declarative partition specification,
where a given value would be fit to the exact partition it belong in.
IIRC that's currently stalled though.

One thing you could try would be to create an index on each partition
that would always be empty. IE, if you have a June 2015 partition, you
could:

CREATE INDEX ... ON( date_field ) WHERE date_field < '2015-6-1'::date OR
date_field >= '2015-7-1'::date;

Because the WHERE clause will never be true, that index will always be
empty, which will make probing it very fast. I suspect that might be
faster than probing a regular index on the date field, but you should
test it.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com