explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lABB

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 23,380.733 ↑ 1.0 5 1

Limit (cost=6,313.02..6,313.08 rows=5 width=956) (actual time=23,379.595..23,380.733 rows=5 loops=1)

2.          

Initplan (forLimit)

3. 3.523 4.505 ↑ 1.0 1 1

Aggregate (cost=149.82..149.83 rows=1 width=8) (actual time=4.504..4.505 rows=1 loops=1)

4. 0.982 0.982 ↑ 1.0 9,684 1

Index Only Scan using marketpaymaster_org_jobs_org_job_code_idx on org_jobs (cost=0.06..144.98 rows=9,684 width=7) (actual time=0.057..0.982 rows=9,684 loops=1)

  • Heap Fetches: 0
5. 3.266 3.959 ↑ 1.0 1 1

Aggregate (cost=149.82..149.83 rows=1 width=8) (actual time=3.959..3.959 rows=1 loops=1)

6. 0.693 0.693 ↑ 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..144.98 rows=9,684 width=7) (actual time=0.009..0.693 rows=9,684 loops=1)

  • Heap Fetches: 0
7. 0.757 23,380.709 ↑ 3,275.4 5 1

Unique (cost=6,013.36..6,193.50 rows=16,377 width=956) (actual time=23,379.593..23,380.709 rows=5 loops=1)

8. 20,218.869 23,379.952 ↑ 11.9 1,371 1

Sort (cost=6,013.36..6,021.54 rows=16,377 width=956) (actual time=23,379.591..23,379.952 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: 251408kB
9. 2,130.224 3,161.083 ↓ 57.7 944,555 1

WindowAgg (cost=2.94..5,784.09 rows=16,377 width=956) (actual time=1,565.346..3,161.083 rows=944,555 loops=1)

10. 107.302 1,030.859 ↓ 57.7 944,555 1

Nested Loop Left Join (cost=2.94..5,382.85 rows=16,377 width=548) (actual time=0.376..1,030.859 rows=944,555 loops=1)

11. 2.945 591.001 ↓ 39.2 7,918 1

Merge Left Join (cost=2.83..3,183.78 rows=202 width=562) (actual time=0.348..591.001 rows=7,918 loops=1)

  • Merge Cond: ((m.pub_survey_code)::text = (matches.survey_code)::text)
  • Join Filter: (matches.organization_id = m.account_id)
12. 4.665 587.988 ↓ 39.2 7,918 1

Nested Loop Left Join (cost=0.31..3,181.07 rows=202 width=550) (actual time=0.296..587.988 rows=7,918 loops=1)

13. 10.173 535.815 ↓ 39.2 7,918 1

Nested Loop (cost=0.22..2,962.30 rows=202 width=526) (actual time=0.281..535.815 rows=7,918 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: 29582
14. 46.843 525.642 ↓ 93.1 37,500 1

Nested Loop (cost=0.17..2,954.80 rows=403 width=530) (actual time=0.080..525.642 rows=37,500 loops=1)

15. 52.841 52.841 ↓ 88.7 70,993 1

Index Scan using merit_account_source_map_pk on merit_account_source_map m (cost=0.09..1,399.20 rows=800 width=22) (actual time=0.054..52.841 rows=70,993 loops=1)

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

Index Scan using pub_survey_sources_pub_survey_code_idx on pub_survey_sources p (cost=0.08..1.94 rows=1 width=508) (actual time=0.006..0.006 rows=1 loops=70,993)

  • Index Cond: ((pub_survey_code)::text = (m.pub_survey_code)::text)
  • Filter: (active_p AND (organization_id IS NULL))
  • Rows Removed by Filter: 0
17. 0.000 0.000 ↑ 1.0 1 37,500

Materialize (cost=0.06..2.47 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=37,500)

18. 0.010 0.010 ↑ 1.0 1 1

Index Scan using merit_acct_org_id_pk on merit_accounts a (cost=0.06..2.47 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (organization_id = 5919)
19. 47.508 47.508 ↑ 1.0 1 7,918

Index Scan using pub_survey_jobs_agg_survey_code on pub_survey_jobs_agg sj (cost=0.08..1.08 rows=1 width=41) (actual time=0.006..0.006 rows=1 loops=7,918)

  • Index Cond: ((m.pub_survey_code)::text = (survey_code)::text)
20. 0.032 0.068 ↑ 2.5 28 1

Sort (cost=2.52..2.55 rows=70 width=33) (actual time=0.049..0.068 rows=28 loops=1)

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

Index Only Scan using matches_aggregate_current_composite_idx_new on matches_aggregate_current matches (cost=0.08..2.09 rows=70 width=33) (actual time=0.031..0.036 rows=28 loops=1)

  • Index Cond: (organization_id = 5919)
  • Heap Fetches: 0
22. 332.556 332.556 ↑ 2.4 119 7,918

Index Only Scan using pub_survey_job_scopes_survey_code_currency_job_scope_idx on pub_survey_job_scopes psjs (cost=0.11..8.02 rows=287 width=15) (actual time=0.011..0.042 rows=119 loops=7,918)

  • Index Cond: (survey_code = (m.pub_survey_code)::text)
  • Heap Fetches: 18214