Professional Web Applications Themes

What's faster - PostgreSQL / PGSQL

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

  1. #1

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

    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to [email]majordomopostgresql.org[/email] so that your
    message can get through to the mailing list cleanly

    Eric Brown Guest

  2. #2

    Default 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 [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])

    David Fetter Guest

Similar Threads

  1. Faster Download
    By ICSHyderabad in forum Macromedia Flash Ad Development
    Replies: 6
    Last Post: June 15th, 05:09 AM
  2. Make php faster?
    By Frank de Bot in forum PHP Development
    Replies: 2
    Last Post: October 9th, 08:18 AM
  3. DuplicateMovieClip goes faster and faster
    By sylvie in forum Macromedia Flash
    Replies: 0
    Last Post: September 30th, 06:11 PM
  4. why not faster
    By rjphoto in forum Adobe Photoshop Elements
    Replies: 17
    Last Post: September 15th, 05:00 AM
  5. Which is Faster?
    By Dan Manes in forum Macromedia Director Lingo
    Replies: 0
    Last Post: July 29th, 10:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139