Performance issues encountered with PostgreSQL

Problem SQL

Scwksmlcls.wk_cls_c
, scwklrgcls.wk_lrg_cls_nm
, scwkmdlcls.wk_mdl_cls_nm
, scwksmlcls.wk_sml_cls_nm
, SCWKSMLCLS.WK_CLS_RMK
From
Screqrsnsws
INNER JOIN Scwkclsreqrsnsws
On scwkclsreqrsnsws.req_rsn_id = screqrsnsws.req_rsn_id
INNER JOIN Scwksmlcls
On scwksmlcls.wk_sml_cls_id = scwkclsreqrsnsws.wk_sml_cls_id
And Scwksmlcls.wk_sml_cls_id_ek is not NULL
INNER JOIN Scwkmdlcls
On scwkmdlcls.wk_mdl_cls_id = scwksmlcls.wk_mdl_cls_id
and scwkmdlcls.wk_lrg_cls_id = scwksmlcls.wk_lrg_cls_id
And Scwkmdlcls.wk_mdl_cls_id_ek is not NULL
INNER JOIN Scwklrgcls
On scwklrgcls.wk_lrg_cls_id = scwkmdlcls.wk_lrg_cls_id
And Scwklrgcls.wk_lrg_cls_id_ek is not NULL
WHERE
Screqrsnsws.req_rsn_c = ' 996N '
and Scwksmlcls.wk_cls_c = ' 112233 '
And Screqrsnsws.req_rsn_id_ek is not NULL
ORDER by
Scwklrgcls.disp_odr
, SCWKMDLCLS.DISP_ODR
, SCWKSMLCLS.DISP_ODR

--The table used

Select COUNT (*) from SCREQRSNSWS; --Dependency reason number: 58

Select COUNT (*) from scwkclsreqrsnsws;--depending on reliability reason: 289142

Select COUNT (*) from SCWKSMLCLS; --Small number of categories: 289414

Select COUNT (*) from SCWKMDLCLS; --The number of categories: 285223

Select COUNT (*) from SCWKLRGCLS; --Number of items in Oita: 1962

Table Definition
CREATE TABLE Score.screqrsnsws (
req_rsn_id integer NOT NULL
, Req_rsn_id_ek Integer
, req_rsn_c character varying (4) NOT NULL
, req_rsn_nm character varying (a) NOT NULL
, fix_assts_tagt_f character varying (1) NOT NULL
, Pms_i_ymd timestamp without time zone default now () is not null
, pms_i_usr character varying (+) default "Current_User" () NOT NULL
, pms_i_class character varying (128)
, Pms_u_ymd timestamp without time zone
, pms_u_usr character varying (32)
, pms_u_class character varying (128)
, primary key (REQ_RSN_ID)
);

req_rsn_id, Req_rsn_c, Req_rsn_id_ekのindex

CREATE TABLE Score.scwkclsreqrsnsws (
req_rsn_id integer NOT NULL
, wk_sml_cls_id integer NOT NULL
, drct_ind_h_k character varying (1) NOT NULL
, Pms_i_ymd timestamp without time zone default now () is not null
, pms_i_usr character varying (+) default "Current_User" () NOT NULL
, pms_i_class character varying (128)
, Pms_u_ymd timestamp without time zone
, pms_u_usr character varying (32)
, pms_u_class character varying (128)
, primary key (REQ_RSN_ID,WK_SML_CLS_ID)
);

Req_rsn_id,wk_sml_cls_idの Composite Index
req_rsn_id

CREATE TABLE Score.scwksmlcls (
wk_sml_cls_id integer NOT null
, Wk_sml_cls_id_ek integer
, Work_lrg_cls_c Character varying (2) NOT null
, wk_mdl_cls_c character varying (2) isn't null
, wk_sml_cls_c character varying (2) not Null
, wk_cls_c character varying (6) NOT null
, wk_sml_cls_nm character varying (+) NOT null
, DISP_ODR Integ Er
, wk_cls_rmk character varying ($)
, pg_dev_hndl_f character varying (1) NOT null
, Fix_assts_tagt_f Charac ter varying (1) NOT NULL
, pms_i_ymd timestamp without time zone default now () NOT null
, pms_i_usr character varyi Ng (+) Default "Current_User" () not null
, pms_i_class character varying (+)
, pms_u_ymd timestamp without time Z One
, pms_u_usr character varying (+)
, pms_u_class character varying (+)
, wk_dept_c character varying (4)
, wk_lrg_cls_id integer
, wk_mdl_cls_id integer
, primary key (wk_sml_cls_id)
);

WK_SML_CLS_ID,
Work_lrg_cls_c,wk_mdl_cls_c,wk_sml_cls_c Composite Index
Wk_cls_c
Wk_sml_cls_id_ek

