explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JZH1

Settings
# exclusive inclusive rows x rows loops node
1. 67.475 144.260 ↓ 2.6 14,954 1

Sort (cost=1,456.41..1,470.99 rows=5,834 width=475) (actual time=137.999..144.260 rows=14,954 loops=1)

  • Sort Key: t.specificationname
  • Sort Method: external merge Disk: 7000kB
2. 10.184 76.785 ↓ 2.6 14,954 1

Hash Left Join (cost=611.22..1,091.48 rows=5,834 width=475) (actual time=21.098..76.785 rows=14,954 loops=1)

  • Hash Cond: (t.gradebandid = syst.id)
3. 10.084 66.200 ↓ 2.6 14,954 1

Hash Left Join (cost=584.03..990.64 rows=5,834 width=460) (actual time=20.688..66.200 rows=14,954 loops=1)

  • Hash Cond: (t.cognitivetaxonomyid = ct.cognitivetaxonomyid)
4. 9.956 56.093 ↓ 2.6 14,954 1

Hash Left Join (cost=582.40..917.06 rows=5,834 width=453) (actual time=20.655..56.093 rows=14,954 loops=1)

  • Hash Cond: (t.frameworklevelid = fl.frameworklevelid)
5. 10.018 45.889 ↓ 2.6 14,954 1

Hash Left Join (cost=568.38..829.54 rows=5,834 width=444) (actual time=20.389..45.889 rows=14,954 loops=1)

  • Hash Cond: (tstp.statementofpurposeid = stp.statementofpurposeid)
6. 13.974 35.787 ↓ 2.6 14,954 1

Hash Right Join (cost=562.88..750.86 rows=5,834 width=431) (actual time=20.293..35.787 rows=14,954 loops=1)

  • Hash Cond: (tstp.testspecificationid = t.testspecificationid)
7. 1.558 1.558 ↓ 1.0 6,045 1

Seq Scan on testspecstatementofpurpose tstp (cost=0.00..99.43 rows=6,043 width=16) (actual time=0.005..1.558 rows=6,045 loops=1)

8. 2.744 20.255 ↓ 1.1 2,791 1

