QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Left Join (cost=2667903.66..3132513.31 rows=1 width=8) (actual time=200578.974..201810.300 rows=1 loops=1) Buffers: shared hit=1610887 read=847496 I/O Timings: read=46389.959 -> Nested Loop Left Join (cost=2667903.22..3132512.57 rows=1 width=16) (actual time=200578.953..201810.278 rows=1 loops=1) Buffers: shared hit=1610884 read=847496 I/O Timings: read=46389.959 -> Nested Loop Left Join (cost=2667902.80..3132512.12 rows=1 width=16) (actual time=200578.934..201810.257 rows=1 loops=1) Buffers: shared hit=1610881 read=847496 I/O Timings: read=46389.959 -> Nested Loop Left Join (cost=2667902.36..3132511.61 rows=1 width=16) (actual time=200578.908..201810.229 rows=1 loops=1) Buffers: shared hit=1610877 read=847496 I/O Timings: read=46389.959 -> Nested Loop Left Join (cost=2667901.93..3132511.14 rows=1 width=16) (actual time=200578.888..201810.207 rows=1 loops=1) Buffers: shared hit=1610874 read=847496 I/O Timings: read=46389.959 -> Gather (cost=2667901.49..3132510.64 rows=1 width=24) (actual time=200578.847..201810.161 rows=1 loops=1) Workers Planned: 6 Workers Launched: 0 Buffers: shared hit=1610870 read=847496 I/O Timings: read=46389.959 -> Hash Semi Join (cost=2666901.49..3131510.54 rows=1 width=24) (actual time=200577.866..200786.512 rows=1 loops=1) Hash Cond: (gr.TABLE_E_pk = ar1.TABLE_E_pk) Buffers: shared hit=1610870 read=847496 I/O Timings: read=46389.959 -> Parallel Hash Left Join (cost=2666893.24..3122518.80 rows=3422280 width=32) (actual time=115007.084..198452.799 rows=20955994 loops=1) Hash Cond: (gr.TABLE_D_pk = gm.TABLE_D_pk) Buffers: shared hit=1610861 read=847496 I/O Timings: read=46389.959 -> Parallel Hash Join (cost=2519520.86..2960409.74 rows=3422280 width=32) (actual time=108933.021..180685.868 rows=20952150 loops=1) Hash Cond: (gr.TABLE_A_pk = ii.TABLE_A_pk) Buffers: shared hit=1493176 read=847478 I/O Timings: read=46372.819 -> Parallel Hash Join (cost=1732613.92..2146401.28 rows=3423649 width=40) (actual time=71413.550..129338.033 rows=20952150 loops=1) Hash Cond: (mr.TABLE_O_pk = mi.TABLE_O_pk) Buffers: shared hit=875756 read=847181 I/O Timings: read=46316.586 -> Parallel Hash Join (cost=1384407.10..1789205.63 rows=3424314 width=48) (actual time=53970.151..99631.531 rows=20952588 loops=1) Hash Cond: (gr.TABLE_E_pk = ar.TABLE_E_pk) Buffers: shared hit=875734 read=579453 I/O Timings: read=32017.291 -> Parallel Hash Join (cost=850104.36..1245854.83 rows=3446882 width=40) (actual time=40670.584..73979.070 rows=20952588 loops=1) Hash Cond: (ir.TABLE_D_pk = gr.TABLE_D_pk) Buffers: shared hit=431786 read=576682 I/O Timings: read=31532.023 -> Parallel Hash Join (cost=377639.61..764283.22 rows=3469280 width=24) (actual time=17370.113..37655.366 rows=20952588 loops=1) Hash Cond: (mr.TABLE_G_pk = ir.TABLE_G_pk) Buffers: shared hit=431746 read=191851 I/O Timings: read=11334.886 -> Parallel Seq Scan on TABLE_G mr (cost=0.00..377476.70 rows=3492155 width=16) (actual time=8.256..7628.867 rows=20952588 loops=1) Filter: (is_deleted = 'N'::bpchar) Rows Removed by Filter: 137463 Buffers: shared hit=333485 read=56 I/O Timings: read=99.651 -> Parallel Hash (cost=333991.70..333991.70 rows=3491833 width=16) (actual time=17209.235..17209.236 rows=20952588 loops=1) Buckets: 33554432 Batches: 1 Memory Usage: 1245280kB Buffers: shared hit=98261 read=191795 I/O Timings: read=11235.235 -> Parallel Seq Scan on TABLE_F ir (cost=0.00..333991.70 rows=3491833 width=16) (actual time=10.521..8410.712 rows=20952588 loops=1) Filter: (is_deleted = 'N'::bpchar) Rows Removed by Filter: 137463 Buffers: shared hit=98261 read=191795 I/O Timings: read=11235.235 -> Parallel Hash (cost=428812.70..428812.70 rows=3492164 width=24) (actual time=23231.120..23231.121 rows=20952588 loops=1) Buckets: 33554432 Batches: 1 Memory Usage: 1410240kB Buffers: shared hit=40 read=384831 I/O Timings: read=20197.137 -> Parallel Seq Scan on TABLE_D gr (cost=0.00..428812.70 rows=3492164 width=24) (actual time=1.548..13339.643 rows=20952588 loops=1) Filter: (is_deleted = 'N'::bpchar) Rows Removed by Filter: 137463 Buffers: shared hit=40 read=384831 I/O Timings: read=20197.137 -> Parallel Hash (cost=490654.70..490654.70 rows=3491843 width=8) (actual time=13214.241..13214.243 rows=20952588 loops=1) Buckets: 33554432 Batches: 1 Memory Usage: 1081824kB Buffers: shared hit=443948 read=2771 I/O Timings: read=485.268 -> Parallel Seq Scan on TABLE_E ar (cost=0.00..490654.70 rows=3491843 width=8) (actual time=0.016..6391.132 rows=20952588 loops=1) Filter: (is_deleted = 'N'::bpchar) Rows Removed by Filter: 137463 Buffers: shared hit=443948 read=2771 I/O Timings: read=485.268 -> Parallel Hash (cost=307982.32..307982.32 rows=3217960 width=8) (actual time=17420.366..17420.366 rows=19289868 loops=1) Buckets: 33554432 Batches: 1 Memory Usage: 1016768kB Buffers: shared hit=22 read=267728 I/O Timings: read=14299.295 -> Parallel Seq Scan on TABLE_O mi (cost=0.00..307982.32 rows=3217960 width=8) (actual time=7.209..9860.853 rows=19289868 loops=1) Filter: (is_deleted = 'N'::bpchar) Rows Removed by Filter: 3884 Buffers: shared hit=22 read=267728 I/O Timings: read=14299.295 -> Parallel Hash (cost=746729.28..746729.28 rows=3214213 width=16) (actual time=37277.818..37277.821 rows=19289868 loops=1) Buckets: 33554432 Batches: 1 Memory Usage: 1167264kB Buffers: shared hit=617420 read=297 I/O Timings: read=56.233 -> Parallel Hash Join (cost=390397.57..746729.28 rows=3214213 width=16) (actual time=14977.066..30086.210 rows=19289868 loops=1) Hash Cond: (ii.TABLE_A_pk = gi.TABLE_A_pk) Buffers: shared hit=617420 read=297 I/O Timings: read=56.233 -> Parallel Seq Scan on TABLE_C ii (cost=0.00..347892.74 rows=3214846 width=8) (actual time=0.010..5278.795 rows=19289868 loops=1) Filter: (is_deleted = 'N'::bpchar) Rows Removed by Filter: 3884 Buffers: shared hit=307402 read=297 I/O Timings: read=56.233 -> Parallel Hash (cost=350211.74..350211.74 rows=3214866 width=8) (actual time=14791.416..14791.417 rows=19289874 loops=1) Buckets: 33554432 Batches: 1 Memory Usage: 1016768kB Buffers: shared hit=310018 -> Parallel Seq Scan on TABLE_A gi (cost=0.00..350211.74 rows=3214866 width=8) (actual time=0.013..6388.707 rows=19289874 loops=1) Filter: (is_deleted = 'N'::bpchar) Rows Removed by Filter: 3878 Buffers: shared hit=310018 -> Parallel Hash (cost=132712.68..132712.68 rows=1172776 width=8) (actual time=6015.243..6015.244 rows=5863214 loops=1) Buckets: 8388608 Batches: 1 Memory Usage: 294912kB Buffers: shared hit=117685 read=18 I/O Timings: read=17.141 -> Parallel Seq Scan on TABLE_H gm (cost=0.00..132712.68 rows=1172776 width=8) (actual time=0.016..2828.580 rows=5863214 loops=1) Filter: (is_deleted = 'N'::bpchar) Rows Removed by Filter: 139838 Buffers: shared hit=117685 read=18 I/O Timings: read=17.141 -> Hash (cost=8.24..8.24 rows=1 width=16) (actual time=0.062..0.064 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=9 -> Nested Loop (cost=1.00..8.24 rows=1 width=16) (actual time=0.048..0.050 rows=1 loops=1) Buffers: shared hit=9 -> Index Scan using ix_TABLE_N_type_value_case on TABLE_N aa1 (cost=0.56..4.18 rows=1 width=8) (actual time=0.031..0.032 rows=1 loops=1) Index Cond: (((TABLE_N_type)::text = ID_TYPE_X'::text) AND ((TABLE_N_value)::text = 'SOME/UNIQUE/VALUE'::text)) Filter: (is_deleted = 'N'::bpchar) Buffers: shared hit=5 -> Index Scan using ix_TABLE_E_TABLE_E_pk on TABLE_E ar1 (cost=0.44..4.06 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1) Index Cond: (TABLE_E_pk = aa1.TABLE_E_pk) Filter: ((is_deleted = 'N'::bpchar) AND ((app_case_type)::text = '8'::text)) Buffers: shared hit=4 -> Index Scan using ix_TABLE_I_TABLE_D_pk on TABLE_I gy (cost=0.44..0.49 rows=1 width=8) (actual time=0.037..0.038 rows=1 loops=1) Index Cond: (TABLE_D_pk = gr.TABLE_D_pk) Filter: (is_deleted = 'N'::bpchar) Buffers: shared hit=4 -> Index Scan using ix_TABLE_J_TABLE_E_pk on TABLE_J ac (cost=0.43..0.46 rows=1 width=8) (actual time=0.017..0.018 rows=0 loops=1) Index Cond: (TABLE_E_pk = ar.TABLE_E_pk) Filter: (is_deleted = 'N'::bpchar) Buffers: shared hit=3 -> Index Scan using ix_TABLE_N_TABLE_E_pk on TABLE_N aa (cost=0.44..0.49 rows=1 width=8) (actual time=0.023..0.024 rows=1 loops=1) Index Cond: (TABLE_E_pk = ar.TABLE_E_pk) Filter: (is_deleted = 'N'::bpchar) Buffers: shared hit=4 -> Index Scan using ix_TABLE_L_TABLE_E_pk on TABLE_L ap (cost=0.42..0.44 rows=1 width=8) (actual time=0.017..0.017 rows=0 loops=1) Index Cond: (TABLE_E_pk = ar.TABLE_E_pk) Filter: (is_deleted = 'N'::bpchar) Buffers: shared hit=3 -> Index Only Scan using ix_TABLE_M_TABLE_G_pk on TABLE_M fp (cost=0.43..0.64 rows=10 width=8) (actual time=0.018..0.018 rows=0 loops=1) Index Cond: ((TABLE_G_pk = mr.TABLE_G_pk) AND (is_deleted = 'N'::bpchar)) Heap Fetches: 0 Buffers: shared hit=3 Settings: effective_cache_size = '43806080kB', maintenance_io_concurrency = '1', max_parallel_workers_per_gather = '8', random_page_cost = '1.79769', search_path = dbname, "$user", public', work_mem = '16GB' Planning: Buffers: shared hit=405 Planning Time: 178.009 ms Execution Time: 201810.512 ms (156 rows)