CREATE TABLE Score.scwkmdlcls (
wk_mdl_cls_id integer NOT NULL
, Wk_mdl_cls_id_ek Integer
, work_lrg_cls_c character varying (2) NOT NULL
, wk_mdl_cls_c character varying (2) NOT NULL
, wk_mdl_cls_nm character varying (not null)
, Disp_odr Integer
, wk_lrg_cls_id Integer
, wk_dept_c character varying (4)
, Pms_i_ymd timestamp without time zone default now () is not null
, pms_i_usr character varying (+) default "Current_User" () NOT NULL
, pms_i_class character varying (128)
, Pms_u_ymd timestamp without time zone
, pms_u_usr character varying (32)
, pms_u_class character varying (128)
, primary key (WK_MDL_CLS_ID)
);

wk_mdl_cls_id
Work_lrg_cls_c,wk_mdl_cls_c
Wk_mdl_cls_id_ek

CREATE TABLE Score.scwklrgcls (
wk_lrg_cls_id integer NOT NULL
, Wk_lrg_cls_id_ek Integer
, work_lrg_cls_c character varying (2) NOT NULL
, wk_lrg_cls_nm character Varyin G

Execution plan with SQL statement before "EXPLAIN ANALYZE" execution sql

QUERY PLAN
Sort (cost=3589.88..3589.89 Rows=1 width=96) (actual time=21816.121..21816.121 Rows=1 Loops=1)
Sort Key:scwklrgcls.disp_odr, Scwkmdlcls.disp_odr, SCWKSMLCLS.DISP_ODR
Sort Method:quicksort memory:25kb
-Nested Loop (cost=557.47..3589.87 rows=1 width=96) (actual time=11548.596..21816.102 Rows=1 Loops=1)
-Nested Loop (cost=557.47..3585.59 rows=1 width=80) (actual time=11548.550..21816.055 Rows=1 Loops=1)
Join Filter: (scwkclsreqrsnsws.req_rsn_id = screqrsnsws.req_rsn_id)
-Nested Loop (cost=557.47..3585.31 rows=1 width=84) (actual time=1.472..21798.988 rows=4401 Loops=1)
-Hash Join (cost=557.47..1413.63 rows=1 width=84) (actual time=1.453..15.100 rows=4401 Loops=1)
Hash Cond: ((scwksmlcls.wk_mdl_cls_id = scwkmdlcls.wk_mdl_cls_id) and (scwksmlcls.wk_lrg_cls_id = Scwkmdlcls.wk_lrg_ CLS_ID))
-Index Scan using I_scwksmlcls_ek on Scwksmlcls (cost=0.00..823.09 rows=4408 width=55) (actual time=0.013..4.404 rows =4401 Loops=1)
Index Cond: (Wk_sml_cls_id_ek is not NULL)
-Hash (cost=540.67..540.67 rows=1120 width=37) (actual time=1.420..1.420 rows=1124 Loops=1)
buckets:1024 batches:1 Memory usage:73kb
-Index Scan using I_scwkmdlcls_ek on Scwkmdlcls (cost=0.00..540.67 rows=1120 width=37) (actual time=0.047..1.051 rows =1124 Loops=1)
Index Cond: (Wk_mdl_cls_id_ek is not NULL)
-Index Scan using Con_scwkclsreqrsnsws_pri on Scwkclsreqrsnsws (cost=0.00..2171.67 Rows=1 width=8) (actual time=1.14 0..4.948 Rows=1 loops=4401)
Index Cond: (wk_sml_cls_id = scwksmlcls.wk_sml_cls_id)
-Index Scan using I_SCREQRSNSWS_ALT2 on Screqrsnsws (cost=0.00..0.27 Rows=1 width=4) (actual time=0.003..0.003 Rows=1 loops=4401)
Index Cond: ((Req_rsn_c):: Text = ' 996N ':: Text)
Filter: (Req_rsn_id_ek is not NULL)
-Index Scan using Con_scwklrgcls_pri on Scwklrgcls (cost=0.00..4.27 Rows=1 width=28) (actual time=0.011..0.012 Rows=1 Loops=1)
Index Cond: (wk_lrg_cls_id = scwkmdlcls.wk_lrg_cls_id)
Filter: (Wk_lrg_cls_id_ek is not NULL)
Total runtime:21816.279 ms

Look at the table definition, index, execution plan, consider the conditions of the small classification table and the Innerjoin in the classification table "and scwkmdlcls.wk_lrg_cls_id = scwksmlcls.wk_lrg_cls_id" superfluous.

Because the primary key of the big middle class table is the ID, it should be sufficient to connect with the primary key ID. The execution time for removing this condition is significantly faster.

EXPLAIN ANALYZE SELECT
Scwksmlcls.wk_cls_c
, scwklrgcls.wk_lrg_cls_nm
, scwkmdlcls.wk_mdl_cls_nm
, SCWKSMLCLS.WK_SML_CLS_NM
, Scwksmlcls.wk_cls_rmk
from
Screqrsnsws
INNER JOIN Scwkclsreqrsnsws
on SC wkclsreqrsnsws.req_rsn_id = screqrsnsws.req_rsn_id
INNER JOIN scwksmlcls
on scwksmlcls.wk_sml_cls_id = SCWKCLSR EQRSNSWS.WK_SML_CLS_ID
and Scwksmlcls.wk_sml_cls_id_ek are not NULL
INNER joins Scwkmdlcls
on Scwkmdlcls.wk_m dl_cls_id = scwksmlcls.wk_mdl_cls_id
--and scwkmdlcls.wk_lrg_cls_id = scwksmlcls.wk_lrg_cls_id
and scwkmdlcls. Wk_mdl_cls_id_ek is isn't NULL
INNER JOIN scwklrgcls
on scwklrgcls.wk_lrg_cls_id = scwkmdlcls.wk_lrg_cls_id
A ND Scwklrgcls.wk_lrg_cls_id_ek is not NULL
WHERE
Screqrsnsws.req_rsn_c = ' 996N '
and Scwksmlcls.wk_cls_c = ' 11 2233 '
and Screqrsnsws.req_rsn_id_ek is not NULL
ORDER by
Scwklrgcls.disp_odr
, Scwkmdlcls.disp_odr
, SCWKSMLCLS.DISP_ODR

QUERY PLAN
Sort (cost=5598.19..5598.20 Rows=1 width=96) (actual time=3.270..3.270 Rows=1 Loops=1)
Sort Key:scwklrgcls.disp_odr, Scwkmdlcls.disp_odr, SCWKSMLCLS.DISP_ODR
Sort Method:quicksort memory:25kb
-Nested Loop (cost=949.97..5598.18 rows=1 width=96) (actual time=3.254..3.260 Rows=1 Loops=1)
-Nested Loop (cost=949.97..5597.80 rows=1 width=76) (actual time=3.245..3.249 Rows=1 Loops=1)
-Hash Join (cost=949.97..5429.77 rows=77 width=47) (actual time=3.237..3.241 Rows=1 Loops=1)
Hash Cond: (scwkclsreqrsnsws.wk_sml_cls_id = scwksmlcls.wk_sml_cls_id)
-Nested Loop (cost=71.78..4512.75 rows=5075 width=4) (actual time=0.038..0.041 Rows=1 Loops=1)
Seq Scan on Screqrsnsws (cost=0.00..2.71 Rows=1 width=4) (actual time=0.023..0.025 Rows=1 Loops=1)
Filter: ((Req_rsn_id_ek is not NULL) and ((Req_rsn_c):: Text = ' 996N ':: Text))
Bitmap Heap Scan on Scwkclsreqrsnsws (cost=71.78..4443.07 rows=5357 width=8) (actual time=0.012..0.012 Rows=1 loops= 1)
Recheck Cond: (req_rsn_id = screqrsnsws.req_rsn_id)
-Bitmap Index Scan on Con_scwkclsreqrsnsws_pri (cost=0.00..70.44 rows=5357 width=0) (actual time=0.008..0.008 Rows=1 Loops=1)
Index Cond: (req_rsn_id = screqrsnsws.req_rsn_id)
-Hash (cost=823.09..823.09 rows=4408 width=51) (actual time=3.186..3.186 rows=4401 Loops=1)
buckets:1024 batches:1 Memory usage:313kb
-Index Scan using I_scwksmlcls_ek on Scwksmlcls (cost=0.00..823.09 rows=4408 width=51) (actual time=0.011..2.002 rows =4401 Loops=1)
Index Cond: (Wk_sml_cls_id_ek is not NULL)
-Index Scan using Con_scwkmdlcls_pri on Scwkmdlcls (cost=0.00..2.17 Rows=1 width=37) (actual time=0.005..0.005 Rows=1 Loops=1)
Index Cond: (wk_mdl_cls_id = scwksmlcls.wk_mdl_cls_id)
Filter: (Wk_mdl_cls_id_ek is not NULL)
-Index Scan using Con_scwklrgcls_pri on Scwklrgcls (cost=0.00..0.37 Rows=1 width=28) (actual time=0.009..0.009 Rows=1 Loops=1)
Index Cond: (wk_lrg_cls_id = scwkmdlcls.wk_lrg_cls_id)
Filter: (Wk_lrg_cls_id_ek is not NULL)
Total runtime:3.364 ms

The implementation plan for PostgreSQL and Oracle is really different.

Also found interesting things, business "Screqrsnsws.req_rsn_c = Scwksmlcls.wk_dept_c" This is established, add this condition after the statement can also become faster, because in the Where condition "screqrsnsws.req_ Rsn_c = ' 996N ' condition, the execution plan will be "Scwksmlcls.wk_dept_c = ' 996N '", the selection data of the inner table is smaller.

EXPLAIN ANALYZE
SELECT
Scwksmlcls.wk_cls_c
, scwklrgcls.wk_lrg_cls_nm
, scwkmdlcls.wk_mdl_cls_nm
, scwksmlcls.wk_sml_cls_nm
, SCWKSMLCLS.WK_CLS_RMK
From
Screqrsnsws
INNER JOIN Scwkclsreqrsnsws
On scwkclsreqrsnsws.req_rsn_id = screqrsnsws.req_rsn_id
And Screqrsnsws.req_rsn_id_ek is not NULL
INNER JOIN Scwksmlcls
On scwksmlcls.wk_sml_cls_id = scwkclsreqrsnsws.wk_sml_cls_id
and Screqrsnsws.req_rsn_c = Scwksmlcls.wk_dept_c
And Scwksmlcls.wk_sml_cls_id_ek is not NULL
INNER JOIN Scwkmdlcls
On scwkmdlcls.wk_mdl_cls_id = scwksmlcls.wk_mdl_cls_id
and scwkmdlcls.wk_lrg_cls_id = scwksmlcls.wk_lrg_cls_id
And Scwkmdlcls.wk_mdl_cls_id_ek is not NULL
INNER JOIN Scwklrgcls
On scwklrgcls.wk_lrg_cls_id = scwkmdlcls.wk_lrg_cls_id
And Scwklrgcls.wk_lrg_cls_id_ek is not NULL
WHERE
Screqrsnsws.req_rsn_c = ' 996N '
And Screqrsnsws.req_rsn_id_ek is not NULL
ORDER by
Scwklrgcls.disp_odr
, SCWKMDLCLS.DISP_ODR
, SCWKSMLCLS.DISP_ODR

QUERY PLAN
Sort (cost=859.96..859.97 Rows=1 width=96) (actual time=2.025..2.025 Rows=1 Loops=1)
Sort Key:scwklrgcls.disp_odr, Scwkmdlcls.disp_odr, SCWKSMLCLS.DISP_ODR
Sort Method:quicksort memory:25kb
-Nested Loop (cost=0.00..859.95 rows=1 width=96) (actual time=1.050..2.013 Rows=1 Loops=1)
Join Filter: (scwksmlcls.wk_lrg_cls_id = scwkmdlcls.wk_lrg_cls_id)
-Nested Loop (cost=0.00..855.66 rows=1 width=79) (actual time=1.041..2.003 Rows=1 Loops=1)
-Nested Loop (cost=0.00..851.35 rows=1 width=87) (actual time=1.012..1.974 Rows=1 Loops=1)
-Index Scan using Con_screqrsnsws_pri on Screqrsnsws (cost=0.00..8.68 Rows=1 width=9) (actual time=0.015..0.030 rows= 1 Loops=1)
Filter: ((Req_rsn_id_ek is not null) and (Req_rsn_id_ek was not null) and ((Req_rsn_c):: Text = ' 996N ':: Text))
-Nested Loop (cost=0.00..842.67 rows=1 width=88) (actual time=0.995..1.942 Rows=1 Loops=1)
-Index Scan using I_scwksmlcls_ek on Scwksmlcls (cost=0.00..834.11 rows=2 width=60) (actual time=0.988..1.934 Rows=1 Loops=1)
Index Cond: (Wk_sml_cls_id_ek is not NULL)
Filter: ((wk_dept_c):: Text = ' 996N ':: Text)
-Index Scan using Con_scwklrgcls_pri on Scwklrgcls (cost=0.00..4.27 Rows=1 width=28) (actual time=0.004..0.005 Rows=1 Loops=1)
Index Cond: (wk_lrg_cls_id = scwksmlcls.wk_lrg_cls_id)
Filter: (Wk_lrg_cls_id_ek is not NULL)
-Index Scan using Con_scwkclsreqrsnsws_pri on Scwkclsreqrsnsws (cost=0.00..4.29 Rows=1 width=8) (actual time=0.028: 0.028 Rows=1 Loops=1)
Index Cond: ((req_rsn_id = screqrsnsws.req_rsn_id) and (wk_sml_cls_id = scwksmlcls.wk_sml_cls_id))
-Index Scan using Con_scwkmdlcls_pri on Scwkmdlcls (cost=0.00..4.28 Rows=1 width=37) (actual time=0.005..0.006 Rows=1 Loops=1)
Index Cond: (wk_mdl_cls_id = scwksmlcls.wk_mdl_cls_id)
Filter: (Wk_mdl_cls_id_ek is not NULL)
Total runtime:2.143 ms

In order to probably understand the implementation plan, but also deliberately searched for "PostgreSQL explain command Details", which is very useful.

Performance issues encountered with PostgreSQL