explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1NnU

Settings
# exclusive inclusive rows x rows loops node
1. 1.058 711.023 ↓ 3.1 56 1

Nested Loop (cost=1,018.69..46,220.48 rows=18 width=547) (actual time=364.567..711.023 rows=56 loops=1)

2.          

CTE np_meannce_lkp

3. 0.121 0.121 ↑ 1.0 1,000 1

Seq Scan on np_meannce_lookup (cost=0.00..17.00 rows=1,000 width=10) (actual time=0.011..0.121 rows=1,000 loops=1)

4.          

CTE gme_lkp

5. 1.425 1.425 ↑ 1.0 10,685 1

Seq Scan on gme_lookup (cost=0.00..214.85 rows=10,685 width=30) (actual time=0.003..1.425 rows=10,685 loops=1)

6. 49.683 400.943 ↓ 7.0 7 1

GroupAggregate (cost=786.84..790.93 rows=1 width=13,091) (actual time=355.233..400.943 rows=7 loops=1)

  • Group Key: main.drcadministrationid, main.orgid, main.assessmenttype, main.testdate, main.testgrade, main.subjectname
7. 22.875 351.260 ↓ 742.0 742 1

Sort (cost=786.84..786.85 rows=1 width=13,031) (actual time=347.080..351.260 rows=742 loops=1)

  • Sort Key: main.drcadministrationid, main.orgid, main.assessmenttype, main.testdate, main.testgrade, main.subjectname
  • Sort Method: external sort Disk: 7512kB
8. 3.748 328.385 ↓ 742.0 742 1

Subquery Scan on main (cost=786.43..786.83 rows=1 width=13,031) (actual time=307.139..328.385 rows=742 loops=1)

9. 15.654 324.637 ↓ 742.0 742 1

WindowAgg (cost=786.43..786.82 rows=1 width=13,068) (actual time=307.120..324.637 rows=742 loops=1)

10. 16.403 308.983 ↓ 742.0 742 1

Sort (cost=786.43..786.44 rows=1 width=12,859) (actual time=306.371..308.983 rows=742 loops=1)

  • Sort Key: asmt.testfamily, asmt.testdate, stu.grade, obj.reporting_subjectname, demo.scu_programlep
  • Sort Method: external sort Disk: 7400kB
11. 12.404 292.580 ↓ 742.0 742 1

WindowAgg (cost=786.03..786.42 rows=1 width=12,859) (actual time=278.679..292.580 rows=742 loops=1)

12. 15.893 280.176 ↓ 742.0 742 1

Sort (cost=786.03..786.04 rows=1 width=12,619) (actual time=277.961..280.176 rows=742 loops=1)

  • Sort Key: asmt.testfamily, asmt.testdate, stu.grade, obj.reporting_subjectname, demo.scu_programiep
  • Sort Method: external sort Disk: 7256kB
13. 12.288 264.283 ↓ 742.0 742 1

WindowAgg (cost=785.63..786.02 rows=1 width=12,619) (actual time=250.528..264.283 rows=742 loops=1)

14. 15.871 251.995 ↓ 742.0 742 1

Sort (cost=785.63..785.64 rows=1 width=12,379) (actual time=249.834..251.995 rows=742 loops=1)

  • Sort Key: asmt.testfamily, asmt.testdate, stu.grade, obj.reporting_subjectname, demo.scu_program504
  • Sort Method: external sort Disk: 7128kB
15. 19.846 236.124 ↓ 742.0 742 1

WindowAgg (cost=783.08..785.62 rows=1 width=12,379) (actual time=214.991..236.124 rows=742 loops=1)

16. 15.459 216.278 ↓ 742.0 742 1

Sort (cost=783.08..783.09 rows=1 width=10,699) (actual time=214.377..216.278 rows=742 loops=1)

  • Sort Key: asmt.testfamily, asmt.testdate, stu.grade, obj.reporting_subjectname, demo.scq_ethnicity
  • Sort Method: external sort Disk: 6168kB
17. 12.273 200.819 ↓ 742.0 742 1

WindowAgg (cost=782.32..783.07 rows=1 width=10,699) (actual time=188.068..200.819 rows=742 loops=1)

18. 16.095 188.546 ↓ 742.0 742 1

Sort (cost=782.32..782.33 rows=1 width=10,219) (actual time=186.817..188.546 rows=742 loops=1)

  • Sort Key: asmt.testfamily, asmt.testdate, stu.grade, obj.reporting_subjectname, stu.gender
  • Sort Method: external sort Disk: 5880kB
19. 165.333 172.451 ↓ 742.0 742 1

WindowAgg (cost=764.11..782.31 rows=1 width=10,219) (actual time=29.892..172.451 rows=742 loops=1)

20. 0.910 7.118 ↓ 742.0 742 1

Sort (cost=764.11..764.11 rows=1 width=139) (actual time=6.895..7.118 rows=742 loops=1)

  • Sort Key: asmt.testfamily, asmt.testdate, stu.grade, obj.reporting_subjectname
  • Sort Method: quicksort Memory: 129kB
