#はじめに
DBのパフォーマンス対策として、テーブルのパーティション分割がありますが
今まで馴染みがなかったので、以下の記事を参考に実際に確認してみました。
パーティショニングにおける性能向上のしくみ
##検証
###検証内容
実行環境はAurora PostgreSQL(エンジンベース：13)で行います。
社員のテーブルが正規化されていて、複数テーブルをjoinして取ってくるケースを考えます。
- 社員個人情報テーブル(社員毎に1レコード) -> 10万レコード
- 給与履歴テーブル(社員、月毎に1レコード) -> 1800万レコード
- 給与詳細の履歴テーブル(社員、月毎、給与項目毎に1レコード) -> 5400万レコード
このテーブルに対して、パーティションを以下の3つのパターンで作成してパフォーマンスを確認します。
- パーティション無し
- 給与2テーブルに月毎に20分割するパーティションを作成
- 3テーブルに社員を均等に20分割するパーティションを作成
実行するSQLはそれぞれのパーティションが機能するように、以下の2つを実行します。
①特定の月の給与計算結果を取得
-> 給与の月毎のパーティションが機能する想定
②勤続年数が○年以上で給与総額が△円で基本給が□円以上の結果を取得
-> 社員パーティションが機能する想定
後者のBI的な検索用には照会用の非正規テーブルを事前に作るべき、
といったそもそも論もあるかもしれませんが、、検証してみます。
###検証用テーブル作成
パーティションなしの3テーブル作成
社員個人情報テーブル
CREATE TABLE PERSON_NO_PAR (emp_id bigint Default 0 NOT NULL, join_date timestamp(0), working_years numeric(5,2) Default 0 not null);
ALTER TABLE PERSON_NO_PAR ADD CONSTRAINT PK_PERSON_NO_PAR PRIMARY KEY (emp_id) USING INDEX;
給与履歴テーブル
CREATE TABLE SALARY_HISTORY_NO_PAR (..割愛, EMP_ID bigint Default 0 NOT NULL, pay_month bigint Default 0 NOT NULL,total bigint Default 0 not Null);
ALTER TABLE SALARY_HISTORY_NO_PAR ADD CONSTRAINT PK_SALARY_HISTORY_NO_PAR PRIMARY KEY (..割愛, pay_month, emp_id) USING INDEX;
給与詳細の履歴テーブル
CREATE TABLE SALARY_DETAIL_HISTORY_NO_PAR (emp_id bigint Default 0 NOT NULL, PAY_MONTH bigint Default 0 not null, item_id bigint Default 0 not null, amount numeric(18,8) Default 0 not null);
ALTER TABLE SALARY_DETAIL_HISTORY_NO_PAR ADD CONSTRAINT PK_SALARY_DETAIL_HISTORY_NO_PAR PRIMARY KEY (emp_id, pay_month,item_id) USING INDEX;
給与2テーブルの月毎のパーティション作成(PARTITION BY RANGEで20分割)
給与履歴テーブル
CREATE TABLE SALARY_HISTORY_PAR_MONTH (..割愛, EMP_ID bigint Default 0 NOT NULL, pay_month bigint Default 0 NOT NULL, ..割愛) PARTITION BY RANGE (pay_month);
ALTER TABLE SALARY_HISTORY_PAR_MONTH ADD CONSTRAINT PK_SALARY_HISTORY_PAR_MONTH PRIMARY KEY (..割愛, pay_month, emp_id) USING INDEX;
CREATE TABLE SALARY_HISTORY_PAR_MONTH_00 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (0) TO (200401);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_01 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200401) TO (200402);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_02 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200402) TO (200403);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_03 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200403) TO (200404);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_04 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200404) TO (200405);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_05 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200405) TO (200406);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_06 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200406) TO (200407);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_07 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200407) TO (200408);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_08 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200408) TO (200409);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_09 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200409) TO (200410);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_10 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200410) TO (200411);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_11 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200411) TO (200412);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_12 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200412) TO (200501);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_13 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200501) TO (200502);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_14 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200502) TO (200503);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_15 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200503) TO (200504);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_16 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200504) TO (200505);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_17 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200505) TO (200506);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_18 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200506) TO (200507);
CREATE TABLE SALARY_HISTORY_PAR_MONTH_19 PARTITION OF SALARY_HISTORY_PAR_MONTH FOR VALUES FROM (200507) TO (200508);
給与詳細の履歴テーブル
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH (emp_id bigint Default 0 NOT NULL, PAY_MONTH bigint Default 0 not null, item_id bigint Default 0 not null, amount numeric(18,8) Default 0 not null) PARTITION BY RANGE (pay_month);
ALTER TABLE SALARY_DETAIL_HISTORY_PAR_MONTH ADD CONSTRAINT PK_SALARY_DETAIL_HISTORY_PAR_MONTH PRIMARY KEY (emp_id, pay_month,item_id) USING INDEX;
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_00 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (0) TO (200401);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_01 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200401) TO (200402);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_02 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200402) TO (200403);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_03 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200403) TO (200404);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_04 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200404) TO (200405);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_05 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200405) TO (200406);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_06 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200406) TO (200407);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_07 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200407) TO (200408);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_08 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200408) TO (200409);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_09 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200409) TO (200410);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_10 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200410) TO (200411);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_11 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200411) TO (200412);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_12 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200412) TO (200501);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_13 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200501) TO (200502);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_14 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200502) TO (200503);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_15 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200503) TO (200504);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_16 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200504) TO (200505);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_17 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200505) TO (200506);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_18 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200506) TO (200507);
CREATE TABLE SALARY_DETAIL_HISTORY_PAR_MONTH_19 PARTITION OF SALARY_DETAIL_HISTORY_PAR_MONTH FOR VALUES FROM (200507) TO (200508);
3テーブルの社員毎のパーティション作成(PARTITION BY HASHで20分割)
社員個人情報テーブル
CREATE TABLE PERSON (emp_id bigint Default 0 NOT NULL, join_date timestamp(0), working_years numeric(5,2) Default 0 not null) PARTITION BY HASH (emp_id);
ALTER TABLE PERSON ADD CONSTRAINT PK_PERSON PRIMARY KEY (emp_id) USING INDEX;
CREATE TABLE PERSON_00 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 0);
CREATE TABLE PERSON_01 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 1);
CREATE TABLE PERSON_02 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 2);
CREATE TABLE PERSON_03 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 3);
CREATE TABLE PERSON_04 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 4);
CREATE TABLE PERSON_05 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 5);
CREATE TABLE PERSON_06 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 6);
CREATE TABLE PERSON_07 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 7);
CREATE TABLE PERSON_08 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 8);
CREATE TABLE PERSON_09 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 9);
CREATE TABLE PERSON_10 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 10);
CREATE TABLE PERSON_11 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 11);
CREATE TABLE PERSON_12 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 12);
CREATE TABLE PERSON_13 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 13);
CREATE TABLE PERSON_14 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 14);
CREATE TABLE PERSON_15 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 15);
CREATE TABLE PERSON_16 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 16);
CREATE TABLE PERSON_17 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 17);
CREATE TABLE PERSON_18 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 18);
CREATE TABLE PERSON_19 PARTITION OF PERSON FOR VALUES WITH (modulus 20, remainder 19);
給与履歴テーブル
CREATE TABLE SALARY_HISTORY (..割愛, EMP_ID bigint Default 0 NOT NULL, pay_month bigint Default 0 NOT NULL, ..割愛) PARTITION BY HASH (emp_id);
ALTER TABLE SALARY_HISTORY ADD CONSTRAINT PK_SALARY_HISTORY PRIMARY KEY (..割愛, pay_month, emp_id) USING INDEX ;
CREATE TABLE SALARY_HISTORY_INA_00 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 0);
CREATE TABLE SALARY_HISTORY_INA_01 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 1);
CREATE TABLE SALARY_HISTORY_INA_02 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 2);
CREATE TABLE SALARY_HISTORY_INA_03 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 3);
CREATE TABLE SALARY_HISTORY_INA_04 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 4);
CREATE TABLE SALARY_HISTORY_INA_05 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 5);
CREATE TABLE SALARY_HISTORY_INA_06 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 6);
CREATE TABLE SALARY_HISTORY_INA_07 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 7);
CREATE TABLE SALARY_HISTORY_INA_08 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 8);
CREATE TABLE SALARY_HISTORY_INA_09 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 9);
CREATE TABLE SALARY_HISTORY_INA_10 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 10);
CREATE TABLE SALARY_HISTORY_INA_11 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 11);
CREATE TABLE SALARY_HISTORY_INA_12 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 12);
CREATE TABLE SALARY_HISTORY_INA_13 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 13);
CREATE TABLE SALARY_HISTORY_INA_14 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 14);
CREATE TABLE SALARY_HISTORY_INA_15 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 15);
CREATE TABLE SALARY_HISTORY_INA_16 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 16);
CREATE TABLE SALARY_HISTORY_INA_17 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 17);
CREATE TABLE SALARY_HISTORY_INA_18 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 18);
CREATE TABLE SALARY_HISTORY_INA_19 PARTITION OF SALARY_HISTORY FOR VALUES WITH (modulus 20, remainder 19);
給与詳細の履歴テーブル
CREATE TABLE SALARY_DETAIL_HISTORY (emp_id bigint Default 0 NOT NULL, PAY_MONTH bigint Default 0 not null, item_id bigint Default 0 not null, amount numeric(18,8) Default 0 not null) PARTITION BY HASH (emp_id);
ALTER TABLE SALARY_DETAIL_HISTORY ADD CONSTRAINT PK_SALARY_DETAIL_HISTORY PRIMARY KEY (emp_id, pay_month,item_id) USING INDEX;
CREATE TABLE SALARY_DETAIL_HISTORY_00 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 0);
CREATE TABLE SALARY_DETAIL_HISTORY_01 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 1);
CREATE TABLE SALARY_DETAIL_HISTORY_02 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 2);
CREATE TABLE SALARY_DETAIL_HISTORY_03 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 3);
CREATE TABLE SALARY_DETAIL_HISTORY_04 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 4);
CREATE TABLE SALARY_DETAIL_HISTORY_05 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 5);
CREATE TABLE SALARY_DETAIL_HISTORY_06 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 6);
CREATE TABLE SALARY_DETAIL_HISTORY_07 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 7);
CREATE TABLE SALARY_DETAIL_HISTORY_08 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 8);
CREATE TABLE SALARY_DETAIL_HISTORY_09 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 9);
CREATE TABLE SALARY_DETAIL_HISTORY_10 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 10);
CREATE TABLE SALARY_DETAIL_HISTORY_11 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 11);
CREATE TABLE SALARY_DETAIL_HISTORY_12 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 12);
CREATE TABLE SALARY_DETAIL_HISTORY_13 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 13);
CREATE TABLE SALARY_DETAIL_HISTORY_14 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 14);
CREATE TABLE SALARY_DETAIL_HISTORY_15 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 15);
CREATE TABLE SALARY_DETAIL_HISTORY_16 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 16);
CREATE TABLE SALARY_DETAIL_HISTORY_17 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 17);
CREATE TABLE SALARY_DETAIL_HISTORY_18 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 18);
CREATE TABLE SALARY_DETAIL_HISTORY_19 PARTITION OF SALARY_DETAIL_HISTORY FOR VALUES WITH (modulus 20, remainder 19);
###検証用SQL
SQL①
SELECT * FROM person person
INNER JOIN salary_history salary_history ON person.emp_id = salary_history.emp_id
INNER JOIN salary_detail_history salary_detaiL_history ON salary_history.emp_id = salary_detail_history.emp_id AND salary_history.pay_month = salary_detail_history.pay_month
WHERE salary_history.pay_month = 200505 -- 月のパーティションが効く
order by person.emp_id,salary_history.pay_month,salary_detail_history.item_id
SQL②
select * from person person
inner join salary_history salary_history on person.emp_id = salary_history.emp_id
inner join salary_detail_history salary_detaiL_history on salary_history.emp_id = salary_detail_history.emp_id and salary_history.pay_month = salary_detail_history.pay_month
and person.working_years > 5
and salary_history.a_kei > 100000
and salary_detail_history.amount > 10000
and salary_detail_history.item_id = 111
order by person.emp_id, salary_detail_history.item_id,salary_detail_history.amount;
###検証結果
####SQL①のそれぞれの実行速度と実行計画
パーティション無し：**143**秒
Gather Merge (cost=252432.05..1444367.26 rows=2329704 width=1137) (actual time=154.482..143238.356 rows=2900000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Incremental Sort (cost=251432.03..1174461.58 rows=1164852 width=1137) (actual time=144.939..47732.262 rows=966667 loops=3)
Sort Key: person.emp_id, salary_detail_history.item_id
Presorted Key: person.emp_id
Full-sort Groups: 17233 Sort Method: quicksort Average Memory: 141kB Peak Memory: 141kB
Worker 0: Full-sort Groups: 16057 Sort Method: quicksort Average Memory: 141kB Peak Memory: 141kB
Worker 1: Full-sort Groups: 16710 Sort Method: quicksort Average Memory: 141kB Peak Memory: 141kB
-> Nested Loop (cost=251422.98..1120389.46 rows=1164852 width=1137) (actual time=141.287..42226.045 rows=966667 loops=3)
Join Filter: (person.emp_id = salary_detail_history.emp_id)
-> Merge Join (cost=251422.41..252563.99 rows=42724 width=1108) (actual time=138.743..340.883 rows=33333 loops=3)
Merge Cond: (salary_history.emp_id = person.emp_id)
-> Sort (cost=239429.57..239536.76 rows=42875 width=1088) (actual time=99.698..134.875 rows=33333 loops=3)
Sort Key: salary_history.emp_id
Sort Method: external merge Disk: 33024kB
Worker 0: Sort Method: external merge Disk: 30768kB
Worker 1: Sort Method: external merge Disk: 32016kB
-> Parallel Index Scan using pk_salary_history_no_par on salary_history_no_par salary_history (cost=0.43..215760.80 rows=42875 width=1088) (actual time=23.737..52.072 rows=33333 loops=3)
Index Cond: (pay_month = 200505)
-> Sort (cost=11992.82..12242.82 rows=100000 width=20) (actual time=39.005..62.490 rows=99861 loops=3)
Sort Key: person.emp_id
Sort Method: external merge Disk: 3040kB
Worker 0: Sort Method: external merge Disk: 3040kB
Worker 1: Sort Method: external merge Disk: 3040kB
-> Seq Scan on person_no_par person (cost=0.00..1637.00 rows=100000 width=20) (actual time=0.036..8.045 rows=100000 loops=3)
-> Index Scan using pk_salary_detail_history_no_par on salary_detail_history_no_par salary_detail_history (cost=0.56..19.85 rows=37 width=29) (actual time=1.221..1.233 rows=29 loops=100000)
Index Cond: ((emp_id = salary_history.emp_id) AND (pay_month = 200505))
Planning Time: 1.041 ms
Execution Time: 143399.350 ms
月毎パーティション：**4.7**秒
Merge Join (cost=37325.21..321503.41 rows=2900000 width=1150) (actual time=81.699..4589.899 rows=2900000 loops=1)
Merge Cond: (salary_detail_history.emp_id = person.emp_id)
-> Index Scan using salary_detail_history_par_month_17_pkey on salary_detail_history_par_month_17 salary_detail_history (cost=0.43..214781.31 rows=2900000 width=29) (actual time=0.025..949.646 rows=2900000 loops=1)
Index Cond: (pay_month = 200505)
-> Materialize (cost=37302.58..56244.31 rows=100000 width=1121) (actual time=81.661..1203.905 rows=2899972 loops=1)
-> Merge Join (cost=37302.58..55994.31 rows=100000 width=1121) (actual time=81.649..433.703 rows=100000 loops=1)
Merge Cond: (salary_history.emp_id = person.emp_id)
-> Gather Merge (cost=37301.09..48947.74 rows=100000 width=1101) (actual time=81.588..149.697 rows=100000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=36301.07..36405.24 rows=41667 width=1101) (actual time=68.957..86.857 rows=33333 loops=3)
Sort Key: salary_history.emp_id
Sort Method: external merge Disk: 32896kB
Worker 0: Sort Method: external merge Disk: 27536kB
Worker 1: Sort Method: external merge Disk: 35384kB
-> Parallel Seq Scan on salary_history_par_month_17 salary_history (cost=0.00..13020.83 rows=41667 width=1101) (actual time=0.007..11.638 rows=33333 loops=3)
Filter: (pay_month = 200505)
-> Index Scan using pk_person_no_par on person_no_par person (cost=0.29..5547.76 rows=100000 width=20) (actual time=0.046..56.366 rows=100000 loops=1)
Planning Time: 1.188 ms
Execution Time: 4726.102 ms
社員毎パーティション：**23.5**秒
Incremental Sort (cost=29.15..654071.39 rows=2894930 width=1137) (actual time=2.457..23269.837 rows=2900000 loops=1)
Sort Key: person.emp_id, salary_detail_history.item_id
Presorted Key: person.emp_id
Full-sort Groups: 50000 Sort Method: quicksort Average Memory: 141kB Peak Memory: 141kB
-> Merge Append (cost=23.03..494143.86 rows=2894930 width=1137) (actual time=1.936..7377.690 rows=2900000 loops=1)
Sort Key: person.emp_id
-> Nested Loop (cost=1.13..20873.88 rows=144389 width=1137) (actual time=0.115..352.926 rows=146276 loops=1)
-> Nested Loop (cost=0.70..6536.93 rows=4953 width=1108) (actual time=0.088..149.618 rows=5044 loops=1)
-> Index Scan using person_00_pkey on person_00 person_1 (cost=0.28..175.94 rows=5044 width=20) (actual time=0.035..2.682 rows=5044 loops=1)
-> Index Scan using salary_history_ina_00_emp_id_skdate_idx on salary_history_ina_00 salary_history_1 (cost=0.42..1.25 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=5044)
Index Cond: (emp_id = person_1.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_00_pkey on salary_detail_history_00 salary_detail_history_1 (cost=0.43..2.60 rows=29 width=29) (actual time=0.009..0.019 rows=29 loops=5044)
Index Cond: ((emp_id = person_1.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..20925.78 rows=148591 width=1137) (actual time=0.093..348.078 rows=144942 loops=1)
-> Nested Loop (cost=0.70..6461.84 rows=4972 width=1108) (actual time=0.066..148.234 rows=4998 loops=1)
-> Index Scan using person_01_pkey on person_01 person_2 (cost=0.28..174.25 rows=4998 width=20) (actual time=0.022..2.857 rows=4998 loops=1)
-> Index Scan using salary_history_ina_01_emp_id_skdate_idx on salary_history_ina_01 salary_history_2 (cost=0.42..1.25 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=4998)
Index Cond: (emp_id = person_2.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_01_pkey on salary_detail_history_01 salary_detail_history_2 (cost=0.43..2.61 rows=30 width=29) (actual time=0.009..0.019 rows=29 loops=4998)
Index Cond: ((emp_id = person_2.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..20155.56 rows=141331 width=1137) (actual time=0.097..345.721 rows=143173 loops=1)
-> Nested Loop (cost=0.70..6352.78 rows=4825 width=1108) (actual time=0.068..146.907 rows=4937 loops=1)
-> Index Scan using person_02_pkey on person_02 person_3 (cost=0.28..173.34 rows=4937 width=20) (actual time=0.023..2.930 rows=4937 loops=1)
-> Index Scan using salary_history_ina_02_emp_id_skdate_idx on salary_history_ina_02 salary_history_3 (cost=0.42..1.24 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=4937)
Index Cond: (emp_id = person_3.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_02_pkey on salary_detail_history_02 salary_detail_history_3 (cost=0.43..2.57 rows=29 width=29) (actual time=0.009..0.019 rows=29 loops=4937)
Index Cond: ((emp_id = person_3.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..20939.17 rows=145786 width=1137) (actual time=0.090..351.013 rows=146044 loops=1)
-> Nested Loop (cost=0.70..6528.33 rows=4983 width=1108) (actual time=0.065..149.605 rows=5036 loops=1)
-> Index Scan using person_03_pkey on person_03 person_4 (cost=0.28..175.82 rows=5036 width=20) (actual time=0.022..2.754 rows=5036 loops=1)
-> Index Scan using salary_history_ina_03_emp_id_skdate_idx on salary_history_ina_03 salary_history_4 (cost=0.42..1.25 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=5036)
Index Cond: (emp_id = person_4.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_03_pkey on salary_detail_history_03 salary_detail_history_4 (cost=0.43..2.60 rows=29 width=29) (actual time=0.009..0.019 rows=29 loops=5036)
Index Cond: ((emp_id = person_4.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..20444.34 rows=140793 width=1137) (actual time=0.096..345.646 rows=143405 loops=1)
-> Nested Loop (cost=0.70..6365.09 rows=4918 width=1108) (actual time=0.071..147.586 rows=4945 loops=1)
-> Index Scan using person_04_pkey on person_04 person_5 (cost=0.28..173.46 rows=4945 width=20) (actual time=0.027..2.770 rows=4945 loops=1)
-> Index Scan using salary_history_ina_04_emp_id_skdate_idx on salary_history_ina_04 salary_history_5 (cost=0.42..1.24 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=4945)
Index Cond: (emp_id = person_5.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_04_pkey on salary_detail_history_04 salary_detail_history_5 (cost=0.43..2.57 rows=29 width=29) (actual time=0.009..0.019 rows=29 loops=4945)
Index Cond: ((emp_id = person_5.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..21639.49 rows=148320 width=1137) (actual time=0.094..351.089 rows=146682 loops=1)
-> Nested Loop (cost=0.70..6565.52 rows=5200 width=1108) (actual time=0.069..150.087 rows=5058 loops=1)
-> Index Scan using person_05_pkey on person_05 person_6 (cost=0.28..176.15 rows=5058 width=20) (actual time=0.025..2.636 rows=5058 loops=1)
-> Index Scan using salary_history_ina_05_emp_id_skdate_idx on salary_history_ina_05 salary_history_6 (cost=0.42..1.25 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=5058)
Index Cond: (emp_id = person_6.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_05_pkey on salary_detail_history_05 salary_detail_history_6 (cost=0.43..2.61 rows=29 width=29) (actual time=0.009..0.019 rows=29 loops=5058)
Index Cond: ((emp_id = person_6.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..21104.17 rows=142704 width=1137) (actual time=0.109..351.549 rows=146856 loops=1)
-> Nested Loop (cost=0.70..6574.61 rows=5062 width=1108) (actual time=0.084..150.671 rows=5064 loops=1)
-> Index Scan using person_06_pkey on person_06 person_7 (cost=0.28..176.24 rows=5064 width=20) (actual time=0.026..2.790 rows=5064 loops=1)
-> Index Scan using salary_history_ina_06_emp_id_skdate_idx on salary_history_ina_06 salary_history_7 (cost=0.42..1.25 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=5064)
Index Cond: (emp_id = person_7.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_06_pkey on salary_detail_history_06 salary_detail_history_7 (cost=0.43..2.59 rows=28 width=29) (actual time=0.009..0.019 rows=29 loops=5064)
Index Cond: ((emp_id = person_7.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..20724.88 rows=146092 width=1137) (actual time=0.094..346.558 rows=144507 loops=1)
-> Nested Loop (cost=0.70..6428.79 rows=4921 width=1108) (actual time=0.070..150.078 rows=4983 loops=1)
-> Index Scan using person_07_pkey on person_07 person_8 (cost=0.28..174.03 rows=4983 width=20) (actual time=0.022..2.732 rows=4983 loops=1)
-> Index Scan using salary_history_ina_07_emp_id_skdate_idx on salary_history_ina_07 salary_history_8 (cost=0.42..1.25 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=4983)
Index Cond: (emp_id = person_8.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_07_pkey on salary_detail_history_07 salary_detail_history_8 (cost=0.43..2.61 rows=30 width=29) (actual time=0.009..0.019 rows=29 loops=4983)
Index Cond: ((emp_id = person_8.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..20826.12 rows=142481 width=1137) (actual time=0.094..349.667 rows=145406 loops=1)
-> Nested Loop (cost=0.70..6486.42 rows=5024 width=1108) (actual time=0.069..149.311 rows=5014 loops=1)
-> Index Scan using person_08_pkey on person_08 person_9 (cost=0.28..174.49 rows=5014 width=20) (actual time=0.023..2.614 rows=5014 loops=1)
-> Index Scan using salary_history_ina_08_emp_id_skdate_idx on salary_history_ina_08 salary_history_9 (cost=0.42..1.25 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=5014)
Index Cond: (emp_id = person_9.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_08_pkey on salary_detail_history_08 salary_detail_history_9 (cost=0.43..2.57 rows=28 width=29) (actual time=0.009..0.019 rows=29 loops=5014)
Index Cond: ((emp_id = person_9.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..20805.58 rows=144852 width=1137) (actual time=0.095..349.815 rows=143985 loops=1)
-> Nested Loop (cost=0.70..6397.78 rows=5020 width=1108) (actual time=0.067..150.136 rows=4965 loops=1)
-> Index Scan using person_09_pkey on person_09 person_10 (cost=0.28..173.76 rows=4965 width=20) (actual time=0.023..2.678 rows=4965 loops=1)
-> Index Scan using salary_history_ina_09_emp_id_skdate_idx on salary_history_ina_09 salary_history_10 (cost=0.42..1.24 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=4965)
Index Cond: (emp_id = person_10.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_09_pkey on salary_detail_history_09 salary_detail_history_10 (cost=0.43..2.58 rows=29 width=29) (actual time=0.009..0.019 rows=29 loops=4965)
Index Cond: ((emp_id = person_10.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..20018.21 rows=140032 width=1137) (actual time=0.097..343.690 rows=142100 loops=1)
-> Nested Loop (cost=0.70..6289.84 rows=4819 width=1108) (actual time=0.068..147.031 rows=4900 loops=1)
-> Index Scan using person_10_pkey on person_10 person_11 (cost=0.28..172.78 rows=4900 width=20) (actual time=0.023..2.712 rows=4900 loops=1)
-> Index Scan using salary_history_ina_10_emp_id_skdate_idx on salary_history_ina_10 salary_history_11 (cost=0.42..1.24 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=4900)
Index Cond: (emp_id = person_11.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_10_pkey on salary_detail_history_10 salary_detail_history_11 (cost=0.43..2.56 rows=29 width=29) (actual time=0.009..0.019 rows=29 loops=4900)
Index Cond: ((emp_id = person_11.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..21436.25 rows=148022 width=1137) (actual time=0.096..353.547 rows=147465 loops=1)
-> Nested Loop (cost=0.70..6613.46 rows=5099 width=1108) (actual time=0.068..150.132 rows=5085 loops=1)
-> Index Scan using person_11_pkey on person_11 person_12 (cost=0.28..176.56 rows=5085 width=20) (actual time=0.023..2.799 rows=5085 loops=1)
-> Index Scan using salary_history_ina_11_emp_id_skdate_idx on salary_history_ina_11 salary_history_12 (cost=0.42..1.26 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=5085)
Index Cond: (emp_id = person_12.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_11_pkey on salary_detail_history_11 salary_detail_history_12 (cost=0.43..2.62 rows=29 width=29) (actual time=0.009..0.019 rows=29 loops=5085)
Index Cond: ((emp_id = person_12.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..21156.08 rows=143961 width=1137) (actual time=0.090..349.216 rows=146363 loops=1)
-> Nested Loop (cost=0.70..6545.00 rows=5099 width=1108) (actual time=0.065..148.355 rows=5047 loops=1)
-> Index Scan using person_12_pkey on person_12 person_13 (cost=0.28..175.99 rows=5047 width=20) (actual time=0.023..2.637 rows=5047 loops=1)
-> Index Scan using salary_history_ina_12_emp_id_skdate_idx on salary_history_ina_12 salary_history_13 (cost=0.42..1.25 rows=1 width=1088) (actual time=0.022..0.024 rows=1 loops=5047)
Index Cond: (emp_id = person_13.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_12_pkey on salary_detail_history_12 salary_detail_history_13 (cost=0.43..2.59 rows=28 width=29) (actual time=0.009..0.019 rows=29 loops=5047)
Index Cond: ((emp_id = person_13.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..20420.46 rows=145300 width=1137) (actual time=0.093..344.095 rows=143695 loops=1)
-> Nested Loop (cost=0.70..6384.70 rows=4846 width=1108) (actual time=0.067..147.772 rows=4955 loops=1)
-> Index Scan using person_13_pkey on person_13 person_14 (cost=0.28..173.61 rows=4955 width=20) (actual time=0.026..2.724 rows=4955 loops=1)
-> Index Scan using salary_history_ina_13_emp_id_skdate_idx on salary_history_ina_13 salary_history_14 (cost=0.42..1.24 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=4955)
Index Cond: (emp_id = person_14.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_13_pkey on salary_detail_history_13 salary_detail_history_14 (cost=0.43..2.60 rows=30 width=29) (actual time=0.009..0.019 rows=29 loops=4955)
Index Cond: ((emp_id = person_14.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..21681.52 rows=151158 width=1137) (actual time=0.097..354.265 rows=148567 loops=1)
-> Nested Loop (cost=0.70..6681.19 rows=5139 width=1108) (actual time=0.070..151.182 rows=5123 loops=1)
-> Index Scan using person_14_pkey on person_14 person_15 (cost=0.28..177.13 rows=5123 width=20) (actual time=0.025..2.766 rows=5123 loops=1)
-> Index Scan using salary_history_ina_14_emp_id_skdate_idx on salary_history_ina_14 salary_history_15 (cost=0.42..1.26 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=5123)
Index Cond: (emp_id = person_15.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_14_pkey on salary_detail_history_14 salary_detail_history_15 (cost=0.43..2.63 rows=29 width=29) (actual time=0.009..0.019 rows=29 loops=5123)
Index Cond: ((emp_id = person_15.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..19851.03 rows=138042 width=1137) (actual time=0.096..337.056 rows=140621 loops=1)
-> Nested Loop (cost=0.70..6198.03 rows=4819 width=1108) (actual time=0.070..144.314 rows=4849 loops=1)
-> Index Scan using person_15_pkey on person_15 person_16 (cost=0.28..171.02 rows=4849 width=20) (actual time=0.026..2.627 rows=4849 loops=1)
-> Index Scan using salary_history_ina_15_emp_id_skdate_idx on salary_history_ina_15 salary_history_16 (cost=0.42..1.23 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=4849)
Index Cond: (emp_id = person_16.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_15_pkey on salary_detail_history_15 salary_detail_history_16 (cost=0.43..2.54 rows=29 width=29) (actual time=0.009..0.019 rows=29 loops=4849)
Index Cond: ((emp_id = person_16.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..21536.68 rows=149239 width=1137) (actual time=0.097..352.720 rows=147204 loops=1)
-> Nested Loop (cost=0.70..6596.45 rows=5144 width=1108) (actual time=0.070..149.439 rows=5076 loops=1)
-> Index Scan using person_16_pkey on person_16 person_17 (cost=0.28..176.42 rows=5076 width=20) (actual time=0.026..2.576 rows=5076 loops=1)
-> Index Scan using salary_history_ina_16_emp_id_skdate_idx on salary_history_ina_16 salary_history_17 (cost=0.42..1.25 rows=1 width=1088) (actual time=0.022..0.024 rows=1 loops=5076)
Index Cond: (emp_id = person_17.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_16_pkey on salary_detail_history_16 salary_detail_history_17 (cost=0.43..2.61 rows=29 width=29) (actual time=0.009..0.019 rows=29 loops=5076)
Index Cond: ((emp_id = person_17.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..20853.04 rows=144014 width=1137) (actual time=0.094..348.635 rows=145058 loops=1)
-> Nested Loop (cost=0.70..6465.93 rows=4994 width=1108) (actual time=0.068..148.713 rows=5002 loops=1)
-> Index Scan using person_17_pkey on person_17 person_18 (cost=0.28..174.31 rows=5002 width=20) (actual time=0.023..2.564 rows=5002 loops=1)
-> Index Scan using salary_history_ina_17_emp_id_skdate_idx on salary_history_ina_17 salary_history_18 (cost=0.42..1.25 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=5002)
Index Cond: (emp_id = person_18.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_17_pkey on salary_detail_history_17 salary_detail_history_18 (cost=0.43..2.59 rows=29 width=29) (actual time=0.009..0.019 rows=29 loops=5002)
Index Cond: ((emp_id = person_18.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..20979.59 rows=146534 width=1137) (actual time=0.098..347.327 rows=143463 loops=1)
-> Nested Loop (cost=0.70..6366.44 rows=5103 width=1108) (actual time=0.067..147.840 rows=4947 loops=1)
-> Index Scan using person_18_pkey on person_18 person_19 (cost=0.28..173.49 rows=4947 width=20) (actual time=0.023..2.695 rows=4947 loops=1)
-> Index Scan using salary_history_ina_18_emp_id_skdate_idx on salary_history_ina_18 salary_history_19 (cost=0.42..1.24 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=4947)
Index Cond: (emp_id = person_19.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_18_pkey on salary_detail_history_18 salary_detail_history_19 (cost=0.43..2.57 rows=29 width=29) (actual time=0.009..0.019 rows=29 loops=4947)
Index Cond: ((emp_id = person_19.emp_id) AND (pay_month = 200505))
-> Nested Loop (cost=1.13..20738.53 rows=143289 width=1137) (actual time=0.094..347.843 rows=144188 loops=1)
-> Nested Loop (cost=0.70..6409.25 rows=4990 width=1108) (actual time=0.066..149.116 rows=4972 loops=1)
-> Index Scan using person_19_pkey on person_19 person_20 (cost=0.28..173.86 rows=4972 width=20) (actual time=0.024..2.775 rows=4972 loops=1)
-> Index Scan using salary_history_ina_19_emp_id_skdate_idx on salary_history_ina_19 salary_history_20 (cost=0.42..1.24 rows=1 width=1088) (actual time=0.023..0.024 rows=1 loops=4972)
Index Cond: (emp_id = person_20.emp_id)
Filter: (pay_month = 200505)
Rows Removed by Filter: 17
-> Index Scan using salary_detail_history_19_pkey on salary_detail_history_19 salary_detail_history_20 (cost=0.43..2.58 rows=29 width=29) (actual time=0.009..0.019 rows=29 loops=4972)
Index Cond: ((emp_id = person_20.emp_id) AND (pay_month = 200505))
Planning Time: 21.984 ms
Execution Time: 23401.814 ms
パーティションを作成したどちらも、パーティション無しの速度より速くなりました。
月毎のパーティションの実行計画をみると
該当月のパーティション(salary_history_par_month_17)のみアクセスしているため、全体の20分の1のアクセスで済んでいます。(パーティションプルーニングにより、検索不要なパーティションにそもそもアクセスしません)
また、今回のorder句がindexScan列に含まれていて、sortが全テーブルに行われなかったことが他よりも速度が速くなった要因のようです。
社員毎のパーティションの実行計画は長くなりすぎてわかりづらいですが、
同じ社員が格納されているパーティション同士を結合し、その結果をパーティション数分appendしています。
(person_00,salary_history_00,salary_detail_history_00の結合結果とperson_01,salary_history_01,salary_detail_history_01の結合結果..)
総量が20分の1同士の結合となり、他2つとは異なる結合方法をとった結果、検索速度も速くなったようです。
ただし、パーティション毎の検索をappendする構造上、最終結果セットに対するsortが必要なため、月毎パーティションの速度より遅くなっています。
####SQL②のそれぞれの実行速度と実行計画
パーティション無し：**58**秒
Gather Merge (cost=1325586.94..1358775.79 rows=284456 width=1137) (actual time=53919.198..57963.361 rows=1584000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=1324586.92..1324942.49 rows=142228 width=1137) (actual time=53664.041..55148.052 rows=528000 loops=3)
Sort Key: person.emp_id, salary_detail_history.amount
Sort Method: external merge Disk: 534712kB
Worker 0: Sort Method: external merge Disk: 528240kB
Worker 1: Sort Method: external merge Disk: 531704kB
-> Parallel Hash Join (cost=722985.11..1170460.73 rows=142228 width=1137) (actual time=16547.620..18111.677 rows=528000 loops=3)
Hash Cond: ((salary_history.emp_id = person.emp_id) AND (salary_history.pay_month = salary_detail_history.pay_month))
-> Parallel Seq Scan on salary_history_no_par salary_history (cost=0.00..234375.00 rows=750000 width=1088) (actual time=0.016..1034.307 rows=600000 loops=3)
Filter: (a_kei > '100000'::numeric)
-> Parallel Hash (cost=719450.64..719450.64 rows=142698 width=49) (actual time=4083.460..4083.462 rows=528000 loops=3)
Buckets: 65536 (originally 65536) Batches: 64 (originally 16) Memory Usage: 2688kB
-> Hash Join (cost=3497.46..719450.64 rows=142698 width=49) (actual time=41.388..3749.013 rows=528000 loops=3)
Hash Cond: (salary_detail_history.emp_id = person.emp_id)
-> Parallel Seq Scan on salary_detail_history_no_par salary_detail_history (cost=0.00..712786.12 rows=162680 width=29) (actual time=0.010..3402.129 rows=600000 loops=3)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 16866667
-> Hash (cost=1887.00..1887.00 rows=87717 width=20) (actual time=41.164..41.165 rows=88000 loops=3)
Buckets: 65536 Batches: 2 Memory Usage: 2789kB
-> Seq Scan on person_no_par person (cost=0.00..1887.00 rows=87717 width=20) (actual time=0.028..23.302 rows=88000 loops=3)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 12000
Planning Time: 0.911 ms
Execution Time: 58112.006 ms
月毎パーティション(パーティションワイズOFF)：**58**秒
Gather Merge (cost=1323134.01..1354117.01 rows=265550 width=1146) (actual time=54239.133..58285.958 rows=1584000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=1322133.99..1322465.92 rows=132775 width=1146) (actual time=53932.254..55420.097 rows=528000 loops=3)
Sort Key: person.emp_id, salary_detail_history.amount
Sort Method: external merge Disk: 529280kB
Worker 0: Sort Method: external merge Disk: 538088kB
Worker 1: Sort Method: external merge Disk: 527312kB
-> Parallel Hash Join (cost=723367.24..1177408.75 rows=132775 width=1146) (actual time=16596.516..18142.054 rows=528000 loops=3)
Hash Cond: ((salary_history.emp_id = person.emp_id) AND (salary_history.pay_month = salary_detail_history.pay_month))
-> Parallel Append (cost=0.00..238145.19 rows=750008 width=1097) (actual time=0.017..1087.205 rows=600000 loops=3)
-> Parallel Seq Scan on salary_history_par_month_01 salary_history_2 (cost=0.00..13020.83 rows=41667 width=1093) (actual time=0.016..177.723 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_02 salary_history_3 (cost=0.00..13020.83 rows=41667 width=1097) (actual time=0.012..175.439 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_03 salary_history_4 (cost=0.00..13020.83 rows=41667 width=1093) (actual time=0.007..172.650 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_04 salary_history_5 (cost=0.00..13020.83 rows=41667 width=1093) (actual time=0.007..173.351 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_05 salary_history_6 (cost=0.00..13020.83 rows=41667 width=1093) (actual time=0.007..173.946 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_06 salary_history_7 (cost=0.00..13020.83 rows=41667 width=1097) (actual time=0.007..169.400 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_07 salary_history_8 (cost=0.00..13020.83 rows=41667 width=1097) (actual time=0.007..173.483 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_08 salary_history_9 (cost=0.00..13020.83 rows=41667 width=1097) (actual time=0.006..172.174 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_09 salary_history_10 (cost=0.00..13020.83 rows=41667 width=1097) (actual time=0.008..172.905 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_10 salary_history_11 (cost=0.00..13020.83 rows=41667 width=1097) (actual time=0.007..171.282 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_11 salary_history_12 (cost=0.00..13020.83 rows=41667 width=1097) (actual time=0.007..171.763 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_12 salary_history_13 (cost=0.00..13020.83 rows=41667 width=1073) (actual time=0.007..176.954 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_13 salary_history_14 (cost=0.00..13020.83 rows=41667 width=1097) (actual time=0.005..57.374 rows=33333 loops=3)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_14 salary_history_15 (cost=0.00..13020.83 rows=41667 width=1115) (actual time=0.010..170.012 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_15 salary_history_16 (cost=0.00..13020.83 rows=41667 width=1101) (actual time=0.006..172.650 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_16 salary_history_17 (cost=0.00..13020.83 rows=41667 width=1101) (actual time=0.008..169.407 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_17 salary_history_18 (cost=0.00..13020.83 rows=41667 width=1101) (actual time=0.007..171.513 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_18 salary_history_19 (cost=0.00..13020.83 rows=41667 width=1101) (actual time=0.016..173.132 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_00 salary_history_1 (cost=0.00..10.07 rows=2 width=4646) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Seq Scan on salary_history_par_month_19 salary_history_20 (cost=0.00..10.07 rows=2 width=4646) (actual time=0.002..0.003 rows=0 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Parallel Hash (cost=720078.61..720078.61 rows=132775 width=49) (actual time=4164.591..4164.598 rows=528000 loops=3)
Buckets: 65536 (originally 65536) Batches: 64 (originally 8) Memory Usage: 2720kB
-> Hash Join (cost=3497.46..720078.61 rows=132775 width=49) (actual time=41.082..3790.983 rows=528000 loops=3)
Hash Cond: (salary_detail_history.emp_id = person.emp_id)
-> Parallel Append (cost=0.00..713599.79 rows=151369 width=29) (actual time=0.013..3434.365 rows=600000 loops=3)
-> Parallel Seq Scan on salary_detail_history_par_month_14 salary_detail_history_15 (cost=0.00..42170.00 rows=10732 width=29) (actual time=0.012..690.455 rows=100000 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 3000000
-> Parallel Seq Scan on salary_detail_history_par_month_01 salary_detail_history_2 (cost=0.00..39449.00 rows=8642 width=29) (actual time=0.011..653.964 rows=100000 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 2800000
-> Parallel Seq Scan on salary_detail_history_par_month_02 salary_detail_history_3 (cost=0.00..39449.00 rows=8901 width=29) (actual time=0.005..672.520 rows=100000 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 2800000
-> Parallel Seq Scan on salary_detail_history_par_month_03 salary_detail_history_4 (cost=0.00..39449.00 rows=8748 width=29) (actual time=0.005..654.858 rows=100000 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 2800000
-> Parallel Seq Scan on salary_detail_history_par_month_04 salary_detail_history_5 (cost=0.00..39449.00 rows=8426 width=29) (actual time=0.005..655.985 rows=100000 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 2800000
-> Parallel Seq Scan on salary_detail_history_par_month_05 salary_detail_history_6 (cost=0.00..39449.00 rows=8565 width=29) (actual time=0.008..648.399 rows=100000 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 2800000
-> Parallel Seq Scan on salary_detail_history_par_month_06 salary_detail_history_7 (cost=0.00..39449.00 rows=5656 width=29) (actual time=0.005..626.589 rows=100000 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 2800000
-> Parallel Seq Scan on salary_detail_history_par_month_07 salary_detail_history_8 (cost=0.00..39449.00 rows=8910 width=29) (actual time=0.005..634.864 rows=100000 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 2800000
-> Parallel Seq Scan on salary_detail_history_par_month_08 salary_detail_history_9 (cost=0.00..39449.00 rows=8344 width=29) (actual time=0.005..630.819 rows=100000 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 2800000
-> Parallel Seq Scan on salary_detail_history_par_month_09 salary_detail_history_10 (cost=0.00..39449.00 rows=8538 width=29) (actual time=0.007..628.903 rows=100000 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 2800000
-> Parallel Seq Scan on salary_detail_history_par_month_10 salary_detail_history_11 (cost=0.00..39449.00 rows=7978 width=29) (actual time=0.007..177.582 rows=33333 loops=3)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 933333
-> Parallel Seq Scan on salary_detail_history_par_month_11 salary_detail_history_12 (cost=0.00..39449.00 rows=7915 width=29) (actual time=0.009..452.913 rows=100000 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 2800000
-> Parallel Seq Scan on salary_detail_history_par_month_12 salary_detail_history_13 (cost=0.00..39449.00 rows=8835 width=29) (actual time=0.008..440.205 rows=100000 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 2800000
-> Parallel Seq Scan on salary_detail_history_par_month_13 salary_detail_history_14 (cost=0.00..39449.00 rows=7935 width=29) (actual time=0.006..438.558 rows=100000 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 2800000
-> Parallel Seq Scan on salary_detail_history_par_month_15 salary_detail_history_16 (cost=0.00..39449.00 rows=8230 width=29) (actual time=0.009..435.743 rows=100000 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 2800000
-> Parallel Seq Scan on salary_detail_history_par_month_16 salary_detail_history_17 (cost=0.00..39449.00 rows=7900 width=29) (actual time=0.007..461.121 rows=100000 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 2800000
-> Parallel Seq Scan on salary_detail_history_par_month_17 salary_detail_history_18 (cost=0.00..39449.00 rows=8429 width=29) (actual time=0.010..448.207 rows=100000 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 2800000
-> Parallel Seq Scan on salary_detail_history_par_month_18 salary_detail_history_19 (cost=0.00..39449.00 rows=8683 width=29) (actual time=0.011..446.574 rows=100000 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
Rows Removed by Filter: 2800000
-> Parallel Seq Scan on salary_detail_history_par_month_00 salary_detail_history_1 (cost=0.00..19.97 rows=1 width=44) (actual time=0.000..0.000 rows=0 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
-> Parallel Seq Scan on salary_detail_history_par_month_19 salary_detail_history_20 (cost=0.00..19.97 rows=1 width=44) (actual time=0.001..0.002 rows=0 loops=1)
Filter: ((amount > '10000'::numeric) AND (item_id = 111))
-> Hash (cost=1887.00..1887.00 rows=87717 width=20) (actual time=40.854..40.855 rows=88000 loops=3)
Buckets: 65536 Batches: 2 Memory Usage: 2789kB
-> Seq Scan on person_no_par person (cost=0.00..1887.00 rows=87717 width=20) (actual time=0.031..23.039 rows=88000 loops=3)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 12000
Planning Time: 6.858 ms
Execution Time: 58433.504 ms
月毎パーティション(パーティションワイズON)：**43**秒
Gather Merge (cost=825502.52..863423.40 rows=265550 width=1146) (actual time=36421.408..42935.334 rows=1584000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Incremental Sort (cost=824502.49..831772.32 rows=132775 width=1146) (actual time=34799.386..39101.023 rows=528000 loops=3)
Sort Key: person.emp_id, salary_detail_history.amount
Presorted Key: person.emp_id
Full-sort Groups: 14667 Sort Method: quicksort Average Memory: 97kB Peak Memory: 97kB
Worker 0: Full-sort Groups: 14685 Sort Method: quicksort Average Memory: 97kB Peak Memory: 97kB
Worker 1: Full-sort Groups: 14667 Sort Method: quicksort Average Memory: 91kB Peak Memory: 91kB
-> Merge Join (cost=824502.45..827014.26 rows=132775 width=1146) (actual time=34798.961..36245.248 rows=528000 loops=3)
Merge Cond: (salary_history.emp_id = person.emp_id)
-> Sort (cost=813614.56..813990.94 rows=150553 width=1126) (actual time=34756.701..35061.437 rows=599995 loops=3)
Sort Key: salary_history.emp_id
Sort Method: external merge Disk: 591200kB
Worker 0: Sort Method: external merge Disk: 592096kB
Worker 1: Sort Method: external merge Disk: 586536kB
-> Parallel Append (cost=0.43..652463.07 rows=150553 width=1126) (actual time=0.057..4894.890 rows=600000 loops=3)
-> Nested Loop (cost=0.43..36353.97 rows=10732 width=1126) (actual time=0.056..824.821 rows=100000 loops=1)
-> Parallel Seq Scan on salary_history_par_month_14 salary_history_15 (cost=0.00..13020.83 rows=41667 width=1115) (actual time=0.009..191.139 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_14_pkey on salary_detail_history_par_month_14 salary_detail_history_15 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.005 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_15.emp_id) AND (pay_month = salary_history_15.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.43..36200.91 rows=8361 width=1126) (actual time=0.059..799.162 rows=100000 loops=1)
-> Parallel Seq Scan on salary_history_par_month_04 salary_history_5 (cost=0.00..13020.83 rows=41667 width=1093) (actual time=0.009..187.395 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_04_pkey on salary_detail_history_par_month_04 salary_detail_history_5 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_5.emp_id) AND (pay_month = salary_history_5.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.43..36199.80 rows=8910 width=1126) (actual time=0.033..811.353 rows=100000 loops=1)
-> Parallel Seq Scan on salary_history_par_month_07 salary_history_8 (cost=0.00..13020.83 rows=41667 width=1097) (actual time=0.008..192.810 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_07_pkey on salary_detail_history_par_month_07 salary_detail_history_8 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_8.emp_id) AND (pay_month = salary_history_8.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.43..36199.06 rows=8642 width=1126) (actual time=0.034..828.284 rows=100000 loops=1)
-> Parallel Seq Scan on salary_history_par_month_01 salary_history_2 (cost=0.00..13020.83 rows=41667 width=1093) (actual time=0.008..196.300 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_01_pkey on salary_detail_history_par_month_01 salary_detail_history_2 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.005 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_2.emp_id) AND (pay_month = salary_history_2.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.43..36199.06 rows=7935 width=1126) (actual time=0.043..801.388 rows=100000 loops=1)
-> Parallel Seq Scan on salary_history_par_month_13 salary_history_14 (cost=0.00..13020.83 rows=41667 width=1097) (actual time=0.007..188.052 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_13_pkey on salary_detail_history_par_month_13 salary_detail_history_14 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_14.emp_id) AND (pay_month = salary_history_14.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.43..36198.41 rows=8748 width=1126) (actual time=0.042..789.129 rows=100000 loops=1)
-> Parallel Seq Scan on salary_history_par_month_03 salary_history_4 (cost=0.00..13020.83 rows=41667 width=1093) (actual time=0.008..183.533 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_03_pkey on salary_detail_history_par_month_03 salary_detail_history_4 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_4.emp_id) AND (pay_month = salary_history_4.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.43..36198.14 rows=8787 width=1126) (actual time=0.044..798.069 rows=100000 loops=1)
-> Parallel Seq Scan on salary_history_par_month_12 salary_history_13 (cost=0.00..13020.83 rows=41667 width=1073) (actual time=0.008..185.302 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_12_pkey on salary_detail_history_par_month_12 salary_detail_history_13 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_13.emp_id) AND (pay_month = salary_history_13.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.43..36197.95 rows=8730 width=1126) (actual time=0.048..790.729 rows=100000 loops=1)
-> Parallel Seq Scan on salary_history_par_month_02 salary_history_3 (cost=0.00..13020.83 rows=41667 width=1097) (actual time=0.007..188.635 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_02_pkey on salary_detail_history_par_month_02 salary_detail_history_3 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_3.emp_id) AND (pay_month = salary_history_3.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.43..36197.12 rows=7915 width=1126) (actual time=0.058..825.022 rows=100000 loops=1)
-> Parallel Seq Scan on salary_history_par_month_11 salary_history_12 (cost=0.00..13020.83 rows=41667 width=1097) (actual time=0.007..204.786 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_11_pkey on salary_detail_history_par_month_11 salary_detail_history_12 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.005 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_12.emp_id) AND (pay_month = salary_history_12.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.43..36197.02 rows=8565 width=1126) (actual time=0.040..806.429 rows=100000 loops=1)
-> Parallel Seq Scan on salary_history_par_month_05 salary_history_6 (cost=0.00..13020.83 rows=41667 width=1093) (actual time=0.007..188.932 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_05_pkey on salary_detail_history_par_month_05 salary_detail_history_6 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.005 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_6.emp_id) AND (pay_month = salary_history_6.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.43..36196.84 rows=8344 width=1126) (actual time=0.029..265.473 rows=33333 loops=3)
-> Parallel Seq Scan on salary_history_par_month_08 salary_history_9 (cost=0.00..13020.83 rows=41667 width=1097) (actual time=0.005..61.042 rows=33333 loops=3)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_08_pkey on salary_detail_history_par_month_08 salary_detail_history_9 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_9.emp_id) AND (pay_month = salary_history_9.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.43..36196.56 rows=5555 width=1126) (actual time=0.041..796.150 rows=100000 loops=1)
-> Parallel Seq Scan on salary_history_par_month_06 salary_history_7 (cost=0.00..13020.83 rows=41667 width=1097) (actual time=0.007..191.495 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_06_pkey on salary_detail_history_par_month_06 salary_detail_history_7 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_7.emp_id) AND (pay_month = salary_history_7.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.43..36195.27 rows=7808 width=1126) (actual time=0.022..414.045 rows=50000 loops=2)
-> Parallel Seq Scan on salary_history_par_month_16 salary_history_17 (cost=0.00..13020.83 rows=41667 width=1101) (actual time=0.004..103.146 rows=50000 loops=2)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_16_pkey on salary_detail_history_par_month_16 salary_detail_history_17 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.005 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_17.emp_id) AND (pay_month = salary_history_17.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.43..36194.43 rows=8310 width=1126) (actual time=0.044..798.307 rows=100000 loops=1)
-> Parallel Seq Scan on salary_history_par_month_17 salary_history_18 (cost=0.00..13020.83 rows=41667 width=1101) (actual time=0.008..193.527 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_17_pkey on salary_detail_history_par_month_17 salary_detail_history_18 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_18.emp_id) AND (pay_month = salary_history_18.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.43..36193.88 rows=8467 width=1126) (actual time=0.038..796.088 rows=100000 loops=1)
-> Parallel Seq Scan on salary_history_par_month_09 salary_history_10 (cost=0.00..13020.83 rows=41667 width=1097) (actual time=0.007..192.208 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_09_pkey on salary_detail_history_par_month_09 salary_detail_history_10 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_10.emp_id) AND (pay_month = salary_history_10.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.43..36193.14 rows=8676 width=1126) (actual time=0.038..794.900 rows=100000 loops=1)
-> Parallel Seq Scan on salary_history_par_month_18 salary_history_19 (cost=0.00..13020.83 rows=41667 width=1101) (actual time=0.007..191.928 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_18_pkey on salary_detail_history_par_month_18 salary_detail_history_19 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_19.emp_id) AND (pay_month = salary_history_19.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.43..36189.25 rows=8230 width=1126) (actual time=0.036..825.300 rows=100000 loops=1)
-> Parallel Seq Scan on salary_history_par_month_15 salary_history_16 (cost=0.00..13020.83 rows=41667 width=1101) (actual time=0.008..198.057 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_15_pkey on salary_detail_history_par_month_15 salary_detail_history_16 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.005 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_16.emp_id) AND (pay_month = salary_history_16.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.43..36188.60 rows=7836 width=1126) (actual time=0.051..785.764 rows=100000 loops=1)
-> Parallel Seq Scan on salary_history_par_month_10 salary_history_11 (cost=0.00..13020.83 rows=41667 width=1097) (actual time=0.014..192.746 rows=100000 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_10_pkey on salary_detail_history_par_month_10 salary_detail_history_11 (cost=0.43..0.55 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=100000)
Index Cond: ((emp_id = salary_history_11.emp_id) AND (pay_month = salary_history_11.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.15..10.45 rows=1 width=1126) (actual time=0.001..0.002 rows=0 loops=1)
-> Parallel Seq Scan on salary_history_par_month_00 salary_history_1 (cost=0.00..10.07 rows=2 width=4646) (actual time=0.000..0.001 rows=0 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_00_pkey on salary_detail_history_par_month_00 salary_detail_history_1 (cost=0.15..0.18 rows=1 width=44) (never executed)
Index Cond: ((emp_id = salary_history_1.emp_id) AND (pay_month = salary_history_1.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=0.15..10.45 rows=1 width=1126) (actual time=0.002..0.003 rows=0 loops=1)
-> Parallel Seq Scan on salary_history_par_month_19 salary_history_20 (cost=0.00..10.07 rows=2 width=4646) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_par_month_19_pkey on salary_detail_history_par_month_19 salary_detail_history_20 (cost=0.15..0.18 rows=1 width=44) (never executed)
Index Cond: ((emp_id = salary_history_20.emp_id) AND (pay_month = salary_history_20.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Sort (cost=10887.82..11107.11 rows=87717 width=20) (actual time=42.228..55.907 rows=88000 loops=3)
Sort Key: person.emp_id
Sort Method: external merge Disk: 2680kB
Worker 0: Sort Method: external merge Disk: 2680kB
Worker 1: Sort Method: external merge Disk: 2680kB
-> Seq Scan on person_no_par person (cost=0.00..1887.00 rows=87717 width=20) (actual time=0.046..18.891 rows=88000 loops=3)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 12000
Planning Time: 11.998 ms
Execution Time: 43093.129 ms
社員毎パーティション(パーティションワイズON)：**47**秒
Gather Merge (cost=733429.83..764803.69 rows=268900 width=1137) (actual time=45384.155..46426.552 rows=1584000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=732429.81..732765.94 rows=134450 width=1137) (actual time=44093.467..44341.969 rows=528000 loops=3)
Sort Key: person.emp_id, salary_detail_history.amount
Sort Method: external merge Disk: 505256kB
Worker 0: Sort Method: external merge Disk: 545848kB
Worker 1: Sort Method: external merge Disk: 543608kB
-> Parallel Append (cost=1.13..586786.88 rows=134450 width=1137) (actual time=0.092..18289.664 rows=528000 loops=3)
-> Nested Loop (cost=1.13..30233.89 rows=9958 width=1137) (actual time=0.092..3036.573 rows=80604 loops=1)
Join Filter: (person_17.emp_id = salary_history_17.emp_id)
-> Nested Loop (cost=0.71..20624.38 rows=9957 width=49) (actual time=0.059..216.783 rows=80604 loops=1)
-> Parallel Index Scan using person_16_pkey on person_16 person_17 (cost=0.28..162.99 rows=2634 width=20) (actual time=0.031..2.798 rows=4478 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 598
-> Index Scan using salary_detail_history_16_pkey on salary_detail_history_16 salary_detail_history_17 (cost=0.43..7.73 rows=4 width=29) (actual time=0.007..0.044 rows=18 loops=4478)
Index Cond: ((emp_id = person_17.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_16_emp_id_skdate_idx on salary_history_ina_16 salary_history_17 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.019..0.033 rows=1 loops=80604)
Index Cond: (emp_id = salary_detail_history_17.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_17.pay_month = pay_month))
Rows Removed by Filter: 17
-> Nested Loop (cost=1.13..29937.29 rows=9696 width=1137) (actual time=0.088..2999.297 rows=80478 loops=1)
Join Filter: (person_12.emp_id = salary_history_12.emp_id)
-> Nested Loop (cost=0.71..20574.89 rows=9698 width=49) (actual time=0.057..215.916 rows=80478 loops=1)
-> Parallel Index Scan using person_11_pkey on person_11 person_12 (cost=0.28..163.10 rows=2630 width=20) (actual time=0.031..2.873 rows=4471 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 614
-> Index Scan using salary_detail_history_11_pkey on salary_detail_history_11 salary_detail_history_12 (cost=0.43..7.72 rows=4 width=29) (actual time=0.008..0.044 rows=18 loops=4471)
Index Cond: ((emp_id = person_12.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_11_emp_id_skdate_idx on salary_history_ina_11 salary_history_12 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.019..0.033 rows=1 loops=80478)
Index Cond: (emp_id = salary_detail_history_12.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_12.pay_month = pay_month))
Rows Removed by Filter: 17
-> Nested Loop (cost=1.13..29929.23 rows=9386 width=1137) (actual time=0.093..3061.163 rows=81594 loops=1)
Join Filter: (person_15.emp_id = salary_history_15.emp_id)
-> Nested Loop (cost=0.71..20857.54 rows=9387 width=49) (actual time=0.057..220.074 rows=81594 loops=1)
-> Parallel Index Scan using person_14_pkey on person_14 person_15 (cost=0.28..163.57 rows=2666 width=20) (actual time=0.021..2.945 rows=4533 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 590
-> Index Scan using salary_detail_history_14_pkey on salary_detail_history_14 salary_detail_history_15 (cost=0.43..7.72 rows=4 width=29) (actual time=0.008..0.044 rows=18 loops=4533)
Index Cond: ((emp_id = person_15.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_14_emp_id_skdate_idx on salary_history_ina_14 salary_history_15 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.019..0.033 rows=1 loops=81594)
Index Cond: (emp_id = salary_detail_history_15.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_15.pay_month = pay_month))
Rows Removed by Filter: 17
-> Nested Loop (cost=1.13..29656.86 rows=9708 width=1137) (actual time=0.093..2981.025 rows=79434 loops=1)
Join Filter: (person_7.emp_id = salary_history_7.emp_id)
-> Nested Loop (cost=0.71..20291.04 rows=9708 width=49) (actual time=0.058..214.228 rows=79434 loops=1)
-> Parallel Index Scan using person_06_pkey on person_06 person_7 (cost=0.28..162.84 rows=2596 width=20) (actual time=0.022..2.883 rows=4413 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 651
-> Index Scan using salary_detail_history_06_pkey on salary_detail_history_06 salary_detail_history_7 (cost=0.43..7.71 rows=4 width=29) (actual time=0.008..0.044 rows=18 loops=4413)
Index Cond: ((emp_id = person_7.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_06_emp_id_skdate_idx on salary_history_ina_06 salary_history_7 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.019..0.033 rows=1 loops=79434)
Index Cond: (emp_id = salary_detail_history_7.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_7.pay_month = pay_month))
Rows Removed by Filter: 17
-> Nested Loop (cost=1.13..29609.06 rows=9479 width=1137) (actual time=0.089..2993.249 rows=80262 loops=1)
Join Filter: (person_13.emp_id = salary_history_13.emp_id)
-> Nested Loop (cost=0.71..20459.52 rows=9488 width=49) (actual time=0.054..214.242 rows=80262 loops=1)
-> Parallel Index Scan using person_12_pkey on person_12 person_13 (cost=0.28..162.63 rows=2623 width=20) (actual time=0.021..2.901 rows=4459 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 588
-> Index Scan using salary_detail_history_12_pkey on salary_detail_history_12 salary_detail_history_13 (cost=0.43..7.70 rows=4 width=29) (actual time=0.008..0.044 rows=18 loops=4459)
Index Cond: ((emp_id = person_13.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_12_emp_id_skdate_idx on salary_history_ina_12 salary_history_13 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.019..0.033 rows=1 loops=80262)
Index Cond: (emp_id = salary_detail_history_13.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_13.pay_month = pay_month))
Rows Removed by Filter: 17
-> Nested Loop (cost=1.13..29557.42 rows=9336 width=1137) (actual time=0.092..2985.341 rows=80352 loops=1)
Join Filter: (person_6.emp_id = salary_history_6.emp_id)
-> Nested Loop (cost=0.71..20545.42 rows=9342 width=49) (actual time=0.056..214.477 rows=80352 loops=1)
-> Parallel Index Scan using person_05_pkey on person_05 person_6 (cost=0.28..162.76 rows=2626 width=20) (actual time=0.023..2.734 rows=4464 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 594
-> Index Scan using salary_detail_history_05_pkey on salary_detail_history_05 salary_detail_history_6 (cost=0.43..7.72 rows=4 width=29) (actual time=0.008..0.044 rows=18 loops=4464)
Index Cond: ((emp_id = person_6.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_05_emp_id_skdate_idx on salary_history_ina_05 salary_history_6 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.019..0.033 rows=1 loops=80352)
Index Cond: (emp_id = salary_detail_history_6.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_6.pay_month = pay_month))
Rows Removed by Filter: 17
-> Nested Loop (cost=1.13..29516.35 rows=9985 width=1137) (actual time=0.116..726.938 rows=78300 loops=1)
Join Filter: (person_10.emp_id = salary_detail_history_10.emp_id)
-> Nested Loop (cost=0.70..3966.20 rows=46059 width=1108) (actual time=0.062..304.848 rows=78300 loops=1)
-> Parallel Index Scan using person_09_pkey on person_09 person_10 (cost=0.28..160.61 rows=2559 width=20) (actual time=0.028..13.132 rows=4350 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 615
-> Index Scan using salary_history_ina_09_emp_id_skdate_idx on salary_history_ina_09 salary_history_10 (cost=0.42..1.31 rows=18 width=1088) (actual time=0.007..0.050 rows=18 loops=4350)
Index Cond: (emp_id = person_10.emp_id)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_09_pkey on salary_detail_history_09 salary_detail_history_10 (cost=0.43..0.54 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=78300)
Index Cond: ((emp_id = salary_history_10.emp_id) AND (pay_month = salary_history_10.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=1.13..29454.23 rows=9857 width=1137) (actual time=0.093..2925.262 rows=78588 loops=1)
Join Filter: (person_14.emp_id = salary_history_14.emp_id)
-> Nested Loop (cost=0.71..19972.49 rows=9856 width=49) (actual time=0.059..210.888 rows=78588 loops=1)
-> Parallel Index Scan using person_13_pkey on person_13 person_14 (cost=0.28..160.49 rows=2568 width=20) (actual time=0.021..2.701 rows=4366 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 589
-> Index Scan using salary_detail_history_13_pkey on salary_detail_history_13 salary_detail_history_14 (cost=0.43..7.67 rows=4 width=29) (actual time=0.008..0.044 rows=18 loops=4366)
Index Cond: ((emp_id = person_14.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_13_emp_id_skdate_idx on salary_history_ina_13 salary_history_14 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.019..0.033 rows=1 loops=78588)
Index Cond: (emp_id = salary_detail_history_14.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_14.pay_month = pay_month))
Rows Removed by Filter: 17
-> Nested Loop (cost=1.13..29433.30 rows=9340 width=1137) (actual time=0.088..3004.112 rows=80082 loops=1)
Join Filter: (person_4.emp_id = salary_history_4.emp_id)
-> Nested Loop (cost=0.71..20428.78 rows=9339 width=49) (actual time=0.055..214.669 rows=80082 loops=1)
-> Parallel Index Scan using person_03_pkey on person_03 person_4 (cost=0.28..162.49 rows=2617 width=20) (actual time=0.022..2.820 rows=4449 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 587
-> Index Scan using salary_detail_history_03_pkey on salary_detail_history_03 salary_detail_history_4 (cost=0.43..7.70 rows=4 width=29) (actual time=0.008..0.044 rows=18 loops=4449)
Index Cond: ((emp_id = person_4.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_03_emp_id_skdate_idx on salary_history_ina_03 salary_history_4 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.019..0.033 rows=1 loops=80082)
Index Cond: (emp_id = salary_detail_history_4.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_4.pay_month = pay_month))
Rows Removed by Filter: 17
-> Nested Loop (cost=1.13..29335.92 rows=9518 width=1137) (actual time=0.079..3028.211 rows=79200 loops=1)
Join Filter: (person_18.emp_id = salary_history_18.emp_id)
-> Nested Loop (cost=0.71..20170.08 rows=9516 width=49) (actual time=0.050..219.080 rows=79200 loops=1)
-> Parallel Index Scan using person_17_pkey on person_17 person_18 (cost=0.28..161.07 rows=2588 width=20) (actual time=0.022..2.823 rows=4400 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 602
-> Index Scan using salary_detail_history_17_pkey on salary_detail_history_17 salary_detail_history_18 (cost=0.43..7.69 rows=4 width=29) (actual time=0.008..0.045 rows=18 loops=4400)
Index Cond: ((emp_id = person_18.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_17_emp_id_skdate_idx on salary_history_ina_17 salary_history_18 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.020..0.034 rows=1 loops=79200)
Index Cond: (emp_id = salary_detail_history_18.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_18.pay_month = pay_month))
Rows Removed by Filter: 17
-> Nested Loop (cost=1.13..29329.41 rows=9508 width=1137) (actual time=0.091..2943.435 rows=79272 loops=1)
Join Filter: (person_8.emp_id = salary_history_8.emp_id)
-> Nested Loop (cost=0.71..20176.08 rows=9510 width=49) (actual time=0.055..211.914 rows=79272 loops=1)
-> Parallel Index Scan using person_07_pkey on person_07 person_8 (cost=0.28..160.84 rows=2591 width=20) (actual time=0.022..2.764 rows=4404 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 579
-> Index Scan using salary_detail_history_07_pkey on salary_detail_history_07 salary_detail_history_8 (cost=0.43..7.68 rows=4 width=29) (actual time=0.008..0.044 rows=18 loops=4404)
Index Cond: ((emp_id = person_8.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_07_emp_id_skdate_idx on salary_history_ina_07 salary_history_8 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.019..0.033 rows=1 loops=79272)
Index Cond: (emp_id = salary_detail_history_8.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_8.pay_month = pay_month))
Rows Removed by Filter: 17
-> Nested Loop (cost=1.13..29324.81 rows=9902 width=1137) (actual time=0.115..747.240 rows=77868 loops=1)
Join Filter: (person_3.emp_id = salary_detail_history_3.emp_id)
-> Nested Loop (cost=0.70..3939.48 rows=45805 width=1108) (actual time=0.061..310.584 rows=77868 loops=1)
-> Parallel Index Scan using person_02_pkey on person_02 person_3 (cost=0.28..160.27 rows=2545 width=20) (actual time=0.028..10.589 rows=4326 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 611
-> Index Scan using salary_history_ina_02_emp_id_skdate_idx on salary_history_ina_02 salary_history_3 (cost=0.42..1.30 rows=18 width=1088) (actual time=0.007..0.052 rows=18 loops=4326)
Index Cond: (emp_id = person_3.emp_id)
Filter: (a_kei > '100000'::numeric)
-> Index Scan using salary_detail_history_02_pkey on salary_detail_history_02 salary_detail_history_3 (cost=0.43..0.54 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=77868)
Index Cond: ((emp_id = salary_history_3.emp_id) AND (pay_month = salary_history_3.pay_month) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Nested Loop (cost=1.13..29287.05 rows=9379 width=1137) (actual time=0.086..1030.077 rows=26508 loops=3)
Join Filter: (person_2.emp_id = salary_history_2.emp_id)
-> Nested Loop (cost=0.71..20249.76 rows=9382 width=49) (actual time=0.056..74.487 rows=26508 loops=3)
-> Parallel Index Scan using person_01_pkey on person_01 person_2 (cost=0.28..161.02 rows=2599 width=20) (actual time=0.025..0.979 rows=1473 loops=3)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 193
-> Index Scan using salary_detail_history_01_pkey on salary_detail_history_01 salary_detail_history_2 (cost=0.43..7.69 rows=4 width=29) (actual time=0.008..0.046 rows=18 loops=4418)
Index Cond: ((emp_id = person_2.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_01_emp_id_skdate_idx on salary_history_ina_01 salary_history_2 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.020..0.034 rows=1 loops=79524)
Index Cond: (emp_id = salary_detail_history_2.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_2.pay_month = pay_month))
Rows Removed by Filter: 17
-> Nested Loop (cost=1.13..29069.93 rows=9085 width=1137) (actual time=0.085..1516.009 rows=39897 loops=2)
Join Filter: (person_1.emp_id = salary_history_1.emp_id)
-> Nested Loop (cost=0.71..20308.21 rows=9088 width=49) (actual time=0.054..109.765 rows=39897 loops=2)
-> Parallel Index Scan using person_00_pkey on person_00 person_1 (cost=0.28..162.59 rows=2608 width=20) (actual time=0.025..1.376 rows=2216 loops=2)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 306
-> Index Scan using salary_detail_history_00_pkey on salary_detail_history_00 salary_detail_history_1 (cost=0.43..7.69 rows=3 width=29) (actual time=0.008..0.045 rows=18 loops=4433)
Index Cond: ((emp_id = person_1.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_00_emp_id_skdate_idx on salary_history_ina_00 salary_history_1 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.019..0.033 rows=1 loops=79794)
Index Cond: (emp_id = salary_detail_history_1.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_1.pay_month = pay_month))
Rows Removed by Filter: 17
-> Nested Loop (cost=1.13..28996.51 rows=9136 width=1137) (actual time=0.085..2894.333 rows=79380 loops=1)
Join Filter: (person_9.emp_id = salary_history_9.emp_id)
-> Nested Loop (cost=0.71..20194.09 rows=9136 width=49) (actual time=0.054..211.399 rows=79380 loops=1)
-> Parallel Index Scan using person_08_pkey on person_08 person_9 (cost=0.28..161.22 rows=2594 width=20) (actual time=0.020..2.661 rows=4410 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 604
-> Index Scan using salary_detail_history_08_pkey on salary_detail_history_08 salary_detail_history_9 (cost=0.43..7.68 rows=4 width=29) (actual time=0.008..0.044 rows=18 loops=4410)
Index Cond: ((emp_id = person_9.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_08_emp_id_skdate_idx on salary_history_ina_08 salary_history_9 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.019..0.032 rows=1 loops=79380)
Index Cond: (emp_id = salary_detail_history_9.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_9.pay_month = pay_month))
Rows Removed by Filter: 17
-> Nested Loop (cost=1.13..28978.02 rows=9701 width=1137) (actual time=0.088..2877.684 rows=77544 loops=1)
Join Filter: (person_11.emp_id = salary_history_11.emp_id)
-> Nested Loop (cost=0.71..19658.94 rows=9701 width=49) (actual time=0.054..206.990 rows=77544 loops=1)
-> Parallel Index Scan using person_10_pkey on person_10 person_11 (cost=0.28..159.81 rows=2534 width=20) (actual time=0.019..2.688 rows=4308 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 592
-> Index Scan using salary_detail_history_10_pkey on salary_detail_history_10 salary_detail_history_11 (cost=0.43..7.65 rows=4 width=29) (actual time=0.008..0.044 rows=18 loops=4308)
Index Cond: ((emp_id = person_11.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_10_emp_id_skdate_idx on salary_history_ina_10 salary_history_11 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.019..0.033 rows=1 loops=77544)
Index Cond: (emp_id = salary_detail_history_11.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_11.pay_month = pay_month))
Rows Removed by Filter: 17
-> Nested Loop (cost=1.13..28829.53 rows=9311 width=1137) (actual time=0.077..2838.340 rows=78336 loops=1)
Join Filter: (person_5.emp_id = salary_history_5.emp_id)
-> Nested Loop (cost=0.71..19874.70 rows=9312 width=49) (actual time=0.044..205.172 rows=78336 loops=1)
-> Parallel Index Scan using person_04_pkey on person_04 person_5 (cost=0.28..160.37 rows=2560 width=20) (actual time=0.020..2.590 rows=4352 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 593
-> Index Scan using salary_detail_history_04_pkey on salary_detail_history_04 salary_detail_history_5 (cost=0.43..7.66 rows=4 width=29) (actual time=0.008..0.043 rows=18 loops=4352)
Index Cond: ((emp_id = person_5.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_04_emp_id_skdate_idx on salary_history_ina_04 salary_history_5 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.019..0.032 rows=1 loops=78336)
Index Cond: (emp_id = salary_detail_history_5.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_5.pay_month = pay_month))
Rows Removed by Filter: 17
-> Nested Loop (cost=1.13..28746.48 rows=9214 width=1137) (actual time=0.088..2849.530 rows=78156 loops=1)
Join Filter: (person_20.emp_id = salary_history_20.emp_id)
-> Nested Loop (cost=0.71..19879.90 rows=9215 width=49) (actual time=0.054..205.620 rows=78156 loops=1)
-> Parallel Index Scan using person_19_pkey on person_19 person_20 (cost=0.28..160.70 rows=2554 width=20) (actual time=0.020..2.686 rows=4342 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 630
-> Index Scan using salary_detail_history_19_pkey on salary_detail_history_19 salary_detail_history_20 (cost=0.43..7.68 rows=4 width=29) (actual time=0.007..0.043 rows=18 loops=4342)
Index Cond: ((emp_id = person_20.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_19_emp_id_skdate_idx on salary_history_ina_19 salary_history_20 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.019..0.032 rows=1 loops=78156)
Index Cond: (emp_id = salary_detail_history_20.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_20.pay_month = pay_month))
Rows Removed by Filter: 17
-> Nested Loop (cost=1.13..28525.55 rows=9467 width=1137) (actual time=0.084..2820.481 rows=76878 loops=1)
Join Filter: (person_16.emp_id = salary_history_16.emp_id)
-> Nested Loop (cost=0.71..19443.38 rows=9469 width=49) (actual time=0.051..204.586 rows=76878 loops=1)
-> Parallel Index Scan using person_15_pkey on person_15 person_16 (cost=0.28..158.18 rows=2512 width=20) (actual time=0.019..2.696 rows=4271 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 578
-> Index Scan using salary_detail_history_15_pkey on salary_detail_history_15 salary_detail_history_16 (cost=0.43..7.64 rows=4 width=29) (actual time=0.008..0.044 rows=18 loops=4271)
Index Cond: ((emp_id = person_16.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_15_emp_id_skdate_idx on salary_history_ina_15 salary_history_16 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.019..0.032 rows=1 loops=76878)
Index Cond: (emp_id = salary_detail_history_16.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_16.pay_month = pay_month))
Rows Removed by Filter: 17
-> Nested Loop (cost=1.13..28363.79 rows=8846 width=1137) (actual time=0.094..2860.939 rows=78354 loops=1)
Join Filter: (person_19.emp_id = salary_history_19.emp_id)
-> Nested Loop (cost=0.71..19852.85 rows=8851 width=49) (actual time=0.065..208.185 rows=78354 loops=1)
-> Parallel Index Scan using person_18_pkey on person_18 person_19 (cost=0.28..160.39 rows=2561 width=20) (actual time=0.034..2.726 rows=4353 loops=1)
Filter: (working_years > '5'::numeric)
Rows Removed by Filter: 594
-> Index Scan using salary_detail_history_18_pkey on salary_detail_history_18 salary_detail_history_19 (cost=0.43..7.66 rows=3 width=29) (actual time=0.008..0.044 rows=18 loops=4353)
Index Cond: ((emp_id = person_19.emp_id) AND (item_id = 111))
Filter: (amount > '10000'::numeric)
-> Index Scan using salary_history_ina_18_emp_id_skdate_idx on salary_history_ina_18 salary_history_19 (cost=0.42..0.95 rows=1 width=1088) (actual time=0.019..0.032 rows=1 loops=78354)
Index Cond: (emp_id = salary_detail_history_19.emp_id)
Filter: ((a_kei > '100000'::numeric) AND (salary_detail_history_19.pay_month = pay_month))
Rows Removed by Filter: 17
Planning Time: 21.774 ms
Execution Time: 46578.789 ms
SQL①と同様に、パーティションを作成したどちらも早くなっていることがわかります。
ただし、今回のSQLは月の絞り込みがないため、パーティションワイズオプションがOFFの時は
月毎パーティションを作成していても、パーティション無しの場合と変化がありません。
パーティションワイズオプションがONの場合は、SQL①と同様に各パーティション同士が結合し、
20分の1のサイズのテーブルの絞り込み・結合を行っているため、結果的に早くなっています。
月毎のパーティションも給与の2テーブルは同一の分割方法のため、結合時にパーティションワイズが効いていて、残りの社員1テーブルに対してはパーティションワイズは効いていませんが、結果的に1番早くなりました。
まとめ
今回は3テーブルのうち、2テーブルが親子関係のある同一パーティションのため、月毎パーティションが常に早くなりましたが、テーブル数が増え、各テーブルのパーティションがバラバラだった場合は、また結果が変わってくると思います。
いずれにせよパーティションを作成することで、実行SQLを変えずにパフォーマンス改善できる手段として有効であることがわかりました。パーティションワイズオプションはCPUやメモリに影響があるため、SQL単体だけでなく、サーバー負荷もみながら検証を進めていきます。
関連記事
enable_partitionwise_join
パーティショニングにおける性能向上のしくみ
【PostgreSQL】パフォーマンスチューニング(できるようになるための知識)