oracle - 我无法在 OEL 7 上加速 PostgreSQL 10.4

Question

CREATE TABLE tempxx
    (sid                            NUMBER(10,0))
  PCTFREE     10
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  users
  STORAGE   (
    INITIAL     65536
    NEXT        1048576
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
  NOCACHE
  MONITORING
  NOPARALLEL
  LOGGING
/



CREATE TABLE temptab
    (r1                             NUMBER(10,0),
    r2                             NUMBER(10,0),
    r3                             NUMBER(10,0),
    v1                             VARCHAR2(40 BYTE),
    v2                             VARCHAR2(40 BYTE))
  PCTFREE     10
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  users
  STORAGE   (
    INITIAL     65536
    NEXT        1048576
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
  NOCACHE
  MONITORING
  NOPARALLEL
  LOGGING
/


CREATE UNIQUE INDEX stemptab ON temptab
  (
    r1                              ASC,
    r2                              ASC
  )
  PCTFREE     10
  INITRANS    2
  MAXTRANS    255
  TABLESPACE  users
  STORAGE   (
    INITIAL     65536
    NEXT        1048576
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
NOPARALLEL
LOGGING
/

insert into TEMPXX values (1)
/
commit
/

create 
PROCEDURE stresstest1 is
--use this for session 1

S1 TIMESTAMP;
S2 TIMESTAMP;
S3 TIMESTAMP;
xm1 number;
BEGIN

--EXECUTE IMMEDIATE 'ALTER SESSION SET statistics_level=all';
--EXECUTE IMMEDIATE 'ALTER SESSION SET tracefile_identifier=DANIS'||to_char(sysdate,'ddmmhh24miss');
--EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 8''';

UPDATE TEMPXX SET SID=SID+1;
COMMIT;
S1:=SYSTIMESTAMP;
FOR REC IN 1..100000 LOOP

UPDATE TEMPXX SET SID=SID+1 returning SID into xm1;
INSERT INTO TEMPTAB VALUES (xm1,REC,rec,'TEST '||REC,'TEST2 '||REC);
COMMIT;
END LOOP;
S2:=SYSTIMESTAMP;
COMMIT;
S3:=SYSTIMESTAMP;
--EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT OFF''';

DBMS_OUTPUT.PUT_LINE('STARTED AT '||S1||' TO '||S2||' TO '||S3||' '||(S2-S1)||' '||(S3-S1));

END;    
/

我已经为 postgreSQL 翻译了这段代码，如下所示：

CREATE TABLE tempxx (sid numeric(10,0)) WITH (fillfactor=90);

CREATE TABLE temptab( r1 numeric(10,0),r2 numeric(10,0),r3 numeric(10,0),v1 varchar(40),v2 varchar(40)) WITH (fillfactor=90);
CREATE UNIQUE INDEX stemptab ON temptab (r1 ASC,r2 ASC) WITH (fillfactor=90);
INSERT INTO tempxx VALUES (1);
CREATE OR REPLACE FUNCTION stresstest1 () RETURNS VOID LANGUAGE plpgsql AS $$ DECLARE 
 S1 TIMESTAMP;
S2 TIMESTAMP;
S3 TIMESTAMP;
xm1 int;

BEGIN
UPDATE tempxx SET sid=sid+1;
S1:=CURRENT_TIMESTAMP;
FOR REC IN 1..100000 LOOP
UPDATE TEMPXX SET SID=SID+1 returning SID into xm1;
INSERT INTO TEMPTAB VALUES (xm1,REC,rec,'TEST '||REC,'TEST2 '||REC);

END LOOP;

S2:=CURRENT_TIMESTAMP;
S3:=CURRENT_TIMESTAMP;

RAISE NOTICE '%','STARTED AT '||S1||' TO '||S2||' TO '||S3||' '||(S2-S1)||' '||(S3-S1);

END
$$;
SELECT stresstest1();

在 ORACLE 12c 中，执行此过程需要7 秒。但在 postgresql 10.4 中，需要112 seconds。所以我尝试使用这些设置来提高 postgresql 的性能：

max_connections = 100
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 5242kB
min_wal_size = 1GB
max_wal_size = 2GB

ALTER SYSTEM SET
 max_connections = '100';
ALTER SYSTEM SET
 shared_buffers = '1GB';
ALTER SYSTEM SET
 effective_cache_size = '3GB';
ALTER SYSTEM SET
 maintenance_work_mem = '256MB';
ALTER SYSTEM SET
 checkpoint_completion_target = '0.9';
ALTER SYSTEM SET
 wal_buffers = '16MB';
ALTER SYSTEM SET
 default_statistics_target = '100';
ALTER SYSTEM SET
 random_page_cost = '4';
ALTER SYSTEM SET
 effective_io_concurrency = '2';
ALTER SYSTEM SET
 work_mem = '5242kB';
ALTER SYSTEM SET
 min_wal_size = '1GB';
ALTER SYSTEM SET
 max_wal_size = '2GB';

我可以看到我的设置已应用（我在使用这样的 sql 命令重新启动 postgresql 后进行了检查：）SHOW work_mem;但性能没有变化。所以我该怎么做 ？（对不起，我的英语不好）

更新：

  libra=# EXPLAIN ANALYZE select * from temptab;
                                                   QUERY PLAN

--------------------------------------------------------------------------------
--------------------------------
 Seq Scan on temptab  (cost=0.00..6091.00 rows=300000 width=39) (actual time=0.0
04..27.853 rows=300000 loops=1)
 Planning time: 0.111 ms
 Execution time: 44.241 ms
(3 satır)

Süre: 44,754 ms
libra=# EXPLAIN ANALYZE select * from tempxx;
                                            QUERY PLAN

--------------------------------------------------------------------------------
------------------
 Seq Scan on tempxx  (cost=0.00..443.01 rows=1 width=7) (actual time=0.363..0.36
3 rows=1 loops=1)
 Planning time: 0.095 ms
 Execution time: 0.373 ms
(3 satır)

Süre: 0,713 ms
libra=# EXPLAIN ANALYZE select stresstest1();
NOT:  STARTED AT 2018-08-07 12:01:58.157016 TO 2018-08-07 12:01:58.157016 TO 201                8-08-07 12:01:58.157016 00:00:00 00:00:00
                                         QUERY PLAN

--------------------------------------------------------------------------------
------------
 Result  (cost=0.00..0.26 rows=1 width=4) (actual time=88403.175..88403.176 rows
=1 loops=1)
 Planning time: 0.014 ms
 Execution time: 88403.188 ms
(3 satır)

这些是解释分析结果认为它可以提供帮助。

更新 2： 我发现 oracle 12c 和 postgres 使用顺序扫描。所以我在 postgres 中检查了 pg_stats。我认为这段代码中的索引不起作用。

标签： oraclepostgresqlperformanceoracle12cpostgresql-10