[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Partition Constraint Exclusion Limits
From:       Jim Nasby <Jim.Nasby () BlueTreble ! com>
Date:       2015-10-28 2:59:12
Message-ID: 56303A00.20908 () BlueTreble ! com
[Download RAW message or body]

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[prev in list] [next in list] [prev in thread] [next in thread]