Thread: What's faster

Option 1:
create table a (id serial, hosts text[]);

OR

Option 2:
create table a (id serial);
create table hosts (id int references a, host text);


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.

On Fri, Dec 10, 2004 at 06:15:50PM -0800, Eric Brown wrote:
> Option 1:
> create table a (id serial, hosts text[]);
>
> OR
>
> Option 2:
> create table a (id serial);
> create table hosts (id int references a, host text);

Option 2 will save a lot of developer & query time, as it's much more
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)
FROM a;

> 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.

It's a lot easier to search under option 2, and besides, speed isn't
everything ;)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!