explain.depesz.com

PostgreSQL's explain analyze made readable

Result: glhB

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 8,673.161 ↑ 1.0 5 1

Limit (cost=21,512.77..21,512.82 rows=5 width=2,391) (actual time=8,671.509..8,673.161 rows=5 loops=1)

2.          

Initplan (forLimit)

3. 4.502 5.486 ↑ 1.0 1 1

Aggregate (cost=143.42..143.43 rows=1 width=8) (actual time=5.486..5.486 rows=1 loops=1)

4. 0.984 0.984 ↑ 1.0 9,684 1

Index Only Scan using marketpaymaster_org_jobs_org_job_code_idx on org_jobs (cost=0.06..138.58 rows=9,684 width=218) (actual time=0.032..0.984 rows=9,684 loops=1)

  • Heap Fetches: 0
5. 4.389 5.307 ↑ 1.0 1 1

Aggregate (cost=143.42..143.43 rows=1 width=8) (actual time=5.307..5.307 rows=1 loops=1)

6. 0.918 0.918 ↑ 1.0 9,684 1

Index Only Scan using marketpaymaster_org_jobs_org_job_code_idx on org_jobs org_jobs_1 (cost=0.06..138.58 rows=9,684 width=218) (actual time=0.007..0.918 rows=9,684 loops=1)

  • Heap Fetches: 0
7. 1.021 8,673.152 ↑ 8,852.4 5 1

Unique (cost=21,225.90..21,712.79 rows=44,262 width=2,391) (actual time=8,671.508..8,673.152 rows=5 loops=1)

8. 4,766.964 8,672.131 ↑ 32.3 1,371 1

Sort (cost=21,225.90..21,248.03 rows=44,262 width=2,391) (actual time=8,671.506..8,672.131 rows=1,371 loops=1)

  • Sort Key: (CASE WHEN (sj.num_distinct_survey_jobs = 0) THEN '0'::numeric ELSE COALESCE(round(('100'::numeric * ((((COALESCE(matches.num_jobs_matched, '0'::bigint))::numeric * 1.0) / (sj.num_distinct_survey_jobs)::numeric) *
  • Sort Method: external merge Disk: 247824kB
9. 2,597.768 3,905.167 ↓ 21.0 929,785 1

WindowAgg (cost=19.30..2,267.17 rows=44,262 width=2,391) (actual time=1,962.601..3,905.167 rows=929,785 loops=1)

10. 201.692 1,307.399 ↓ 21.0 929,785 1

Merge Left Join (cost=19.30..1,182.75 rows=44,262 width=1,983) (actual time=0.292..1,307.399 rows=929,785 loops=1)

  • Merge Cond: ((m.pub_survey_code)::text = (matches.survey_code)::text)
  • Join Filter: (matches.organization_id = m.account_id)
11. 148.055 1,105.531 ↓ 63.0 929,785 1

Nested Loop Left Join (cost=0.42..595.74 rows=14,754 width=2,189) (actual time=0.253..1,105.531 rows=929,785 loops=1)

12. 7.446 692.684 ↓ 7,788.0 7,788 1

Nested Loop Left Join (cost=0.31..92.41 rows=1 width=2,189) (actual time=0.232..692.684 rows=7,788 loops=1)

13. 20.724 630.722 ↓ 7,788.0 7,788 1

Nested Loop (cost=0.22..90.32 rows=1 width=2,165) (actual time=0.221..630.722 rows=7,788 loops=1)

  • Join Filter: CASE WHEN (m.account_id = 5919) THEN (p.survey_year = a.survey_year) ELSE (p.survey_year >= (a.survey_year - 2)) END
  • Rows Removed by Join Filter: 29576
14. 40.960 125.582 ↓ 60,552.0 60,552 1

Nested Loop (cost=0.14..88.21 rows=1 width=230) (actual time=0.043..125.582 rows=60,552 loops=1)

15. 24.070 24.070 ↓ 60,552.0 60,552 1

Index Scan using merit_account_source_map_pk on merit_account_source_map m (cost=0.09..86.14 rows=1 width=226) (actual time=0.031..24.070 rows=60,552 loops=1)

  • Index Cond: ((account_id = 5919) AND (org_group_id = 5892))
  • Filter: (archive_p = 0)
  • Rows Removed by Filter: 2
16. 60.552 60.552 ↑ 1.0 1 60,552

Index Scan using merit_acct_org_id_pk on merit_accounts a (cost=0.06..2.07 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=60,552)

  • Index Cond: (organization_id = 5919)
17. 484.416 484.416 ↑ 1.0 1 60,552

Index Scan using pub_survey_sources_pub_survey_code_idx on pub_survey_sources p (cost=0.08..2.09 rows=1 width=1,943) (actual time=0.008..0.008 rows=1 loops=60,552)

  • Index Cond: ((pub_survey_code)::text = (m.pub_survey_code)::text)
  • Filter: (active_p AND (organization_id IS NULL))
  • Rows Removed by Filter: 0
18. 54.516 54.516 ↑ 1.0 1 7,788

Index Scan using pub_survey_jobs_agg_survey_code on pub_survey_jobs_agg sj (cost=0.08..2.09 rows=1 width=242) (actual time=0.007..0.007 rows=1 loops=7,788)

  • Index Cond: ((m.pub_survey_code)::text = (survey_code)::text)
19. 264.792 264.792 ↑ 124.0 119 7,788

Index Only Scan using pub_survey_job_scopes_survey_code_currency_job_scope_idx on pub_survey_job_scopes psjs (cost=0.11..355.78 rows=14,754 width=218) (actual time=0.011..0.034 rows=119 loops=7,788)

  • Index Cond: (survey_code = (m.pub_survey_code)::text)
  • Heap Fetches: 0
20. 0.159 0.176 ↓ 3.5 2,310 1

Sort (cost=18.88..19.21 rows=661 width=238) (actual time=0.037..0.176 rows=2,310 loops=1)

  • Sort Key: matches.survey_code
  • Sort Method: quicksort Memory: 27kB
21. 0.017 0.017 ↑ 23.6 28 1

Index Only Scan using matches_aggregate_current_composite_idx_new on matches_aggregate_current matches (cost=0.08..12.69 rows=661 width=238) (actual time=0.011..0.017 rows=28 loops=1)

  • Index Cond: (organization_id = 5919)
  • Heap Fetches: 0