What's faster

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!