explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nHSu

Settings
# exclusive inclusive rows x rows loops node
1. 0.085 69,973.212 ↓ 1.0 126 1

Group (cost=14,360,884.82..14,360,886.03 rows=121 width=19) (actual time=69,973.097..69,973.212 rows=126 loops=1)

  • Group Key: dim.lb_delivery
2. 1.323 69,973.127 ↓ 1.5 374 1

Sort (cost=14,360,884.82..14,360,885.42 rows=242 width=19) (actual time=69,973.096..69,973.127 rows=374 loops=1)

  • Sort Key: dim.lb_delivery
  • Sort Method: quicksort Memory: 50kB
3. 6.399 69,971.804 ↓ 1.5 374 1

Gather (cost=14,360,849.83..14,360,875.24 rows=242 width=19) (actual time=69,971.370..69,971.804 rows=374 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 5,475.358 69,965.405 ↓ 1.0 125 3

Partial HashAggregate (cost=14,359,849.83..14,359,851.04 rows=121 width=19) (actual time=69,965.387..69,965.405 rows=125 loops=3)

  • Group Key: dim.lb_delivery
5. 5,612.163 64,490.047 ↑ 20.3 23,295,212 3

Merge Join (cost=588,117.12..13,179,506.31 rows=472,137,408 width=19) (actual time=2,588.928..64,490.047 rows=23,295,212 loops=3)

  • Merge Cond: (dim.dr_sk = dim_1.dr_sk)
6. 32,429.194 50,971.366 ↑ 1.3 23,002,463 3

Nested Loop (cost=0.70..5,171,355.14 rows=29,357,990 width=27) (actual time=0.179..50,971.366 rows=23,002,463 loops=3)

7. 18,542.170 18,542.170 ↑ 1.2 13,283,086 3

Parallel Index Scan using fa_drsk on fact_alarm dim (cost=0.56..1,986,919.65 rows=16,542,509 width=34) (actual time=0.125..18,542.170 rows=13,283,086 loops=3)

  • Filter: (lb_spip IS NOT NULL)
  • Rows Removed by Filter: 1072577
8. 0.002 0.002 ↑ 1.0 2 39,849,259

Index Only Scan using drrd_iddisp on dimn_ref_region_disp (cost=0.14..0.17 rows=2 width=7) (actual time=0.002..0.002 rows=2 loops=39,849,259)

  • Index Cond: (id_disp = (dim.id_disp)::text)
  • Heap Fetches: 0
9. 1,399.330 7,906.518 ↓ 11.2 24,909,077 3

Materialize (cost=588,116.42..857,042.24 rows=2,229,925 width=12) (actual time=2,588.621..7,906.518 rows=24,909,077 loops=3)

10. 759.626 6,507.188 ↑ 1.2 1,866,088 3

Merge Join (cost=588,116.42..851,467.43 rows=2,229,925 width=12) (actual time=2,588.616..6,507.188 rows=1,866,088 loops=3)

  • Merge Cond: (frsc.dr_sk = dim_1.dr_sk)
11. 300.985 300.985 ↑ 1.0 1,866,088 3

Index Only Scan using frec_drsk on fact_rec_status_chg frsc (cost=0.43..43,369.64 rows=1,866,881 width=8) (actual time=0.049..300.985 rows=1,866,088 loops=3)

  • Heap Fetches: 0
12. 282.383 5,446.577 ↓ 1.0 2,007,992 3

Materialize (cost=588,115.99..774,767.65 rows=1,914,376 width=4) (actual time=2,588.563..5,446.577 rows=2,007,992 loops=3)

13. 1,726.843 5,164.194 ↑ 1.0 1,913,374 3

Unique (cost=588,115.99..750,837.95 rows=1,914,376 width=366) (actual time=2,588.559..5,164.194 rows=1,913,374 loops=3)

14. 2,839.088 3,437.351 ↑ 1.0 1,913,374 3

Sort (cost=588,115.99..592,901.93 rows=1,914,376 width=366) (actual time=2,588.557..3,437.351 rows=1,913,374 loops=3)

  • Sort Key: dim_1.dr_sk, dim_1.scd_start_date, dim_1.scd_end_date, dim_1.scd_current, dim_1.id_disp, dim_1.lb_disp, dim_1.id_spip, dim_1.lb_spip, dim_1.id_delivery, dim_1.lb_delivery, dim_1.id_record, dim_1.dt_starting_measure, dim_1.dt_ending_measure, dim_1.id_offender, dim_1.cd_assignment_type, dim_1.cd_main_measure, dim_1.id_nationality, dim_1.lb_nationality, dim_1.cd_sex, dim_1.cd_main_zip_code, dim_1.lb_main_town, dim_1.cd_appi_number, dim_1.id_prisoner_initial, dim_1.id_prison, dim_1.lb_prison, dim_1.id_prisoner_current, dim_1.id_assignment_authority, dim_1.lb_assignment_authority, dim_1.dt_assignment_decision, dim_1.bl_biometrics_available, dim_1.lb_management_profile, dim_1.cd_current_status, dim_1.lb_current_status
  • Sort Method: external merge Disk: 701272kB
15. 598.263 598.263 ↑ 1.0 1,914,376 3

Seq Scan on dimn_record dim_1 (cost=0.00..113,547.76 rows=1,914,376 width=366) (actual time=0.024..598.263 rows=1,914,376 loops=3)