INHERITS and planning

From: Edmund Dengler <edmundd(at)eSentire(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: INHERITS and planning
Date: 2005-06-10 01:30:13
Message-ID: Pine.BSO.4.58.0506092120370.19098@cyclops4.internal
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Glaesemann 2005-06-10 02:37:38 Re: Now() function
Previous Message Tom Lane 2005-06-10 01:10:33 Re: Pushing limit into subqueries of a union

Browse pgsql-hackers by date

  From Date Subject
Next Message Christopher Kings-Lynne 2005-06-10 01:41:05 Re: Bug in pg_restore ... ?
Previous Message Tom Lane 2005-06-09 23:23:54 Re: Fix for cross compilation