Greetings!
Is there an issue when a large number of INHERITS tables exist for
planning?
We have 2 base tables, and use INHERITS to partition the data. When we get
around 2000-2200 sub-tables (approx 1000-1100 per base table), planning a
SELECT statement on the base table (ie, to search all sub-tables) will
start slowing down dramatically (ie, feels like something exponential OR
some kind of in-memory to on-disk transition).
I haven't done enough to really plot out the planning times, but
definitely around 1600 tables we were getting sub-second plans, and around
2200 we were above 30 seconds.
Also, is there any plans to support proper partitioning/binning of data
rather than through INHERITS? I know it has been mentioned as upcoming
sometime similar to Oracle. I would like to put in a vote to support
"auto-binning" in which a function is called to define the bin. The Oracle
model really only supports: (1) explicit partitioning (ie, every new
partition must be defined), or (2) hash binning. What we deal with is
temporal data, and would like to bin on the hour or day "automatically",
hopefully to support truncating whole bins.
This helps us 2 ways: (1) data deletion is bulk (we currently drop a full
inherited table), (2) cancelling a VACUUM/SELECT doesn't take forever
while the execution engine finishes "this table" (we have had cancels
take 2 hours because the VACUUM was on a very large single table).
Regards!
Ed