explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hPpJ

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

Limit (cost=28,213.36..28,221.78 rows=100 width=394) (actual rows= loops=)

2.          

CTE ibm_mms_all

3. 0.000 0.000 ↓ 0.0

Finalize HashAggregate (cost=10,749.87..11,226.47 rows=47,660 width=30) (actual rows= loops=)

  • Group Key: mall.org_job_code, mall.org_job_code_key, mall.organization_id
4. 0.000 0.000 ↓ 0.0

Gather (cost=8,724.32..10,392.42 rows=238,300 width=26) (actual rows= loops=)

  • Workers Planned: 5
5. 0.000 0.000 ↓ 0.0

Partial HashAggregate (cost=8,674.32..9,150.92 rows=47,660 width=26) (actual rows= loops=)

  • Group Key: mall.org_job_code, mall.org_job_code_key, mall.organization_id
6. 0.000 0.000 ↓ 0.0

Parallel Index Only Scan using ibm_mms_orgid_jc_jck_not_unknown_idx on merit_match_summaries mall (cost=0.08..8,531.36 rows=95,311 width=26) (actual rows= loops=)

7.          

CTE ibm_mms_2018

8. 0.000 0.000 ↓ 0.0

Gather (cost=71.36..12,536.19 rows=168,304 width=101) (actual rows= loops=)

  • Workers Planned: 3
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=21.36..11,644.67 rows=54,292 width=101) (actual rows= loops=)

  • Hash Cond: ((merit_match_summaries.survey_year = merit_user_aging_factors.benchmark_year) AND (merit_match_summaries.organization_id = merit_user_aging_factors.organization_id) AND ((merit_match_summaries.aging_code)::text =
10. 0.000 0.000 ↓ 0.0

Parallel Index Scan using ibm_mms_survey_year_idx on merit_match_summaries (cost=0.08..11,537.88 rows=54,292 width=76) (actual rows= loops=)

  • Index Cond: (survey_year = 2018)
  • Filter: ((org_job_code)::text !~~ 'unknown%'::text)
11. 0.000 0.000 ↓ 0.0

Hash (cost=21.10..21.10 rows=16 width=27) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on merit_user_aging_factors (cost=2.55..21.10 rows=16 width=27) (actual rows= loops=)

  • Recheck Cond: ((610474 = user_id) AND (benchmark_year = 2018))
13. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on merit_user_aging_factors_pkey (cost=0.00..2.54 rows=16 width=0) (actual rows= loops=)

  • Index Cond: ((610474 = user_id) AND (benchmark_year = 2018))
14.          

CTE mms_view_610474_19384

15. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=979.77..4,446.47 rows=238 width=892) (actual rows= loops=)

  • Hash Cond: ((y_8.organization_id = ma.organization_id) AND ((y_8.org_job_code)::text = (ma.org_job_code)::text) AND ((y_8.org_job_code_key)::text = (ma.org_job_code_key)::text))
16. 0.000 0.000 ↓ 0.0

CTE Scan on ibm_mms_2018 y_8 (cost=0.00..3,450.23 rows=842 width=888) (actual rows= loops=)

  • Filter: (organization_id = 606563)
17. 0.000 0.000 ↓ 0.0

Hash (cost=977.03..977.03 rows=238 width=738) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

CTE Scan on ibm_mms_all ma (cost=0.00..977.03 rows=238 width=738) (actual rows= loops=)

  • Filter: (organization_id = 606563)
19. 0.000 0.000 ↓ 0.0

WindowAgg (cost=4.23..38,502.99 rows=457,566 width=394) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4.23..27,292.62 rows=457,566 width=146) (actual rows= loops=)

  • Hash Cond: ((org_jobs.organization_id = merit_currency_conversion_map_view.organization_id) AND ((org_jobs.currency)::text = merit_currency_conversion_map_view.source))
21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.06..26,554.43 rows=457,566 width=150) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.00..21,748.72 rows=457,566 width=150) (actual rows= loops=)

  • Join Filter: ((org_jobs.organization_id = mms_view_610474_19384.organization_id) AND ((org_jobs.org_job_code)::text = (mms_view_610474_19384.org_job_code)::text) AND ((org_jobs.org_job_code_key)::text = (mms_view_6
  • Filter: (((mms_view_610474_19384.slot_p)::text <> 'slotted'::text) OR (mms_view_610474_19384.slot_p IS NULL))
23. 0.000 0.000 ↓ 0.0

Seq Scan on org_jobs (cost=0.00..11,627.07 rows=459,854 width=70) (actual rows= loops=)

  • Filter: (organization_id = 606563)
24. 0.000 0.000 ↓ 0.0

CTE Scan on mms_view_610474_19384 (cost=0.00..4.88 rows=1 width=856) (actual rows= loops=)

  • Filter: (organization_id = 606563)
25. 0.000 0.000 ↓ 0.0

Materialize (cost=0.06..1.27 rows=1 width=4) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Index Only Scan using merit_acct_org_id_pk on merit_accounts (cost=0.06..1.27 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (organization_id = 606563)
27. 0.000 0.000 ↓ 0.0

Hash (cost=4.16..4.16 rows=1 width=36) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Subquery Scan on merit_currency_conversion_map_view (cost=4.13..4.16 rows=1 width=36) (actual rows= loops=)

  • Filter: ((merit_currency_conversion_map_view.effective_date IS NULL) OR (merit_currency_conversion_map_view.effective_date < now()))
29. 0.000 0.000 ↓ 0.0

Unique (cost=4.13..4.14 rows=2 width=120) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Sort (cost=4.13..4.14 rows=2 width=120) (actual rows= loops=)

  • Sort Key: "*SELECT* 1".organization_id, "*SELECT* 1".source, "*SELECT* 1".target, "*SELECT* 1".rate, (("*SELECT* 1".effective_date)::timestamp with time zone), "*SELECT* 1".last_modified, "*SELECT* 1".l
31. 0.000 0.000 ↓ 0.0

Append (cost=0.06..4.13 rows=2 width=120) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=0.06..2.66 rows=1 width=39) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Index Scan using mccm_idx on merit_currency_conversion_map (cost=0.06..2.65 rows=1 width=35) (actual rows= loops=)

  • Index Cond: ((organization_id = 606563) AND (target = ''::text))
34. 0.000 0.000 ↓ 0.0

Group (cost=0.06..1.45 rows=1 width=64) (actual rows= loops=)

  • Group Key: merit_currency_conversion_map_1.organization_id, merit_currency_conversion_map_1.target
35. 0.000 0.000 ↓ 0.0

Index Only Scan using mccm_idx on merit_currency_conversion_map merit_currency_conversion_map_1 (cost=0.06..1.45 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((organization_id = 606563) AND (target = ''::text))