Re: select count() out of memory

"Sam Mason" <sam(at)samason(dot)me(dot)uk> 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