Re: Speed of locating tables?

Thanks for the quick response!

Matthias Urlichs wrote:

> Steve Wampler:
> > So, to refine the question - given a DB with (say) 100,000
> > tables, how quickly can PG access a table given its name?
> >
> You will then have a directory with one hundred thousand entries (plus
> one for each index you decide to create). This will slow down your
> system like hell.

Ah - I hadn't appreciated that postgres stores each table as a file!
I assume that means also that every database is managed as a single
directory (i.e. no way to spread tables in a database across multiple
directories).

> > Thanks!  I'm also open to suggestions on other ways to
> > represent the data that would provide better access
> > performance -
> >
> Easy. Every table gets another column "telescope_ID", and you create
> a new table which maps from the csope's ID to their name and vice versa.

Hmmm, but there's now only one table (with around 2 million rows) instead of
100,000
(each with around 20 rows) right?  (I'm confused by the "Every table gets...").
I read what you've written as saying "add the configuration set ID to each
attribute
(so now it's an ID, name, value triple instead of a pair), storing
all attributes for all sets in a single table and then, when given an ID,
search the table, collect the matching rows into a temporary table and reference
the attributes from that temporary table" - is that correct?  [I don't need
fast update of the attributes, just fast read access while the sun is shining.]

I guess I don't understand the "name" in the 2nd table you've mentioned - I have
a "config set ID" identifying each set of attributes, not an ID and a name.

I imagine I can pull "old" configuration sets from the large table (every night,
perhaps) and archive them into some other table(s) to keep the size of the
active table smaller.

> > you can probably tell I'm new to the world of databases.

>>You definitely want to read an introductory book / online article about
> the subject.

Thanks - any favorites?  I've looked through a couple, but they seem to
concentrate on performance issues when the query is complicated and not
with quickly locating a small set of information from a larger set with
a simple query (in my case, the config set ID...).

Thanks again!

--
Steve Wampler-  SOLIS Project, National Solar Observatory
swampler@noao.edu