explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GPRl

Settings
# exclusive inclusive rows x rows loops node
1. 31.173 901,092.101 ↑ 51,934.0 1 1

Hash Right Join (cost=34,099.17..80,264.19 rows=51,934 width=320) (actual time=788,984.119..901,092.101 rows=1 loops=1)

  • Hash Cond: (cl.caseid = i.caseid)
2.          

Initplan (for Hash Right Join)

3. 0.160 0.160 ↑ 1.0 1 1

Seq Scan on agencies (cost=0.00..1.04 rows=1 width=32) (actual time=0.160..0.160 rows=1 loops=1)

  • Filter: (agenciesid = 1)
  • Rows Removed by Filter: 2
4. 0.154 0.154 ↑ 1.0 1 1

Seq Scan on case_types (cost=0.00..1.31 rows=1 width=8) (actual time=0.151..0.154 rows=1 loops=1)

  • Filter: ((tag)::text = 'Case Report'::text)
  • Rows Removed by Filter: 24
5. 0.142 0.142 ↑ 1.0 1 1

Seq Scan on case_investigation_types (cost=0.00..1.05 rows=1 width=8) (actual time=0.142..0.142 rows=1 loops=1)

  • Filter: ((tag)::text ~~* 'patrol'::text)
  • Rows Removed by Filter: 3
6. 0.001 0.178 ↑ 1.0 1 1

Limit (cost=0.00..3.09 rows=1 width=8) (actual time=0.178..0.178 rows=1 loops=1)

7. 0.177 0.177 ↑ 1.0 1 1

Seq Scan on case_dispositions (cost=0.00..3.09 rows=1 width=8) (actual time=0.177..0.177 rows=1 loops=1)

  • Filter: ((description)::text ~~ 'Closed'::text)
  • Rows Removed by Filter: 54
8. 461,152.720 900,409.918 ↓ 17.7 54,851 1

Nested Loop Left Join (cost=27,592.54..45,499.01 rows=3,102 width=8) (actual time=2,498.676..900,409.918 rows=54,851 loops=1)

  • Join Filter: (cl.personnelid = cr.casepersonnelid)
  • Rows Removed by Join Filter: 5,283,798,212
9. 56.650 2,204.430 ↓ 17.7 54,851 1

Hash Right Join (cost=27,592.54..37,189.54 rows=3,102 width=12) (actual time=2,152.238..2,204.430 rows=54,851 loops=1)

  • Hash Cond: (tmp.serialno = cl.serialno)
10. 68.711 68.711 ↓ 1.6 113,503 1

Seq Scan on tblmasterperson tmp (cost=0.00..9,307.44 rows=68,944 width=36) (actual time=0.026..68.711 rows=113,503 loops=1)

11. 9.279 2,079.069 ↓ 4,394.6 39,551 1

Hash (cost=27,592.43..27,592.43 rows=9 width=40) (actual time=2,079.069..2,079.069 rows=39,551 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,212kB
12. 2,069.790 2,069.790 ↓ 4,394.6 39,551 1

Seq Scan on cncasepersonnel cl (cost=0.00..27,592.43 rows=9 width=40) (actual time=10.399..2,069.790 rows=39,551 loops=1)

  • Filter: ((active = 1) AND (z_rank = 1))
  • Rows Removed by Filter: 56,950
13. 436,450.773 437,052.768 ↓ 19,266.2 96,331 54,851

Materialize (cost=0.00..8,076.82 rows=5 width=4) (actual time=0.005..7.968 rows=96,331 loops=54,851)

14. 601.995 601.995 ↓ 19,266.2 96,331 1

Seq Scan on cncasepersonnelrole cr (cost=0.00..8,076.80 rows=5 width=4) (actual time=168.150..601.995 rows=96,331 loops=1)

  • Filter: ((active = 1) AND (z_rank = 1))
  • Rows Removed by Filter: 15,778
15. 0.006 650.376 ↑ 144.0 1 1

Hash (cost=6,498.34..6,498.34 rows=144 width=320) (actual time=650.376..650.376 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.011 650.370 ↑ 144.0 1 1

Hash Left Join (cost=199.88..6,498.34 rows=144 width=320) (actual time=648.483..650.370 rows=1 loops=1)

  • Hash Cond: (l.postid = other.itemid)
17. 0.014 648.936 ↑ 27.0 1 1

Hash Left Join (cost=99.94..6,393.31 rows=27 width=320) (actual time=647.049..648.936 rows=1 loops=1)

  • Hash Cond: (l.precinctid = zone.itemid)
18. 0.231 647.037 ↑ 5.0 1 1

Nested Loop Left Join (cost=0.00..6,292.43 rows=5 width=320) (actual time=645.150..647.037 rows=1 loops=1)

  • Join Filter: (disposition.itemid = i.casestatusid)
  • Rows Removed by Join Filter: 2,935
19. 2.866 641.163 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.00..6,192.49 rows=1 width=320) (actual time=640.201..641.163 rows=1 loops=1)

  • Join Filter: (l.caseid = i.caseid)
  • Rows Removed by Join Filter: 39,756
20. 448.378 448.378 ↑ 1.0 1 1

Seq Scan on tblcase i (cost=0.00..4,504.55 rows=1 width=152) (actual time=447.779..448.378 rows=1 loops=1)

  • Filter: ((caseno IS NOT NULL) AND (z_rank = 1) AND (caseno = 'WBPD-CR-4712-19'::text))
  • Rows Removed by Filter: 40,392
21. 189.919 189.919 ↓ 484.8 39,757 1

Seq Scan on cncaselocation l (cost=0.00..1,686.91 rows=82 width=172) (actual time=0.150..189.919 rows=39,757 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 5
22. 5.643 5.643 ↓ 2.8 2,936 1

Seq Scan on tblmaintenanceitem disposition (cost=0.00..86.64 rows=1,064 width=8) (actual time=0.004..5.643 rows=2,936 loops=1)

23. 0.425 1.885 ↓ 2.8 2,936 1

Hash (cost=86.64..86.64 rows=1,064 width=8) (actual time=1.885..1.885 rows=2,936 loops=1)

  • Buckets: 4,096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 136kB
24. 1.460 1.460 ↓ 2.8 2,936 1

Seq Scan on tblmaintenanceitem zone (cost=0.00..86.64 rows=1,064 width=8) (actual time=0.002..1.460 rows=2,936 loops=1)

25. 0.415 1.423 ↓ 2.8 2,936 1

Hash (cost=86.64..86.64 rows=1,064 width=8) (actual time=1.423..1.423 rows=2,936 loops=1)

  • Buckets: 4,096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 136kB
26. 1.008 1.008 ↓ 2.8 2,936 1

Seq Scan on tblmaintenanceitem other (cost=0.00..86.64 rows=1,064 width=8) (actual time=0.002..1.008 rows=2,936 loops=1)

Planning time : 414.726 ms
Execution time : 901,092.718 ms