"Sam Mason" <sam@xxxxxxxxxxxxx> writes: > On Fri, Oct 26, 2007 at 08:25:09AM +0200, Thomas Finneid wrote: >> Gregory Stark wrote: >> >Tom's point is that if you have 55k tables then just *finding* the newest >> >child table is fairly expensive. You're accessing a not insignificant-sized >> >index and table of tables. And the situation is worse when you consider the >> >number of columns all those tables have, all the indexes those tables have, >> >all the column keys those indexes the tables have have, etc. >> >> Yes, I got that. But I name the child tables so that I when my server >> receives read requests, I retreive details from the request to be able >> to figure out the exact child table name, without the system needing to >> do any internal searches to find the newest table. > > I think the lookup that is being referred to is the fact that if you've > got 55k (plus) files in a directory then the filesystem still has to > perform a search in the directory to locate the actual file associated > with the filename. There probably isn't going to be much difference > between the filesystem performing this lookup vs the database descending > a few levels of its index structure. That's true but it's in *addition* to the database having to find the catalog records for the table which involves an index lookup itself. Actually many index lookups since it has to look up the catalog record for the table, for all the columns of the table, for all indexes of the table, for all the index keys of those indexes, all constraints of the table, all triggers of the table, all dependencies on other objects and of other objects on this table (this latter is why I suggest not using inheritance). Each of these lookups is using an index to find the table out of 55k records which is just the same work that you're saving in the top level of the index tree. If you think there's one obvious solution then you just haven't analyzed the problem seriously. In serious engineering there are always tradeoffs. The cleanest prettiest solution is not necessarily -- and in cases where you're dealing with large numbers like this almost certainly isn't -- the optimal choice. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
