explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6NPF

Settings
# exclusive inclusive rows x rows loops node
1. 0.978 707.149 ↓ 3.1 56 1

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

2.          

CTE np_meannce_lkp

3. 0.113 0.113 ↑ 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.113 rows=1,000 loops=1)

4.          

CTE gme_lkp

5. 1.441 1.441 ↑ 1.0 10,685 1

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

6. 48.550 401.860 ↓ 7.0 7 1

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

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

Sort (cost=786.84..786.85 rows=1 width=13,031) (actual time=349.057..353.310 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. 2.963 334.202 ↓ 742.0 742 1

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

9. 12.866 331.239 ↓ 742.0 742 1

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

10. 16.117 318.373 ↓ 742.0 742 1

Sort (cost=786.43..786.44 rows=1 width=12,859) (actual time=316.159..318.373 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.509 302.256 ↓ 742.0 742 1

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

12. 15.942 289.747 ↓ 742.0 742 1

Sort (cost=786.03..786.04 rows=1 width=12,619) (actual time=287.546..289.747 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.415 273.805 ↓ 742.0 742 1

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

14. 16.015 261.390 ↓ 742.0 742 1

Sort (cost=785.63..785.64 rows=1 width=12,379) (actual time=259.240..261.390 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.715 245.375 ↓ 742.0 742 1

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

16. 15.636 225.660 ↓ 742.0 742 1

Sort (cost=783.08..783.09 rows=1 width=10,699) (actual time=223.731..225.660 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.345 210.024 ↓ 742.0 742 1

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

18. 16.519 197.679 ↓ 742.0 742 1

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

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

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

20. 1.134 8.438 ↓ 742.0 742 1

Sort (cost=764.11..764.11 rows=1 width=139) (actual time=8.200..8.438 rows=742 loops=1)

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

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

22. 0.395 5.775 ↓ 742.0 742 1

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

23. 0.648 3.896 ↓ 742.0 742 1

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

24. 0.251 1.764 ↓ 8.0 742 1

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

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

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

26. 0.023 0.104 ↑ 1.0 1 1

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

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

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

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

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

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

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

  • Filter: ((administrationid = 593218) AND (orglevel = 1) AND ((orgcode)::text = 'BZA'::text))
  • Rows Removed by Filter: 126
30. 1.111 1.111 ↑ 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.018..1.111 rows=742 loops=1)

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

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

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

Seq Scan on dim_objective obj (cost=0.00..30.23 rows=43 width=32) (actual time=0.049..0.087 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. 1.484 1.484 ↑ 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.002..0.002 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.351 1.351 ↑ 2.2 8 7

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

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

SubPlan (forNested Loop)

38. 23.576 23.576 ↑ 5.0 1 56

CTE Scan on np_meannce_lkp (cost=0.00..40.00 rows=5 width=4) (actual time=0.054..0.421 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.280 7.280 ↓ 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.130..0.130 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.128 23.128 ↑ 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.052..0.413 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.280 7.280 ↓ 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.130..0.130 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.168 64.624 ↑ 1.0 1 56

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

43. 64.456 64.456 ↓ 9.0 9 56

CTE Scan on gme_lkp (cost=0.00..587.68 rows=1 width=16) (actual time=0.255..1.151 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.056 58.016 ↑ 1.0 1 56

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

45. 57.960 57.960 ↓ 0.0 0 56

CTE Scan on gme_lkp gme_lkp_1 (cost=0.00..587.68 rows=1 width=16) (actual time=1.035..1.035 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 60.928 ↑ 1.0 1 56

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

47. 60.816 60.816 ↓ 9.0 9 56

CTE Scan on gme_lkp gme_lkp_2 (cost=0.00..587.68 rows=1 width=16) (actual time=0.212..1.086 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.128 ↑ 1.0 1 56

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

49. 58.072 58.072 ↓ 0.0 0 56

CTE Scan on gme_lkp gme_lkp_3 (cost=0.00..587.68 rows=1 width=16) (actual time=1.037..1.037 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 : 344.094 ms
Execution time : 796.335 ms