Re: INHERITS and planning

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Edmund Dengler <edmundd(at)eSentire(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: INHERITS and planning
Date: 2005-06-15 21:18:25
Message-ID: 1118870305.3645.94.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Simon Riggs 2005-06-15 21:29:26 Re: [HACKERS] INHERITS and planning
Previous Message Matthew Phillips 2005-06-15 20:47:23 Re: plpgsql - TIMESTAMP variables in EXTRACT

Browse pgsql-hackers by date

  From Date Subject
Next Message Simon Riggs 2005-06-15 21:29:26 Re: [HACKERS] INHERITS and planning
Previous Message Bruce Momjian 2005-06-15 20:21:30 Re: Autovacuum in the backend