21. 0.579 6.208 ↓ 742.0 742 1

Nested Loop (cost=39.41..764.10 rows=1 width=139) (actual time=0.253..6.208 rows=742 loops=1)

22. 0.873 4.887 ↓ 742.0 742 1

Nested Loop (cost=39.13..763.78 rows=1 width=152) (actual time=0.238..4.887 rows=742 loops=1)

23. 0.321 3.272 ↓ 742.0 742 1

Nested Loop (cost=38.85..763.46 rows=1 width=129) (actual time=0.221..3.272 rows=742 loops=1)

24. 0.214 1.467 ↓ 8.0 742 1

Hash Join (cost=38.43..702.42 rows=93 width=90) (actual time=0.201..1.467 rows=742 loops=1)

  • Hash Cond: (x.objectiveid = obj.objectiveid)
25. 0.177 1.153 ↑ 1.4 742 1

Nested Loop (cost=7.66..668.89 rows=1,044 width=78) (actual time=0.089..1.153 rows=742 loops=1)

26. 0.021 0.089 ↑ 1.0 1 1

Hash Join (cost=7.24..14.37 rows=1 width=20) (actual time=0.067..0.089 rows=1 loops=1)

  • Hash Cond: (sch.parent_organizationid = dst.organizationid)
27. 0.037 0.037 ↓ 1.0 88 1

Seq Scan on dim_organizations sch (cost=0.00..6.91 rows=85 width=20) (actual time=0.015..0.037 rows=88 loops=1)

  • Filter: ((administrationid = 593218) AND (orglevel = 2))
  • Rows Removed by Filter: 39
28. 0.006 0.031 ↑ 1.0 1 1