Hash (cost=531.52..531.52 rows=2,509 width=423) (actual time=20.255..20.255 rows=2,791 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1241kB
9. 1.895 17.511 ↓ 1.1 2,791 1

Hash Left Join (cost=366.13..531.52 rows=2,509 width=423) (actual time=10.802..17.511 rows=2,791 loops=1)

  • Hash Cond: (tstr.typeofreportingid = tyrp.typeofreportingid)
10. 3.549 15.553 ↓ 1.1 2,791 1

Hash Right Join (cost=362.49..496.50 rows=2,509 width=405) (actual time=10.729..15.553 rows=2,791 loops=1)

  • Hash Cond: (tstr.testspecificationid = t.testspecificationid)
11. 1.299 1.299 ↓ 1.0 5,447 1

Seq Scan on testspectypeofreporting tstr (cost=0.00..88.76 rows=5,376 width=16) (actual time=0.005..1.299 rows=5,447 loops=1)

12. 1.338 10.705 ↓ 1.1 1,309 1

Hash (cost=347.33..347.33 rows=1,213 width=397) (actual time=10.705..10.705 rows=1,309 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 548kB
13. 0.967 9.367 ↓ 1.1 1,309 1

Hash Left Join (cost=98.41..347.33 rows=1,213 width=397) (actual time=1.607..9.367 rows=1,309 loops=1)

  • Hash Cond: (t.testpopulationid = tpp.testpopulationid)
14. 0.986 8.373 ↓ 1.1 1,309 1

Hash Left Join (cost=96.80..330.79 rows=1,213 width=382) (actual time=1.570..8.373 rows=1,309 loops=1)

  • Hash Cond: (t.contentframeworkid = cf.contentframeworkid)
15. 0.898 6.902 ↓ 1.1 1,309 1

Hash Left Join (cost=74.73..292.61 rows=1,213 width=355) (actual time=1.076..6.902 rows=1,309 loops=1)

  • Hash Cond: (t.gradecourseid = gc.gradecourseid)
16. 0.877 5.828 ↓ 1.1 1,309 1

Hash Left Join (cost=62.99..265.59 rows=1,213 width=337) (actual time=0.890..5.828 rows=1,309 loops=1)

  • Hash Cond: (t.contentareaid = ca.contentareaid)
17. 0.866 4.907 ↓ 1.1 1,309 1

Hash Left Join (cost=59.87..247.36 rows=1,213 width=312) (actual time=0.836..4.907 rows=1,309 loops=1)

  • Hash Cond: (t.organizationid = org.organizationid)
18. 0.929 4.020 ↓ 1.1 1,309 1

Hash Left Join (cost=58.33..230.96 rows=1,213 width=294) (actual time=0.804..4.020 rows=1,309 loops=1)

  • Hash Cond: (t.testingprogramid = tp.testingprogramid)
19. 1.060 2.599 ↓ 1.1 1,309 1

Hash Left Join (cost=27.09..183.08 rows=1,213 width=271) (actual time=0.303..2.599 rows=1,309 loops=1)

  • Hash Cond: (t.testformatid = tf.testformatid)
20. 1.259 1.259 ↓ 1.1 1,309 1

Seq Scan on testspecification t (cost=0.00..139.99 rows=1,213 width=247) (actual time=0.013..1.259 rows=1,309 loops=1)

  • Filter: (inuse AND (organizationid = ANY ('{12508,15009}'::bigint[])) AND (approvedstatus = 0))
  • Rows Removed by Filter: 1284
21. 0.019 0.280 ↑ 1.0 36 1

Hash (cost=26.64..26.64 rows=36 width=32) (actual time=0.280..0.280 rows=36 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
22. 0.087 0.261 ↑ 1.0 36 1

Merge Right Join (cost=8.50..26.64 rows=36 width=32) (actual time=0.095..0.261 rows=36 loops=1)

  • Merge Cond: (sys.id = tf.systemrecordid)
23. 0.124 0.124 ↑ 2.2 287 1

Index Scan using systemrecord_pkey on systemrecord sys (cost=0.28..50.71 rows=631 width=14) (actual time=0.010..0.124 rows=287 loops=1)

24. 0.033 0.050 ↑ 1.0 36 1

Sort (cost=2.29..2.38 rows=36 width=26) (actual time=0.042..0.050 rows=36 loops=1)

  • Sort Key: tf.systemrecordid
  • Sort Method: quicksort Memory: 27kB
25. 0.017 0.017 ↑ 1.0 36 1

Seq Scan on testformat tf (cost=0.00..1.36 rows=36 width=26) (actual time=0.003..0.017 rows=36 loops=1)

26. 0.024 0.492 ↑ 1.0 58 1

Hash (cost=30.51..30.51 rows=58 width=31) (actual time=0.492..0.492 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
27. 0.049 0.468 ↑ 1.0 58 1

Hash Left Join (cost=27.20..30.51 rows=58 width=31) (actual time=0.416..0.468 rows=58 loops=1)

  • Hash Cond: (tp.systemrecordid = systp.id)
28. 0.017 0.017 ↑ 1.0 58 1

Seq Scan on testingprogram tp (cost=0.00..2.58 rows=58 width=25) (actual time=0.003..0.017 rows=58 loops=1)

29. 0.214 0.402 ↓ 1.0 637 1

Hash (cost=19.31..19.31 rows=631 width=14) (actual time=0.402..0.402 rows=637 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
30. 0.188 0.188 ↓ 1.0 637 1

Seq Scan on systemrecord systp (cost=0.00..19.31 rows=631 width=14) (actual time=0.002..0.188 rows=637 loops=1)

31. 0.011 0.021 ↓ 1.0 25 1

Hash (cost=1.24..1.24 rows=24 width=26) (actual time=0.021..0.021 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
32. 0.010 0.010 ↓ 1.0 25 1

Seq Scan on organization_ org (cost=0.00..1.24 rows=24 width=26) (actual time=0.003..0.010 rows=25 loops=1)

33. 0.024 0.044 ↓ 1.0 51 1

Hash (cost=2.50..2.50 rows=50 width=25) (actual time=0.044..0.044 rows=51 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
34. 0.020 0.020 ↓ 1.0 51 1

Seq Scan on contentarea ca (cost=0.00..2.50 rows=50 width=25) (actual time=0.003..0.020 rows=51 loops=1)

35. 0.083 0.176 ↓ 1.0 258 1

Hash (cost=8.55..8.55 rows=255 width=18) (actual time=0.176..0.176 rows=258 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
36. 0.093 0.093 ↓ 1.0 258 1

Seq Scan on gradecourse gc (cost=0.00..8.55 rows=255 width=18) (actual time=0.003..0.093 rows=258 loops=1)

37. 0.117 0.485 ↓ 1.1 349 1

Hash (cost=17.95..17.95 rows=330 width=27) (actual time=0.485..0.485 rows=349 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
38. 0.215 0.368 ↓ 1.1 349 1

Hash Left Join (cost=3.53..17.95 rows=330 width=27) (actual time=0.067..0.368 rows=349 loops=1)

  • Hash Cond: (cf.frameworktypeid = ft.frameworktypeid)
39. 0.097 0.097 ↓ 1.1 349 1

Seq Scan on contentframework cf (cost=0.00..10.30 rows=330 width=16) (actual time=0.003..0.097 rows=349 loops=1)

40. 0.029 0.056 ↓ 1.0 71 1

Hash (cost=2.68..2.68 rows=68 width=19) (actual time=0.056..0.056 rows=71 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
41. 0.027 0.027 ↓ 1.0 71 1

Seq Scan on frameworktype ft (cost=0.00..2.68 rows=68 width=19) (actual time=0.003..0.027 rows=71 loops=1)

42. 0.016 0.027 ↑ 1.0 27 1

Hash (cost=1.27..1.27 rows=27 width=23) (actual time=0.027..0.027 rows=27 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
43. 0.011 0.011 ↑ 1.0 27 1

Seq Scan on testpopulation tpp (cost=0.00..1.27 rows=27 width=23) (actual time=0.002..0.011 rows=27 loops=1)

44. 0.028 0.063 ↑ 1.0 73 1

Hash (cost=2.73..2.73 rows=73 width=26) (actual time=0.063..0.063 rows=73 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
45. 0.035 0.035 ↑ 1.0 73 1

Seq Scan on typeofreporting tyrp (cost=0.00..2.73 rows=73 width=26) (actual time=0.007..0.035 rows=73 loops=1)

46. 0.040 0.084 ↑ 1.0 111 1

Hash (cost=4.11..4.11 rows=111 width=21) (actual time=0.084..0.084 rows=111 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
47. 0.044 0.044 ↑ 1.0 111 1

Seq Scan on statementofpurpose stp (cost=0.00..4.11 rows=111 width=21) (actual time=0.004..0.044 rows=111 loops=1)

48. 0.110 0.248 ↓ 1.0 322 1

Hash (cost=10.12..10.12 rows=312 width=17) (actual time=0.248..0.248 rows=322 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
49. 0.138 0.138 ↓ 1.0 322 1

Seq Scan on frameworklevel fl (cost=0.00..10.12 rows=312 width=17) (actual time=0.005..0.138 rows=322 loops=1)

50. 0.011 0.023 ↓ 1.0 29 1

Hash (cost=1.28..1.28 rows=28 width=15) (actual time=0.023..0.023 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
51. 0.012 0.012 ↓ 1.0 29 1

Seq Scan on cognitivetaxonomy ct (cost=0.00..1.28 rows=28 width=15) (actual time=0.003..0.012 rows=29 loops=1)

52. 0.212 0.401 ↓ 1.0 637 1

Hash (cost=19.31..19.31 rows=631 width=23) (actual time=0.401..0.401 rows=637 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
53. 0.189 0.189 ↓ 1.0 637 1

Seq Scan on systemrecord syst (cost=0.00..19.31 rows=631 width=23) (actual time=0.002..0.189 rows=637 loops=1)