explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xU3A

Settings
# exclusive inclusive rows x rows loops node
1. 1.026 799.793 ↓ 3.1 56 1

Nested Loop (cost=1,019.86..46,221.64 rows=18 width=547) (actual time=396.448..799.793 rows=56 loops=1)

2.          

CTE np_meannce_lkp

3. 0.143 0.143 ↑ 1.0 1,000 1

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

4.          

CTE gme_lkp

5. 1.660 1.660 ↑ 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.660 rows=10,685 loops=1)

6. 52.337 434.599 ↓ 7.0 7 1

GroupAggregate (cost=788.01..792.09 rows=1 width=13,091) (actual time=385.685..434.599 rows=7 loops=1)

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

Sort (cost=788.01..788.01 rows=1 width=13,031) (actual time=377.457..382.262 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.377 360.602 ↓ 742.0 742 1

Subquery Scan on main (cost=787.59..788.00 rows=1 width=13,031) (actual time=340.729..360.602 rows=742 loops=1)

9. 14.829 357.225 ↓ 742.0 742 1

WindowAgg (cost=787.59..787.99 rows=1 width=13,068) (actual time=340.709..357.225 rows=742 loops=1)

10. 18.508 342.396 ↓ 742.0 742 1

Sort (cost=787.59..787.60 rows=1 width=12,859) (actual time=339.856..342.396 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. 14.252 323.888 ↓ 742.0 742 1

WindowAgg (cost=787.19..787.58 rows=1 width=12,859) (actual time=307.931..323.888 rows=742 loops=1)

12. 18.216 309.636 ↓ 742.0 742 1

Sort (cost=787.19..787.20 rows=1 width=12,619) (actual time=307.104..309.636 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. 14.178 291.420 ↓ 742.0 742 1

WindowAgg (cost=786.79..787.18 rows=1 width=12,619) (actual time=275.608..291.420 rows=742 loops=1)

14. 18.359 277.242 ↓ 742.0 742 1

Sort (cost=786.79..786.80 rows=1 width=12,379) (actual time=274.818..277.242 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. 22.883 258.883 ↓ 742.0 742 1

WindowAgg (cost=784.24..786.78 rows=1 width=12,379) (actual time=234.465..258.883 rows=742 loops=1)

16. 17.743 236.000 ↓ 742.0 742 1

Sort (cost=784.24..784.25 rows=1 width=10,699) (actual time=233.765..236.000 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. 14.110 218.257 ↓ 742.0 742 1

WindowAgg (cost=783.49..784.23 rows=1 width=10,699) (actual time=203.454..218.257 rows=742 loops=1)

18. 18.283 204.147 ↓ 742.0 742 1

Sort (cost=783.49..783.49 rows=1 width=10,219) (actual time=202.071..204.147 rows=742 loops=1)

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

WindowAgg (cost=765.27..783.48 rows=1 width=10,219) (actual time=32.579..185.864 rows=742 loops=1)

20. 1.028 8.216 ↓ 742.0 742 1

Sort (cost=765.27..765.27 rows=1 width=139) (actual time=7.999..8.216 rows=742 loops=1)

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

Nested Loop (cost=39.41..765.26 rows=1 width=139) (actual time=0.271..7.188 rows=742 loops=1)

22. 0.354 5.643 ↓ 742.0 742 1

Nested Loop (cost=39.13..764.94 rows=1 width=160) (actual time=0.254..5.643 rows=742 loops=1)

23. 0.623 3.805 ↓ 742.0 742 1

Nested Loop (cost=38.85..764.63 rows=1 width=137) (actual time=0.236..3.805 rows=742 loops=1)

24. 0.247 1.698 ↓ 8.0 742 1

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

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

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

26. 0.020 0.092 ↑ 1.0 1 1

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

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

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

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

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

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

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

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

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

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

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

Seq Scan on dim_objective obj (cost=0.00..30.23 rows=43 width=32) (actual time=0.054..0.096 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=67) (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 (x.participantid = participantid) 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.568 1.568 ↑ 2.2 8 7

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

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

SubPlan (forNested Loop)

38. 27.944 27.944 ↑ 5.0 1 56

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

  • Filter: (meannce = trunc(((((20 * "*VALUES*".column6) + "*VALUES*".column2))::numeric / ((20 * "*VALUES*".column2))::numeric), 1))
  • Rows Removed by Filter: 999
39. 8.624 8.624 ↓ 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.154..0.154 rows=0 loops=56)

  • Filter: (meannce = trunc(((((20 * "*VALUES*".column7) + "*VALUES*".column2))::numeric / ((20 * "*VALUES*".column2))::numeric), 1))
  • Rows Removed by Filter: 1000
40. 27.496 27.496 ↑ 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.062..0.491 rows=1 loops=56)

  • Filter: (meannce = trunc(((((20 * "*VALUES*".column6) + "*VALUES*".column2))::numeric / ((20 * "*VALUES*".column2))::numeric), 1))
  • Rows Removed by Filter: 999
41. 8.680 8.680 ↓ 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.155..0.155 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 76.664 ↑ 1.0 1 56

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

43. 76.496 76.496 ↓ 9.0 9 56

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

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

45. 69.664 69.664 ↓ 0.0 0 56

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

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

47. 73.416 73.416 ↓ 9.0 9 56

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

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

49. 69.832 69.832 ↓ 0.0 0 56

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