Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2014
    Posts
    2

    Exclamation Unanswered: SELECT FOR UPDATE : severe performance issues between postgres 8.4 and 9.3

    We suffer an obvious difference between 8.4 and 9.3 with the following code.

    We have a table named chrono, containing less than 50 rows.

    Our code does the following things :
    -----------------------------------------------------
    begin
    for 1 to 1000
    select * from chrono where [where_condition] FOR UPDATE
    do stuff (or not…..)
    update chrono set [set_clause] where [where_condition]
    end for
    commit
    --------------------------------------------------------
    In 8.4, no problem: the time by loop still the same during the whole process
    In 9.3, there is an exponential loss of performance: in some conditions, the overall loss exceed 1000 %.

    Tests show that an "easy" way to solve this problem would consist in doing a commit after each update.
    But, but, but: doing this has other HUGE consequences on our application.

    Any ideas or explanations on this behaviors differences ?

    Thanks
    Last edited by krile; 11-29-14 at 08:25.

  2. #2
    Join Date
    Nov 2014
    Posts
    2
    Some more details :

    The loss of performance occurs with more than 1000 loops.

    Here is another example, performed with pgadmin using "select * from jpi_test_perf_chronos()"
    The tables etablissement and param_chrono both have less than 50 rows.

    ---------------------------------------------------------

    CREATE OR REPLACE FUNCTION jpi_test_perf_chronos()
    RETURNS VOID AS
    $BODY$
    DECLARE
    x INTEGER := 0;
    y INTEGER;


    BEGIN

    LOOP
    select 1 from param_chrono into y where chron_table = 'facture' for UPDATE;

    select 1 into y from etablissement limit 1;


    update param_chrono set chron_chrono = chron_chrono where chron_table = 'facture';

    x := x +1 ;

    IF x >= 9999 THEN
    EXIT;
    END iF ;

    END LOOP;

    RETURN;

    END
    $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;
    ------------------------------------------------------------------------------

    In 8.4 => 4300 ms
    In 9.3 => 19 500 ms (...)

Tags for this Thread

Posting Permissions

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