Hash (cost=7.22..7.22 rows=1 width=16) (actual time=0.031..0.031 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.025 0.025 ↑ 1.0 1 1

Seq Scan on dim_organizations dst (cost=0.00..7.22 rows=1 width=16) (actual time=0.004..0.025 rows=1 loops=1)

  • Filter: ((administrationid = 593218) AND (orglevel = 1) AND ((orgcode)::text = 'BZA'::text))
  • Rows Removed by Filter: 126
30. 0.887 0.887 ↑ 2.3 742 1

Index Scan using udx_fact_subtestresult on fact_subtestresult x (cost=0.42..637.43 rows=1,709 width=82) (actual time=0.019..0.887 rows=742 loops=1)

  • Index Cond: ((administrationid = 593218) AND (organizationid = sch.organizationid))
  • Filter: (scalescore > 0)
  • Rows Removed by Filter: 1590
31. 0.011 0.100 ↓ 1.0 44 1

Hash (cost=30.23..30.23 rows=43 width=32) (actual time=0.100..0.100 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
32. 0.089 0.089 ↓ 1.0 44 1

Seq Scan on dim_objective obj (cost=0.00..30.23 rows=43 width=32) (actual time=0.051..0.089 rows=44 loops=1)

  • Filter: ((administrationid = 593218) AND ((testscore_category)::text = 'SUBJECT'::text))
  • Rows Removed by Filter: 438
33. 1.484 1.484 ↑ 1.0 1 742

Index Scan using pk_dim_assessmentparticipant on dim_assessmentparticipant asmt (cost=0.42..0.66 rows=1 width=59) (actual time=0.002..0.002 rows=1 loops=742)

  • Index Cond: (assessmentparticipantid = x.assessmentparticipantid)
  • Filter: ((administrationid = 593218) AND ((is_reportable)::text = 'YES'::text) AND ((reportphase)::text = 'IR'::text) AND (COALESCE(NULLIF((nottestedcode)::text, ''::text), '*'::text) = ANY ('{*,DN}'::text[])))
34. 0.742 0.742 ↑ 1.0 1 742

Index Scan using pk_dim_participantdemographic on dim_participantdemographic demo (cost=0.29..0.32 rows=1 width=27) (actual time=0.001..0.001 rows=1 loops=742)

  • Index Cond: ((administrationid = 593218) AND (participantid = x.participantid))
35. 0.742 0.742 ↑ 1.0 1 742

Index Scan using pk_dim_participant on dim_participant stu (cost=0.29..0.32 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=742)

  • Index Cond: (participantid = x.participantid)
  • Filter: (administrationid = 593218)
36. 1.414 1.414 ↑ 2.2 8 7

Values Scan on "*VALUES*" (cost=0.00..0.81 rows=18 width=104) (actual time=0.007..0.202 rows=8 loops=7)

  • Filter: (column2 > 0)
  • Rows Removed by Filter: 46
37.          

SubPlan (forNested Loop)

38. 23.912 23.912 ↑ 5.0 1 56

CTE Scan on np_meannce_lkp (cost=0.00..40.00 rows=5 width=4) (actual time=0.059..0.427 rows=1 loops=56)

  • Filter: (meannce = trunc(((((20 * "*VALUES*".column6) + "*VALUES*".column2))::numeric / ((20 * "*VALUES*".column2))::numeric), 1))
  • Rows Removed by Filter: 999
39. 7.336 7.336 ↓ 0.0 0 56

CTE Scan on np_meannce_lkp np_meannce_lkp_1 (cost=0.00..40.00 rows=5 width=4) (actual time=0.131..0.131 rows=0 loops=56)

  • Filter: (meannce = trunc(((((20 * "*VALUES*".column7) + "*VALUES*".column2))::numeric / ((20 * "*VALUES*".column2))::numeric), 1))
  • Rows Removed by Filter: 1000
40. 23.520 23.520 ↑ 5.0 1 56

CTE Scan on np_meannce_lkp np_meannce_lkp_2 (cost=0.00..40.00 rows=5 width=4) (actual time=0.057..0.420 rows=1 loops=56)

  • Filter: (meannce = trunc(((((20 * "*VALUES*".column6) + "*VALUES*".column2))::numeric / ((20 * "*VALUES*".column2))::numeric), 1))
  • Rows Removed by Filter: 999
41. 7.392 7.392 ↓ 0.0 0 56

CTE Scan on np_meannce_lkp np_meannce_lkp_3 (cost=0.00..40.00 rows=5 width=4) (actual time=0.132..0.132 rows=0 loops=56)

  • Filter: (meannce = trunc(((((20 * "*VALUES*".column7) + "*VALUES*".column2))::numeric / ((20 * "*VALUES*".column2))::numeric), 1))
  • Rows Removed by Filter: 1000
42. 0.112 65.632 ↑ 1.0 1 56

Aggregate (cost=587.68..587.69 rows=1 width=32) (actual time=1.172..1.172 rows=1 loops=56)

43. 65.520 65.520 ↓ 9.0 9 56

CTE Scan on gme_lkp (cost=0.00..587.68 rows=1 width=16) (actual time=0.255..1.170 rows=9 loops=56)

  • Filter: ((norm_year = (max(main.normsyear))) AND ((reporting_subjectname)::text = (main.subjectname)::text) AND ((subgroupvalue)::text = "left"((max((main.normsseason)::text)), 1)) AND ((scalescore)::numeric >= round(((((200 * "*VALUES*".column4) + "*VALUES*".column2))::numeric / ((200 * "*VALUES*".column2))::numeric), 2)))
  • Rows Removed by Filter: 10676
44. 0.000 59.080 ↑ 1.0 1 56

Aggregate (cost=587.68..587.69 rows=1 width=32) (actual time=1.055..1.055 rows=1 loops=56)

45. 59.080 59.080 ↓ 0.0 0 56

CTE Scan on gme_lkp gme_lkp_1 (cost=0.00..587.68 rows=1 width=16) (actual time=1.055..1.055 rows=0 loops=56)

  • Filter: ((norm_year = (max(main.normsyear))) AND ((reporting_subjectname)::text = (main.subjectname)::text) AND ((subgroupvalue)::text = "left"((max((main.normsseason)::text)), 1)) AND ((scalescore)::numeric >= round(((((200 * "*VALUES*".column5) + "*VALUES*".column2))::numeric / ((200 * "*VALUES*".column2))::numeric), 2)))
  • Rows Removed by Filter: 10685
46. 0.112 61.936 ↑ 1.0 1 56

Aggregate (cost=587.68..587.69 rows=1 width=32) (actual time=1.106..1.106 rows=1 loops=56)

47. 61.824 61.824 ↓ 9.0 9 56

CTE Scan on gme_lkp gme_lkp_2 (cost=0.00..587.68 rows=1 width=16) (actual time=0.211..1.104 rows=9 loops=56)

  • Filter: ((norm_year = (max(main.normsyear))) AND ((reporting_subjectname)::text = (main.subjectname)::text) AND ((subgroupvalue)::text = "left"((max((main.normsseason)::text)), 1)) AND ((scalescore)::numeric >= round(((((200 * "*VALUES*".column4) + "*VALUES*".column2))::numeric / ((200 * "*VALUES*".column2))::numeric), 2)))
  • Rows Removed by Filter: 10676
48. 0.056 58.800 ↑ 1.0 1 56

Aggregate (cost=587.68..587.69 rows=1 width=32) (actual time=1.050..1.050 rows=1 loops=56)

49. 58.744 58.744 ↓ 0.0 0 56

CTE Scan on gme_lkp gme_lkp_3 (cost=0.00..587.68 rows=1 width=16) (actual time=1.049..1.049 rows=0 loops=56)

  • Filter: ((norm_year = (max(main.normsyear))) AND ((reporting_subjectname)::text = (main.subjectname)::text) AND ((subgroupvalue)::text = "left"((max((main.normsseason)::text)), 1)) AND ((scalescore)::numeric >= round(((((200 * "*VALUES*".column5) + "*VALUES*".column2))::numeric / ((200 * "*VALUES*".column2))::numeric), 2)))
  • Rows Removed by Filter: 10685
Planning time : 352.339 ms
Execution time : 798.311 ms