Re: INHERITS and planning

On Thu, 2005-06-09 at 21:30 -0400, Edmund Dengler wrote:
> 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.

Interesting... I tested up to 1000 and found the performance acceptable,
as you suggest. I'd question why you have so many partitions.

There is a known issue here to do with a lack of an index on the
pg_inherits catalog relation....

Here is the comment from backend/optimizer/util/plancat.c's
find_inheritance_children line 565:568

* XXX might be a good idea to create an index on pg_inherits' inhparent
* field, so that we can use an indexscan instead of sequential scan here
* However, in typical databases pg_inherits won't have enough entries to
* justify an indexscan...

In other places in the code there are comments that show that having
more than a 1000 catalog entries usually requires an index.

> 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.

Working on it now.

> 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.

Unlikely in the 8.1 version....

Best Regards, Simon Riggs