#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    3
    Rep Power
    0

    Pgsql: Adding element to array takes too long


    Hi There!

    I have a table in which one column is an array. Every array has the same length (it stores laboratory data) and now I want to add a new element to all the arrays. I wrote a pgSQL function to take care of it and tested it, successfully, on a small chunk of data. My array has over 1000 elements already and the whole table takes about 11 GB of diskspace. The function has already run for more than 48 hours and is not finished yet. I need something faster. Can you help please?

    Here is my code:
    CREATE OR REPLACE FUNCTION omen_add_sensors("no" integer)
    RETURNS void AS
    $BODY$
    DECLARE
    x INTEGER := 1;
    filler REAL[];
    my_row archives%rowtype;
    BEGIN
    WHILE x <= no LOOP
    filler := array_append(filler, NULL);
    x := x + 1;
    END LOOP;
    FOR my_row IN
    SELECT * FROM archives ORDER BY times
    LOOP
    UPDATE archives SET data = array_cat(my_row.data, filler) WHERE times = my_row.times;
    END LOOP;
    END
    $BODY$
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    That's why using arrays is almost always a bad idea. Relational databases are optimized for table operations, not for array operations. They also tend to become a kind of "table in a table", which you can see very well in your example: You're basically emulating SQL queries like INSERT INTO for your arrays. But yours are of course much slower than an actual INSERT INTO.

    So my suggestion is, simply dump the arrays and store the data like you normally do in the relational model: as rows in a table. This will also allow you to store additional information like e. g. a timestamp.
    Last edited by Jacques1; August 23rd, 2012 at 04:38 PM.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    349
    No need to use a loop. Just do a single update. That will be a lot faster than updating each row indivudually:
    Code:
    CREATE OR REPLACE FUNCTION omen_add_sensors("no" integer)
       RETURNS void AS
    $BODY$
    DECLARE
       x INTEGER := 1;
       filler REAL[];
       my_row archives%rowtype;
    BEGIN
      WHILE x <= no LOOP
        filler := array_append(filler, NULL);
        x := x + 1;
      END LOOP;
      UPDATE archives SET data = array_cat(data, filler);
    END
    $BODY$
    Relational databases are really good at dealing with sets, they are not that good dealing with indivdual rows one at a time.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    3
    Rep Power
    0
    Thank you both very much!

    shammat: This solved the problem in less than 40 minutes. Great! Thank you!

    Jacques1: There are many good reasons for using arrays. For lab data, it makes retrieval and doing serious math on the data much easier to store the values together. I do store a timestamp in the same table as the array => so the whole array has a single timestamp, which saves me having store over a 1000 redundant timestamps (they would occupy as much diskspace as all of my real data). And so on...

    Thank you again.
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    This still strikes me as massively wrong design, which comes from applying procedural techniques to the relational model. Your initial post kind of proves that.

    But, whatever ...
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo