CODEWITHSUNDEEP
sqlplpgsqlquery-performancepostgresql-9.5sqlperformance
user1720827
user1720827

Reputation: 147

Enterprise Postgresql Query performance on millions of rows

Please find the below procedure.The cursor(cntry_lobreg_lang_cur) contains 23749782 rows & the view (skills) has 629 rows. It's taking more than 3 hrs to join and retrieve the rows. It's using 100% CPU(8 cores) and Memory(64GB) and getting crashed.

Would you please suggest on how to increase the performance.

 CREATE TYPE lang_tab AS ( countryid character varying(255), lob_id character varying(255), region_id numeric(10,0), language_id character varying(255), roleid character varying(255), language_id_flag character varying(5), roleid_flag character varying(5), course_id character varying(255), skill_id character varying(255), language_id_null character varying(5), roleid_null character varying(5), lob_name character varying(256) );

CREATE TYPE lang_obj AS lang_tab;


DECLARE
    CURSOR cntry_lobreg_lang_cur is
        SELECT *
        FROM
               LOBREG_LANGROLE
        ;

    _array_type lang_obj := lang_obj();
BEGIN

    OPEN cntry_lobreg_lang_cur;
    LOOP
        FETCH cntry_lobreg_lang_cur BULK COLLECT
        INTO  _array_type LIMIT 30000
        ;

        EXIT WHEN _array_type.count = 0;
        dbms_output.put_line('_array_type : '
        ||_array_type.COUNT);

        INSERT        INTO LANGROL_TAB
               (COUNTRYID
                    , LOB_ID
                    , REGION_ID
                    , LANGUAGE_ID
                    , ROLEID
                    , SKILL_ID
                    , LOB_NAME
               )
        SELECT
               A.COUNTRYID
             , A.LOB_ID
             , A.REGION_ID
             , A.LANGUAGE_ID
             , A.ROLEID
             , B.PRODUCTID
             , A.LOB_NAME
        FROM
               TABLE(_array_type) A
             , skills             B
        WHERE
               A.LOB_NAME=B.LINE_OF_BUSINESS
        ;

    END LOOP;

    CLOSE cntry_lobreg_lang_cur;

END;

Should you have any queries, please let me know.

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

Start by rewriting the logic as a single statement:

INSERT INTO LANGROL_TAB (COUNTRYID, LOB_ID, REGION_ID, LANGUAGE_ID, ROLEID, SKILL_ID, LOB_NAME)
    SELECT l.COUNTRYID, l.LOB_ID, l.REGION_ID, l.LANGUAGE_ID, l.ROLEID,
           B.PRODUCTID,
           l.LOB_NAME
    FROM LOBREG_LANGROLE l JOIN
         skills s
         ON l.LOB_NAME = s.LINE_OF_BUSINESS;

Then, if you don't have one, add an index on skills(LINE_OF_BUSINESS, PRODUCTID).

Notes:

  • Avoid cursors.
  • Always use explicit, proper, standard JOIN syntax. Never use commas in the FROM clause.
  • Use meaningful table aliases. a and b don't mean anything; abbreviations for the table names do.

Upvotes: 3

Related Questions