Search Postgresql Archives

Re: INHERITS and planning

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
      message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux