explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dLpf

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 3,009.918 ↑ 1.0 1 1

Limit (cost=52,952.90..52,955.96 rows=1 width=7,122) (actual time=3,009.918..3,009.918 rows=1 loops=1)

2.          

CTE adultchargereason

3. 59.810 179.745 ↓ 172.5 34,496 1

HashAggregate (cost=26,499.62..26,502.12 rows=200 width=36) (actual time=166.675..179.745 rows=34,496 loops=1)

  • Group Key: c.bookid
4. 119.935 119.935 ↑ 8.0 73,052 1

Seq Scan on jamin_charge c (cost=0.00..23,585.75 rows=582,775 width=36) (actual time=1.045..119.935 rows=73,052 loops=1)

5.          

CTE current_cell

6. 0.002 35.510 ↓ 0.0 0 1

WindowAgg (cost=16,587.95..19,643.06 rows=111,095 width=36) (actual time=35.510..35.510 rows=0 loops=1)

7. 0.015 35.508 ↓ 0.0 0 1

Sort (cost=16,587.95..16,865.69 rows=111,095 width=36) (actual time=35.508..35.508 rows=0 loops=1)

  • Sort Key: jamin_bclass.bookid, jamin_bclass.cdate DESC, jamin_bclass.ctime DESC, jamin_bclass.id DESC, jamin_bclass.jamin_bclass_zid
  • Sort Method: quicksort Memory: 25kB
8. 5.116 35.493 ↓ 0.0 0 1

Hash Join (cost=12.91..4,239.39 rows=111,095 width=36) (actual time=35.493..35.493 rows=0 loops=1)

  • Hash Cond: (jamin_bclass.z_unit_custom_jail_locations = jail_locations.jail_locationsid)
9. 30.267 30.267 ↑ 1.0 111,821 1

Seq Scan on jamin_bclass (cost=0.00..2,696.21 rows=111,821 width=36) (actual time=0.011..30.267 rows=111,821 loops=1)

10. 0.025 0.110 ↑ 1.3 231 1

Hash (cost=9.08..9.08 rows=306 width=8) (actual time=0.110..0.110 rows=231 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
11. 0.085 0.085 ↑ 1.3 231 1

Seq Scan on jail_locations (cost=0.00..9.08 rows=306 width=8) (actual time=0.011..0.085 rows=231 loops=1)

  • Filter: (jail_cell_blocksid IS NOT NULL)
  • Rows Removed by Filter: 77
12.          

CTE one_release

13. 14.540 53.002 ↑ 1.7 28,655 1

WindowAgg (cost=5,433.31..6,807.71 rows=49,978 width=28) (actual time=34.177..53.002 rows=28,655 loops=1)

14. 22.126 38.462 ↑ 1.7 28,655 1

Sort (cost=5,433.31..5,558.26 rows=49,978 width=28) (actual time=34.171..38.462 rows=28,655 loops=1)

  • Sort Key: jamin_releas.bookid, jamin_releas.jailid, jamin_releas.reldate, jamin_releas.reltime, jamin_releas.jamin_releas_zid
  • Sort Method: quicksort Memory: 5,081kB
15. 16.336 16.336 ↑ 1.0 49,978 1

Seq Scan on jamin_releas (cost=0.00..1,532.78 rows=49,978 width=28) (actual time=0.010..16.336 rows=49,978 loops=1)

16. 120.385 3,009.917 ↑ 431,923,245,438,400.0 1 1

Nested Loop Left Join (cost=0.00..1,321,456,827,077,708.50 rows=431,923,245,438,400 width=7,122) (actual time=3,009.917..3,009.917 rows=1 loops=1)

  • Join Filter: (a.agenciesid = n.agenciesid)
  • Rows Removed by Join Filter: 1,855,647
17. 0.029 1,775.294 ↑ 3,808,511,113,997.0 1 1

Nested Loop Left Join (cost=0.00..25,687,090,633,214.77 rows=3,808,511,113,997 width=6,961) (actual time=1,775.294..1,775.294 rows=1 loops=1)

  • Join Filter: (r.reloff = r_off.badgeno)
  • Rows Removed by Join Filter: 55
18. 0.004 1,775.190 ↑ 1,963,150,063,227.0 1 1

Nested Loop Left Join (cost=0.00..14,261,557,265,223.77 rows=1,963,150,063,227 width=6,729) (actual time=1,775.190..1,775.190 rows=1 loops=1)

  • Join Filter: (cc.bookid = jb.bookid)
19. 262.101 1,739.675 ↑ 707,441,464,226.0 1 1

Nested Loop Left Join (cost=0.00..5,427,381,978,207.51 rows=707,441,464,226 width=6,713) (actual time=1,739.675..1,739.675 rows=1 loops=1)

  • Join Filter: (a_bill.agenciesid = agen_name.agenciesid)
  • Rows Removed by Join Filter: 4,184,827
20. 0.008 803.240 ↑ 31,034,280.0 1 1

Nested Loop Left Join (cost=0.00..1,699,693,471.41 rows=31,034,280 width=6,552) (actual time=803.240..803.240 rows=1 loops=1)

  • Join Filter: (agen_bill.z_agencyname_agencies = a_bill.agenciesid)
  • Rows Removed by Join Filter: 102
21. 0.003 803.160 ↑ 31,034,280.0 1 1

Nested Loop Left Join (cost=0.00..1,652,211,012.74 rows=31,034,280 width=5,697) (actual time=803.160..803.160 rows=1 loops=1)

  • Join Filter: (jb.billagid = agen_bill.agencycode)
  • Rows Removed by Join Filter: 2
22. 0.016 803.152 ↑ 31,034,280.0 1 1

Nested Loop Left Join (cost=0.00..1,599,142,389.51 rows=31,034,280 width=5,433) (actual time=803.152..803.152 rows=1 loops=1)

  • Join Filter: (agen.z_agencyname_agencies = a.agenciesid)
  • Rows Removed by Join Filter: 102
23. 0.007 803.026 ↑ 31,034,280.0 1 1

Nested Loop Left Join (cost=0.00..1,551,659,930.84 rows=31,034,280 width=4,578) (actual time=803.026..803.026 rows=1 loops=1)

  • Join Filter: (jb.arragid = agen.agencycode)
  • Rows Removed by Join Filter: 33
24. 0.013 802.973 ↑ 31,034,280.0 1 1

Nested Loop Left Join (cost=0.00..1,498,591,307.61 rows=31,034,280 width=4,314) (actual time=802.973..802.973 rows=1 loops=1)

  • Join Filter: (b_off.userid = jb.bookby)
  • Rows Removed by Join Filter: 112
25. 0.995 802.833 ↑ 18,472,786.0 1 1

Nested Loop (cost=0.00..1,405,488,455.97 rows=18,472,786 width=4,133) (actual time=802.833..802.833 rows=1 loops=1)

  • Join Filter: (jb.jailid = ji.jailid)
  • Rows Removed by Join Filter: 12,345
26. 2.019 769.268 ↑ 22,786.0 1 1

Nested Loop Left Join (cost=0.00..1,225,770,830.70 rows=22,786 width=2,470) (actual time=769.268..769.268 rows=1 loops=1)

  • Join Filter: (orel.jamin_releas_zid = r.jamin_releas_zid)
  • Rows Removed by Join Filter: 24,274
27. 2.392 759.155 ↑ 22,786.0 1 1

Nested Loop Left Join (cost=0.00..1,176,609,921.77 rows=22,786 width=2,093) (actual time=759.155..759.155 rows=1 loops=1)

  • Join Filter: ((jb.jailid = orel.jailid) AND (jb.bookid = orel.bookid))
  • Rows Removed by Join Filter: 28,641
28. 0.011 694.067 ↑ 22,786.0 1 1

Nested Loop Left Join (cost=0.00..1,176,466,387.27 rows=22,786 width=2,069) (actual time=694.067..694.067 rows=1 loops=1)

  • Join Filter: (btrim(upper(jb.custat)) = btrim(upper(cust_stat.chrval)))
  • Rows Removed by Join Filter: 1
29. 2.884 693.813 ↑ 22,786.0 1 1

Nested Loop Left Join (cost=0.00..1,176,461,793.00 rows=22,786 width=1,869) (actual time=693.813..693.813 rows=1 loops=1)

  • Join Filter: (jb.bookid = acr.bookid)
  • Rows Removed by Join Filter: 34,495
30. 3.427 503.206 ↑ 22,786.0 1 1

Nested Loop (cost=0.00..1,176,359,254.00 rows=22,786 width=1,833) (actual time=503.206..503.206 rows=1 loops=1)

  • Join Filter: (jb.jamin_book_zid = pt_pn.convertedid)
  • Rows Removed by Join Filter: 45,744
31. 403.926 403.926 ↑ 52,448.0 1 1

Seq Scan on pt_people_names pt_pn (cost=0.00..51,445.28 rows=52,448 width=310) (actual time=403.926..403.926 rows=1 loops=1)

  • Filter: (wtrun = 205)
  • Rows Removed by Filter: 1,809,781
32. 95.853 95.853 ↑ 10.1 45,745 1

Seq Scan on jamin_book jb (cost=0.00..16,658.59 rows=461,559 width=1,523) (actual time=56.621..95.853 rows=45,745 loops=1)

33. 187.723 187.723 ↓ 172.5 34,496 1

CTE Scan on adultchargereason acr (cost=0.00..4.00 rows=200 width=36) (actual time=166.677..187.723 rows=34,496 loops=1)

34. 0.002 0.243 ↑ 4.0 2 1

Materialize (cost=0.00..37.09 rows=8 width=200) (actual time=0.121..0.243 rows=2 loops=1)

35. 0.241 0.241 ↑ 4.0 2 1

Seq Scan on jamin_sys_tab cust_stat (cost=0.00..37.05 rows=8 width=200) (actual time=0.119..0.241 rows=2 loops=1)

  • Filter: (tab = 'icustat'::text)
  • Rows Removed by Filter: 1,434
36. 62.696 62.696 ↓ 114.6 28,642 1

CTE Scan on one_release orel (cost=0.00..1,124.50 rows=250 width=24) (actual time=34.183..62.696 rows=28,642 loops=1)

  • Filter: (ranking = 1)
  • Rows Removed by Filter: 13
37. 8.094 8.094 ↑ 2.1 24,275 1

Seq Scan on jamin_releas r (cost=0.00..1,532.78 rows=49,978 width=377) (actual time=0.006..8.094 rows=24,275 loops=1)

38. 32.570 32.570 ↑ 13.1 12,346 1

Seq Scan on jamin_inmate ji (cost=0.00..5,860.42 rows=162,142 width=1,663) (actual time=0.029..32.570 rows=12,346 loops=1)

39. 0.061 0.127 ↑ 3.0 112 1

Materialize (cost=0.00..11.04 rows=336 width=181) (actual time=0.020..0.127 rows=112 loops=1)

40. 0.066 0.066 ↑ 3.0 112 1

Seq Scan on tdt_core_user b_off (cost=0.00..9.36 rows=336 width=181) (actual time=0.015..0.066 rows=112 loops=1)

41. 0.009 0.046 ↑ 3.4 34 1

Materialize (cost=0.00..4.71 rows=114 width=264) (actual time=0.014..0.046 rows=34 loops=1)

42. 0.037 0.037 ↑ 3.4 34 1

Seq Scan on tdt_core_agency agen (cost=0.00..4.14 rows=114 width=264) (actual time=0.013..0.037 rows=34 loops=1)

43. 0.052 0.110 ↑ 1.0 102 1

Materialize (cost=0.00..10.53 rows=102 width=855) (actual time=0.012..0.110 rows=102 loops=1)

44. 0.058 0.058 ↑ 1.0 102 1

Seq Scan on agencies a (cost=0.00..10.02 rows=102 width=855) (actual time=0.010..0.058 rows=102 loops=1)

45. 0.003 0.005 ↑ 38.0 3 1

Materialize (cost=0.00..4.71 rows=114 width=264) (actual time=0.004..0.005 rows=3 loops=1)

46. 0.002 0.002 ↑ 38.0 3 1

Seq Scan on tdt_core_agency agen_bill (cost=0.00..4.14 rows=114 width=264) (actual time=0.002..0.002 rows=3 loops=1)

47. 0.056 0.072 ↑ 1.0 102 1

Materialize (cost=0.00..10.53 rows=102 width=855) (actual time=0.007..0.072 rows=102 loops=1)

48. 0.016 0.016 ↑ 1.0 102 1

Seq Scan on agencies a_bill (cost=0.00..10.02 rows=102 width=855) (actual time=0.001..0.016 rows=102 loops=1)

49. 674.334 674.334 ↑ 1.1 4,184,827 1

Seq Scan on names agen_name (cost=0.00..117,839.97 rows=4,559,097 width=161) (actual time=0.315..674.334 rows=4,184,827 loops=1)

50. 35.511 35.511 ↓ 0.0 0 1

CTE Scan on current_cell cc (cost=0.00..2,499.64 rows=555 width=16) (actual time=35.511..35.511 rows=0 loops=1)

  • Filter: (ranking = 1)
51. 0.035 0.075 ↑ 6.9 56 1

Materialize (cost=0.00..10.82 rows=388 width=232) (actual time=0.029..0.075 rows=56 loops=1)

52. 0.040 0.040 ↑ 6.9 56 1

Seq Scan on tdt_core_officer r_off (cost=0.00..8.88 rows=388 width=232) (actual time=0.026..0.040 rows=56 loops=1)

53. 351.993 1,114.238 ↓ 81.8 1,855,647 1

Materialize (cost=0.00..129,351.12 rows=22,682 width=161) (actual time=0.273..1,114.238 rows=1,855,647 loops=1)

54. 762.245 762.245 ↓ 81.8 1,855,647 1

Seq Scan on names n (cost=0.00..129,237.71 rows=22,682 width=161) (actual time=0.272..762.245 rows=1,855,647 loops=1)

  • Filter: ((COALESCE(peopleid, businessesid, unknownsid) IS NOT NULL) AND (currentid = namesid))
  • Rows Removed by Filter: 2,329,180
Planning time : 15.989 ms
Execution time : 3,048.126 ms