On 09/18/2013 12:20 PM, Shaun Thomas wrote:
On 09/18/2013 11:05 AM, "Gabriel E. Sánchez Martínez" wrote:
Or would it have to check every child partition, as follows?
#
Check partition 2000-01-01, 2000-01-02, ... , 2013-09-18. 5010 checks.
All days but 2012-01-01 are excluded. 5009 partitions excluded with
5010 constraint exclusion checks.
My guess based on the fact the planner has no concept of ranges aside
from their width, is that this is the approach it'll likely take. You
can't really teach it that your particular inheritance tree is range
constrained per level, so it has to check every range.
In that case maybe this could be considered as a performance improvement
for future versions of PG? If I am not mistaken check constraints are
inherited from parent table to child table, making it feasible for the
query planner to prune entire branches at once by checking the
inheritance structure. I know that query performance should be very
good with partitions for each month and indexes on the timestamp column,
but I can think of many applications where data is extracted in daily
batches and it is more natural and convenient for DB maintenance to have
a partition per day. In big data applications, it may be feasible to
fit a day's worth of data in RAM, but not a whole month; in such cases
there could be performance gains from tree constraint exclusion.
Thanks for your insight.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
