explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OFy0 : Optimization for: plan #zTCx

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,411,142.40..3,008,512.92 rows=975,512 width=389) (actual rows= loops=)

  • Hash Cond: ((pm.persorg_master_id = pa.persorg_master_id) AND ((SubPlan 1) = pa.persorg_address_id))
2. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,057,604.85..2,092,221.48 rows=975,512 width=345) (actual rows= loops=)

  • Hash Cond: ((pm.persorg_master_id = pp.persorg_master_id) AND ((SubPlan 3) = pp.persorg_phone_id))
3. 0.000 0.000 ↓ 0.0

Hash Join (cost=865,589.57..1,374,339.61 rows=975,512 width=321) (actual rows= loops=)

  • Hash Cond: (ec.employer_master_id = em.employer_master_id)
4. 0.000 0.000 ↓ 0.0

Hash Join (cost=583,944.72..1,008,762.04 rows=3,217,762 width=48) (actual rows= loops=)

  • Hash Cond: (pm.persorg_master_id = ec.persorg_master_id)
5. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=387,230.67..684,222.22 rows=6,143,147 width=40) (actual rows= loops=)

  • Hash Cond: ((pe.persorg_master_id = pm.persorg_master_id) AND (pe.persorg_email_id = (SubPlan 2)))
6. 0.000 0.000 ↓ 0.0

Seq Scan on persorg_email pe (cost=0.00..23,434.64 rows=546,464 width=47) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Hash (cost=265,087.47..265,087.47 rows=6,143,147 width=9) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Seq Scan on persorg_master pm (cost=0.00..265,087.47 rows=6,143,147 width=9) (actual rows= loops=)

9.          

SubPlan (for Hash Right Join)

10. 0.000 0.000 ↓ 0.0

Limit (cost=8.45..8.46 rows=1 width=8) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Sort (cost=8.45..8.46 rows=1 width=8) (actual rows= loops=)

  • Sort Key: pe2.persorg_email_id DESC
12. 0.000 0.000 ↓ 0.0

Index Scan using persorgemail_persorg_ix on persorg_email pe2 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (persorg_master_id = pm.persorg_master_id)
13. 0.000 0.000 ↓ 0.0

Hash (cost=140,780.02..140,780.02 rows=3,217,762 width=16) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Seq Scan on employer_contact ec (cost=0.00..140,780.02 rows=3,217,762 width=16) (actual rows= loops=)

  • Filter: ((employer_contact_kind_cd)::text = 'PRIMARY'::text)
15. 0.000 0.000 ↓ 0.0

Hash (cost=274,322.99..274,322.99 rows=141,989 width=289) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Merge Join (cost=192,243.23..274,322.99 rows=141,989 width=289) (actual rows= loops=)

  • Merge Cond: (newhire_trans.employer_master_id = em.employer_master_id)
17. 0.000 0.000 ↓ 0.0

Finalize GroupAggregate (cost=192,242.80..238,036.58 rows=141,989 width=24) (actual rows= loops=)

  • Group Key: newhire_trans.employer_master_id, (date_trunc('month'::text, ((newhire_trans.transmittal_ts)::date)::timestamp with time zone))
18. 0.000 0.000 ↓ 0.0

Gather Merge (cost=192,242.80..233,421.94 rows=283,978 width=24) (actual rows= loops=)

  • Workers Planned: 2
19. 0.000 0.000 ↓ 0.0

Partial GroupAggregate (cost=191,242.78..199,643.81 rows=141,989 width=24) (actual rows= loops=)

  • Group Key: newhire_trans.employer_master_id, (date_trunc('month'::text, ((newhire_trans.transmittal_ts)::date)::timestamp with time zone))
20. 0.000 0.000 ↓ 0.0

Sort (cost=191,242.78..192,721.84 rows=591,622 width=16) (actual rows= loops=)

  • Sort Key: newhire_trans.employer_master_id, (date_trunc('month'::text, ((newhire_trans.transmittal_ts)::date)::timestamp with time zone))
21. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on newhire_trans (cost=0.00..124,411.54 rows=591,622 width=16) (actual rows= loops=)

  • Filter: (((newhire_report_type_cd)::text = 'NH'::text) AND ((newhire_status_cd)::text = 'COMPLETED'::text) AND (deleted_flag = 'N'::bpchar))
22. 0.000 0.000 ↓ 0.0

Index Scan using employermast_pk on employer_master em (cost=0.42..31,920.76 rows=468,356 width=265) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash (cost=123,641.71..123,641.71 rows=2,997,171 width=40) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on persorg_phone pp (cost=0.00..123,641.71 rows=2,997,171 width=40) (actual rows= loops=)

25.          

SubPlan (for Hash Left Join)

26. 0.000 0.000 ↓ 0.0

Limit (cost=8.47..8.48 rows=1 width=8) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Sort (cost=8.47..8.48 rows=2 width=8) (actual rows= loops=)

  • Sort Key: pp2.persorg_phone_id DESC
28. 0.000 0.000 ↓ 0.0

Index Scan using persorgphone_persorg_ix on persorg_phone pp2 (cost=0.43..8.46 rows=2 width=8) (actual rows= loops=)

  • Index Cond: (persorg_master_id = pm.persorg_master_id)
29. 0.000 0.000 ↓ 0.0

Hash (cost=227,689.62..227,689.62 rows=4,710,062 width=69) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on persorg_address pa (cost=0.00..227,689.62 rows=4,710,062 width=69) (actual rows= loops=)

31.          

SubPlan (for Hash Left Join)

32. 0.000 0.000 ↓ 0.0

Limit (cost=8.46..8.46 rows=1 width=8) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Sort (cost=8.46..8.46 rows=1 width=8) (actual rows= loops=)

  • Sort Key: pa2.persorg_address_id DESC
34. 0.000 0.000 ↓ 0.0

Index Scan using persorgaddress_persorg_ix on persorg_address pa2 (cost=0.43..8.45 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (persorg_master_id = pm.persorg_master_id)