explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Cc5

Settings
# exclusive inclusive rows x rows loops node
1. 5.624 697.467 ↓ 1.0 7,962 1

Hash Join (cost=31,254.59..67,192.8 rows=7,907 width=810) (actual time=477.96..697.467 rows=7,962 loops=1)

  • Buffers: shared hit=39853, temp read=1247 written=2070
2.          

CTE aa

3. 53.025 537.145 ↓ 1.0 47,962 1

Merge Join (cost=26,594.11..27,837.89 rows=47,122 width=135) (actual time=444.452..537.145 rows=47,962 loops=1)

  • Buffers: shared hit=20358, temp read=1013 written=1013
4. 198.063 243.857 ↓ 1.0 47,842 1

Sort (cost=14,284.59..14,402.39 rows=47,122 width=89) (actual time=228.884..243.857 rows=47,842 loops=1)

  • Sort Key: ea.pos_control_num, ea.per_id, ea.emp_assgn_dt, ea.pos_assign_start_date
  • Sort Method: external merge Disk: 4816kB
  • Buffers: shared hit=10933, temp read=603 written=603
5. 45.794 45.794 ↓ 1.0 47,842 1

Seq Scan on employee_assignment ea (cost=0..8,208.45 rows=47,122 width=89) (actual time=0.103..45.794 rows=47,842 loops=1)

  • Filter: (ea.emp_assign_is_sub_assign <> 'Y'::bpchar)
  • Buffers: shared hit=10924
6. 5.961 240.263 ↑ 1.0 43,689 1

Materialize (cost=12,309.52..12,538.57 rows=45,809 width=73) (actual time=215.556..240.263 rows=43,689 loops=1)

  • Buffers: shared hit=9425, temp read=410 written=410
7. 198.439 234.302 ↑ 1.0 43,689 1

Sort (cost=12,309.52..12,424.04 rows=45,809 width=73) (actual time=215.551..234.302 rows=43,689 loops=1)

  • Sort Key: edwh.emp_dist_wk_pos_control_num, edwh.per_id, edwh.emp_assgn_dt, edwh.pos_assign_start_date
  • Sort Method: external merge Disk: 3272kB
  • Buffers: shared hit=9425, temp read=410 written=410
8. 35.863 35.863 ↓ 1.0 46,357 1

Seq Scan on employee_district_work_history edwh (cost=0..6,726.14 rows=45,809 width=73) (actual time=0.11..35.863 rows=46,357 loops=1)

  • Filter: ((edwh.emp_dist_wk_type_code)::text = ANY ('{TERMINATED,"TRANSFER OUT",RESIGN,RETIRE,DEATH,TERMINATION,"INITIALIZATION END","END ASSIGNMENT"}'::text[]))
  • Buffers: shared hit=9425
9. 6.701 690.623 ↓ 1.0 7,962 1

Hash Join (cost=3,306.72..39,116.44 rows=7,907 width=729) (actual time=476.713..690.623 rows=7,962 loops=1)

  • Buffers: shared hit=39786, temp read=1247 written=2070
10. 0.518 675.928 ↑ 1.2 4,280 1

Nested Loop (cost=2,804.85..38,542.68 rows=5,236 width=469) (actual time=468.663..675.928 rows=4,280 loops=1)

  • Buffers: shared hit=39226, temp read=1247 written=2070
11. 8.219 636.890 ↑ 1.2 4,280 1

Hash Join (cost=2,804.43..5,742.49 rows=5,236 width=415) (actual time=468.635..636.89 rows=4,280 loops=1)

  • Buffers: shared hit=22074, temp read=1247 written=2070
12. 604.849 604.849 ↑ 1.2 4,280 1

CTE Scan on aa aa (cost=0..1,649.27 rows=5,236 width=394) (actual time=444.498..604.849 rows=4,280 loops=1)

  • Filter: ((aa.end_date >= ('now'::cstring)::date) AND (aa.emp_assgn_dt <= ('now'::cstring)::date))
  • Buffers: shared hit=20358, temp read=1013 written=1838
