Speed of locating tables?

From: Steve Wampler <swampler(at)noao(dot)edu>
To: postgres-general <pgsql-general(at)postgresql(dot)org>
Subject: Speed of locating tables?
Date: 2000-05-26 14:18:30
Message-ID: 392E87B6.32710D1F@noao.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Given the name of a table, how quickly can postgresql
locate and access the table? How does the performance
compare (ballpark estimate) with just using flat files
to represent the data in each table?

I have a problem where the (to me) most natural solution
is to create a large number of small tables. A new solar
telescope/instrument set we're building needs to share
configuration information (sets of attribute (name-value
pairs)) across a distributed environment, plus retain these
sets for possible reuse. Typically, there will be 10-30
thousand of these sets created each day. Each set has
associated with it a unique id string. When an
attribute set is needed, it is needed quickly - every 1/5
of a second or so a request will be made of the system
that will require access to one of the sets - this request
will be via the id string, never by any more complex scheme.

To me, the most natural way to encode the sets is to
create a separate table for each set, since the attributes
can then be indexed and referenced quickly once the table
is accessed. But I don't know how fast PG is at locating
a table, given its name.

So, to refine the question - given a DB with (say) 100,000
tables, how quickly can PG access a table given its name?

Thanks! I'm also open to suggestions on other ways to
represent the data that would provide better access
performance - you can probably tell I'm new to the world of
databases.

--
Steve Wampler- SOLIS Project, National Solar Observatory
swampler(at)noao(dot)edu

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2000-05-26 14:51:17 Re: PG 7.0 is 2.5 times slower running a big report
Previous Message Richard J. Kuhns 2000-05-26 14:05:18 createdb -- alternate locations