explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KvdP : Optimization for: Query for Employer Outreach; plan #HGrE

Settings

Optimization path:

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

Limit (cost=192,264.94..246,099.71 rows=1,000 width=389) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=192,264.94..52,708,736.49 rows=975,512 width=389) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=192,256.03..37,585,962.41 rows=975,512 width=374) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=192,255.60..22,514,980.61 rows=975,512 width=343) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=192,255.17..7,380,630.76 rows=975,512 width=290) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Merge Join (cost=192,254.74..499,045.41 rows=975,512 width=289) (actual rows= loops=)

  • Merge Cond: (em.employer_master_id = ec.employer_master_id)
7. 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)
8. 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))
9. 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
10. 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))
11. 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))
12. 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))
13. 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=)

14. 0.000 0.000 ↓ 0.0

Index Scan using employercontact_employermast_ix on employer_contact ec (cost=0.43..206,707.26 rows=3,217,762 width=16) (actual rows= loops=)

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

Index Only Scan using persorg_pk on persorg_master pm (cost=0.43..7.05 rows=1 width=9) (actual rows= loops=)

  • Index Cond: (persorg_master_id = ec.persorg_master_id)
16. 0.000 0.000 ↓ 0.0

Index Scan using persorgaddress_persorg_ix on persorg_address pa (cost=0.43..15.50 rows=1 width=69) (actual rows= loops=)

  • Index Cond: (persorg_master_id = pm.persorg_master_id)
  • Filter: (persorg_address_id = (SubPlan 1))
17.          

SubPlan (for Index Scan)

18. 0.000 0.000 ↓ 0.0

Aggregate (cost=8.45..8.46 rows=1 width=32) (actual rows= loops=)

19. 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)
20. 0.000 0.000 ↓ 0.0

Index Scan using persorgemail_persorg_ix on persorg_email pe (cost=0.42..15.44 rows=1 width=47) (actual rows= loops=)

  • Index Cond: (persorg_master_id = pm.persorg_master_id)
  • Filter: (persorg_email_id = (SubPlan 2))
21.          

SubPlan (for Index Scan)

22. 0.000 0.000 ↓ 0.0

Aggregate (cost=8.45..8.46 rows=1 width=32) (actual rows= loops=)

23. 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)
24. 0.000 0.000 ↓ 0.0

Index Scan using persorgphone_pk on persorg_phone pp (cost=8.91..15.49 rows=1 width=40) (actual rows= loops=)

  • Index Cond: (persorg_phone_id = (SubPlan 3))
  • Filter: (persorg_master_id = pm.persorg_master_id)
25.          

SubPlan (for Index Scan)

26. 0.000 0.000 ↓ 0.0

Aggregate (cost=8.47..8.48 rows=1 width=32) (actual rows= loops=)

27. 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)