13. 13.219 23.822 ↑ 1.1 53,818 1

Hash (cost=1,511.57..1,511.57 rows=56,657 width=34) (actual time=23.822..23.822 rows=53,818 loops=1)

  • Buffers: shared hit=1713, temp written=198
14. 10.603 10.603 ↑ 1.1 53,818 1

Seq Scan on position_assignment pa (cost=0..1,511.57 rows=56,657 width=34) (actual time=0.092..10.603 rows=53,818 loops=1)

  • Buffers: shared hit=1713
15. 38.520 38.520 ↑ 1.0 1 4,280

Index Scan using idbi_121010_002_person_002 on person per (cost=0.42..6.25 rows=1 width=54) (actual time=0.008..0.009 rows=1 loops=4,280)

  • Index Cond: ((aa.per_id)::text = (per.per_id)::text)
  • Buffers: shared hit=17152
16. 4.389 7.994 ↓ 1.0 7,923 1

Hash (cost=403.04..403.04 rows=7,907 width=302) (actual time=7.994..7.994 rows=7,923 loops=1)

  • Buffers: shared hit=560
17. 3.605 3.605 ↓ 1.0 7,923 1

Seq Scan on position_inventory pi (cost=0..403.04 rows=7,907 width=302) (actual time=0.075..3.605 rows=7,923 loops=1)

  • Filter: ((pi.pos_inv_status)::text <> 'Pending Approval'::text)
  • Buffers: shared hit=560
18. 0.243 1.220 ↓ 1.0 724 1

Hash (cost=100.95..100.95 rows=722 width=91) (actual time=1.22..1.22 rows=724 loops=1)

  • Buffers: shared hit=64
19. 0.222 0.977 ↓ 1.0 724 1

Hash Join (cost=3.7..100.95 rows=722 width=91) (actual time=0.079..0.977 rows=724 loops=1)

  • Buffers: shared hit=64
20. 0.202 0.745 ↓ 1.0 724 1

Hash Join (cost=2.41..90.4 rows=722 width=94) (actual time=0.053..0.745 rows=724 loops=1)

  • Buffers: shared hit=63
21. 0.389 0.535 ↓ 1.0 724 1

Hash Join (cost=1.18..79.33 rows=722 width=95) (actual time=0.033..0.535 rows=724 loops=1)

  • Buffers: shared hit=62
22. 0.138 0.138 ↓ 1.0 724 1

Seq Scan on role role (cost=0..68.22 rows=722 width=98) (actual time=0.007..0.138 rows=724 loops=1)

  • Buffers: shared hit=61
23. 0.007 0.008 ↓ 1.1 9 1

Hash (cost=1.08..1.08 rows=8 width=16) (actual time=0.008..0.008 rows=9 loops=1)

  • Buffers: shared hit=1
24. 0.001 0.001 ↓ 1.1 9 1

Seq Scan on rfs_role_type rrt (cost=0..1.08 rows=8 width=16) (actual time=0.001..0.001 rows=9 loops=1)

  • Buffers: shared hit=1
25. 0.005 0.008 ↑ 1.0 10 1

Hash (cost=1.1..1.1 rows=10 width=20) (actual time=0.008..0.008 rows=10 loops=1)

  • Buffers: shared hit=1
26. 0.003 0.003 ↑ 1.0 10 1

Seq Scan on rfds_role_category rrc (cost=0..1.1 rows=10 width=20) (actual time=0.001..0.003 rows=10 loops=1)

  • Buffers: shared hit=1
27. 0.006 0.010 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=22) (actual time=0.01..0.01 rows=13 loops=1)

  • Buffers: shared hit=1
28. 0.004 0.004 ↑ 1.0 13 1

Seq Scan on rfd_role_sub_category rrsc (cost=0..1.13 rows=13 width=22) (actual time=0.003..0.004 rows=13 loops=1)

  • Buffers: shared hit=1