explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A21R

Settings
# exclusive inclusive rows x rows loops node
1. 5.456 904.539 ↓ 1.0 7,962 1

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

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

CTE aa

3. 49.601 746.694 ↓ 1.0 47,962 1

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

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

Sort (cost=14,284.59..14,402.39 rows=47,122 width=89) (actual time=296.335..310.479 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. 49.892 49.892 ↓ 1.0 47,842 1

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

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

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

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

Sort (cost=12,309.52..12,424.04 rows=45,809 width=73) (actual time=365.457..380.871 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. 51.786 51.786 ↓ 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.136..51.786 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.035 897.856 ↓ 1.0 7,962 1

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

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

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

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

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

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

CTE Scan on aa aa (cost=0..1,649.27 rows=5,236 width=394) (actual time=661.869..810.889 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. 17.581 31.983 ↑ 1.1 53,818 1

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

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

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

  • Buffers: shared hit=1713
15. 29.960 29.960 ↑ 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.007..0.007 rows=1 loops=4,280)

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

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

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

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

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

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

  • Buffers: shared hit=64
19. 0.197 0.968 ↓ 1.0 724 1

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

  • Buffers: shared hit=64
20. 0.225 0.762 ↓ 1.0 724 1

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

  • Buffers: shared hit=63
21. 0.397 0.529 ↓ 1.0 724 1

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

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

Seq Scan on role role (cost=0..68.22 rows=722 width=98) (actual time=0.007..0.124 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.006 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.002 0.002 ↑ 1.0 10 1

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

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

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

  • Buffers: shared hit=1
28. 0.005 0.005 ↑ 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.005 rows=13 loops=1)

  • Buffers: shared hit=1
Planning time : 10.239 ms
Execution time : 908.845 ms