explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lb5Q

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 31,266,677.863 ↑ 1.0 100 1

Limit (cost=31,518.36..31,527.59 rows=100 width=394) (actual time=31,266,677.788..31,266,677.863 rows=100 loops=1)

2.          

CTE ibm_mms_all

3. 200.678 327.919 ↓ 7.2 338,871 1

Finalize HashAggregate (cost=7,568.87..8,040.29 rows=47,142 width=30) (actual time=252.352..327.919 rows=338,871 loops=1)

  • Group Key: mall.org_job_code, mall.org_job_code_key, mall.organization_id
4. 57.221 127.241 ↓ 1.8 339,745 1

Gather (cost=5,871.76..7,286.02 rows=188,568 width=26) (actual time=62.673..127.241 rows=339,745 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
5. 45.124 70.020 ↓ 1.4 67,949 5

Partial HashAggregate (cost=5,821.76..6,293.18 rows=47,142 width=26) (actual time=55.697..70.020 rows=67,949 loops=5)

  • Group Key: mall.org_job_code, mall.org_job_code_key, mall.organization_id
6. 24.896 24.896 ↑ 1.2 94,284 5

Parallel Index Only Scan using ibm_mms_orgid_jc_jck on merit_match_summaries mall (cost=0.08..5,644.99 rows=117,845 width=26) (actual time=0.051..24.896 rows=94,284 loops=5)

  • Filter: ((org_job_code)::text !~~ 'unknown%'::text)
  • Heap Fetches: 0
7.          

CTE ibm_mms_2018

8. 0.000 53.883 ↑ 1.0 163,843 1

Gather (cost=57.46..19,121.52 rows=164,385 width=101) (actual time=1.646..53.883 rows=163,843 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
9. 24.249 98.340 ↑ 1.3 32,769 5

Hash Left Join (cost=7.46..18,249.60 rows=41,096 width=101) (actual time=0.254..98.340 rows=32,769 loops=5)

  • 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 = (merit_user_aging_factors.aging_code)::text))
10. 74.048 74.048 ↑ 1.3 32,769 5

Parallel Seq Scan on merit_match_summaries (cost=0.00..18,177.41 rows=41,096 width=76) (actual time=0.020..74.048 rows=32,769 loops=5)

  • Filter: (((org_job_code)::text !~~ 'unknown%'::text) AND (survey_year = 2018))
  • Rows Removed by Filter: 61516
11. 0.001 0.043 ↓ 0.0 0 5

Hash (cost=7.35..7.35 rows=10 width=27) (actual time=0.043..0.043 rows=0 loops=5)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
12. 0.042 0.042 ↓ 0.0 0 5

Index Scan using merit_user_aging_factors_pkey on merit_user_aging_factors (cost=0.08..7.35 rows=10 width=27) (actual time=0.042..0.042 rows=0 loops=5)

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

CTE mms_view_610474_19384

14. 150.452 802.641 ↓ 1,435.9 338,871 1

Hash Right Join (cost=969.13..4,355.10 rows=236 width=892) (actual time=522.767..802.641 rows=338,871 loops=1)

  • 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))
15. 131.103 131.103 ↓ 199.3 163,843 1

CTE Scan on ibm_mms_2018 y_8 (cost=0.00..3,369.89 rows=822 width=888) (actual time=1.652..131.103 rows=163,843 loops=1)

  • Filter: (organization_id = 606563)
16. 76.990 521.086 ↓ 1,435.9 338,871 1

Hash (cost=966.41..966.41 rows=236 width=738) (actual time=521.086..521.086 rows=338,871 loops=1)

  • Buckets: 524288 (originally 1024) Batches: 1 (originally 1) Memory Usage: 23694kB
17. 444.096 444.096 ↓ 1,435.9 338,871 1

CTE Scan on ibm_mms_all ma (cost=0.00..966.41 rows=236 width=738) (actual time=252.356..444.096 rows=338,871 loops=1)

  • Filter: (organization_id = 606563)
18. 4,842.179 31,266,677.845 ↑ 4,500.7 100 1

WindowAgg (cost=1.45..41,544.86 rows=450,068 width=394) (actual time=31,266,677.787..31,266,677.845 rows=100 loops=1)

19. 617.448 31,261,835.666 ↓ 1.0 452,318 1

Nested Loop Left Join (cost=1.45..30,518.19 rows=450,068 width=146) (actual time=995.170..31,261,835.666 rows=452,318 loops=1)

  • Join Filter: ((mat_merit_currency_conversion_map_view.organization_id = org_jobs.organization_id) AND ((org_jobs.currency)::text = mat_merit_currency_conversion_map_view.source))
20. 1,035.130 31,261,218.218 ↓ 1.0 452,318 1

Nested Loop (cost=0.06..25,262.34 rows=450,068 width=150) (actual time=994.958..31,261,218.218 rows=452,318 loops=1)

21. 11,560,409.383 31,260,183.088 ↓ 1.0 452,318 1

Nested Loop Left Join (cost=0.00..20,534.16 rows=450,068 width=150) (actual time=994.929..31,260,183.088 rows=452,318 loops=1)

  • 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_610474_19384.org_job_code_key)::text))
  • Rows Removed by Join Filter: 153277114107
  • Filter: (((mms_view_610474_19384.slot_p)::text <> 'slotted'::text) OR (mms_view_610474_19384.slot_p IS NULL))
22. 1,324.805 1,324.805 ↑ 1.0 452,318 1

Seq Scan on org_jobs (cost=0.00..10,578.34 rows=452,318 width=70) (actual time=0.008..1,324.805 rows=452,318 loops=1)

  • Filter: (organization_id = 606563)
23. 19,698,448.900 19,698,448.900 ↓ 338,871.0 338,871 452,318

CTE Scan on mms_view_610474_19384 (cost=0.00..4.84 rows=1 width=856) (actual time=0.006..43.550 rows=338,871 loops=452,318)

  • Filter: (organization_id = 606563)
24. 0.000 0.000 ↑ 1.0 1 452,318

Materialize (cost=0.06..2.47 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=452,318)

25. 0.023 0.023 ↑ 1.0 1 1

Index Only Scan using merit_acct_org_id_pk on merit_accounts (cost=0.06..2.47 rows=1 width=4) (actual time=0.022..0.023 rows=1 loops=1)

  • Index Cond: (organization_id = 606563)
  • Heap Fetches: 0
26. 0.000 0.000 ↓ 0.0 0 452,318

Materialize (cost=1.40..80.07 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=452,318)

27. 0.150 0.208 ↓ 0.0 0 1

Bitmap Heap Scan on mat_merit_currency_conversion_map_view (cost=1.40..80.07 rows=1 width=8) (actual time=0.208..0.208 rows=0 loops=1)

  • Recheck Cond: (organization_id = 606563)
  • Filter: ((target = ''::text) AND ((effective_date IS NULL) OR (effective_date < now())))
  • Rows Removed by Filter: 92
  • Heap Blocks: exact=82
28. 0.058 0.058 ↑ 1.0 92 1

Bitmap Index Scan on mat_merit_currency_conversion_map_view_idx (cost=0.00..1.40 rows=92 width=0) (actual time=0.056..0.058 rows=92 loops=1)

  • Index Cond: (organization_id = 606563)
Planning time : 3.061 ms
Execution time : 31,266,757.221 ms