On Fri, Dec 10, 2004 at 06:15:50PM -0800, Eric Brown wrote:Option 2 will save a lot of developer & query time, as it's much more> Option 1:
> create table a (id serial, hosts text);
> Option 2:
> create table a (id serial);
> create table hosts (id int references a, host text);
standard. If you need a VIEW like table a, it's easy to do like this:
CREATE VIEW view_a AS
SELECT a.id, ARRAY(SELECT host FROM hosts WHERE id = a.id)
It's a lot easier to search under option 2, and besides, speed isn't> Table 'a' will have about 500,000 records. There will probably be
> about 20 reads for every write. Each id has approximately 1.1 hosts.
> If I use the array (option 1), I'll have to loop over the elements
> of the array to see if I have a match when querying a given id. This
> isn't hard, but it means that SELECT will always return 1 record
> when, in option 2, it might return 0 records and only have accessed
> the indexes.
> Given the indexes that will be built and disk pages used (cached or
> otherwise), which mechanism would be faster for searching.
David Fetter [email]davidfetter.org[/email] [url]http://fetter.org/[/url]
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])