时间戳

首页 > 解决方案 > 数百万行的 Enterprise Postgresql 查询性能

sql - 数百万行的 Enterprise Postgresql 查询性能

问题描述

请查找以下过程。游标（cntry_lobreg_lang_cur）包含 23749782 行，视图（技能）包含 629 行。加入和检索行需要 3 个多小时。它使用 100% CPU（8 核）和内存（64GB）并崩溃。

请您建议如何提高性能。

 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;

如果您有任何疑问，请告诉我。

标签： sqlplpgsqlquery-performancepostgresql-9.5sqlperformance

解决方案

首先将逻辑重写为单个语句：

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;

然后，如果您没有，请在skills(LINE_OF_BUSINESS, PRODUCTID).

笔记：

  • 避免游标。
  • 始终使用明确、正确、标准 JOIN的语法。 切勿在FROM子句中使用逗号。
  • 使用有意义的表别名。 没有任何意义a；b表名的缩写。

推荐阅读