Re: 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(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!