explain.depesz.com

PostgreSQL's explain analyze made readable

Result: usve

Settings
# exclusive inclusive rows x rows loops node
1. 4,197.351 7,402.799 ↓ 49,629.3 148,888 1

Nested Loop Semi Join (cost=356.10..4,736.67 rows=3 width=470) (actual time=15.183..7,402.799 rows=148,888 loops=1)

  • Join Filter: (gd.accrual_gl_account_id = gat_temp.gl_account_id)
  • Rows Removed by Join Filter: 11814248
2. 144.184 1,567.680 ↓ 49,629.3 148,888 1

Nested Loop Left Join (cost=356.10..4,640.48 rows=3 width=593) (actual time=15.013..1,567.680 rows=148,888 loops=1)

  • Join Filter: false
3. 143.928 1,423.496 ↓ 49,629.3 148,888 1

Nested Loop Left Join (cost=356.10..4,640.45 rows=3 width=566) (actual time=15.011..1,423.496 rows=148,888 loops=1)

  • Join Filter: false
4. 137.495 1,279.568 ↓ 49,629.3 148,888 1

Nested Loop Left Join (cost=356.10..4,640.42 rows=3 width=556) (actual time=15.010..1,279.568 rows=148,888 loops=1)

  • Join Filter: false
5. 140.952 1,142.073 ↓ 49,629.3 148,888 1

Nested Loop Left Join (cost=356.10..4,640.39 rows=3 width=546) (actual time=15.008..1,142.073 rows=148,888 loops=1)

  • Join Filter: false
6. 140.496 1,001.121 ↓ 49,629.3 148,888 1

Nested Loop Left Join (cost=356.10..4,640.36 rows=3 width=511) (actual time=15.007..1,001.121 rows=148,888 loops=1)

  • Join Filter: false
7. 138.972 860.625 ↓ 49,629.3 148,888 1

Nested Loop Left Join (cost=356.10..4,640.33 rows=3 width=503) (actual time=15.005..860.625 rows=148,888 loops=1)

  • Join Filter: false
8. 138.355 721.653 ↓ 49,629.3 148,888 1

Nested Loop Left Join (cost=356.10..4,640.30 rows=3 width=503) (actual time=15.004..721.653 rows=148,888 loops=1)

  • Join Filter: false
9. 142.544 583.298 ↓ 49,629.3 148,888 1

Nested Loop Left Join (cost=356.10..4,640.27 rows=3 width=499) (actual time=15.002..583.298 rows=148,888 loops=1)

  • Join Filter: false
10. 220.126 440.754 ↓ 49,629.3 148,888 1

Nested Loop Left Join (cost=356.10..4,640.24 rows=3 width=462) (actual time=15.000..440.754 rows=148,888 loops=1)

  • Join Filter: ((pb.cid = p.cid) AND (pb.property_id = p.id))
11. 121.278 220.628 ↓ 49,629.3 148,888 1

Nested Loop Left Join (cost=345.94..4,626.01 rows=3 width=462) (actual time=14.965..220.628 rows=148,888 loops=1)

  • Join Filter: ((pu.cid = p.cid) AND (pu.property_id = p.id))
12. 0.985 71.182 ↓ 335.3 1,006 1

Nested Loop Left Join (cost=295.05..4,563.24 rows=3 width=457) (actual time=14.675..71.182 rows=1,006 loops=1)

  • Join Filter: false
13. 0.964 70.197 ↓ 335.3 1,006 1

Nested Loop Left Join (cost=295.05..4,563.21 rows=3 width=433) (actual time=14.673..70.197 rows=1,006 loops=1)

  • Join Filter: false
14. 1.080 69.233 ↓ 335.3 1,006 1

Nested Loop Left Join (cost=295.05..4,563.18 rows=3 width=409) (actual time=14.671..69.233 rows=1,006 loops=1)

  • Join Filter: false
15. 1.292 68.153 ↓ 335.3 1,006 1

Nested Loop Left Join (cost=295.05..4,563.15 rows=3 width=405) (actual time=14.670..68.153 rows=1,006 loops=1)

16. 2.256 63.843 ↓ 335.3 1,006 1

Nested Loop Left Join (cost=294.63..4,561.45 rows=3 width=385) (actual time=14.650..63.843 rows=1,006 loops=1)

17. 1.728 56.557 ↓ 335.3 1,006 1

Nested Loop Left Join (cost=294.21..4,537.36 rows=3 width=336) (actual time=14.629..56.557 rows=1,006 loops=1)

18. 2.468 52.817 ↓ 335.3 1,006 1

Nested Loop Left Join (cost=293.92..4,536.38 rows=3 width=334) (actual time=14.615..52.817 rows=1,006 loops=1)

  • Join Filter: (gh.cid = art1.cid)
19. 4.257 46.325 ↓ 335.3 1,006 1

Nested Loop Left Join (cost=293.49..4,528.10 rows=3 width=290) (actual time=14.601..46.325 rows=1,006 loops=1)

  • Join Filter: (gh.gl_transaction_type_id = ANY ('{7,8,9,17}'::integer[]))
  • Rows Removed by Join Filter: 382
20. 2.026 33.014 ↓ 335.3 1,006 1

Nested Loop Left Join (cost=56.85..3,806.07 rows=3 width=274) (actual time=14.574..33.014 rows=1,006 loops=1)

  • Join Filter: (gh.gl_transaction_type_id = ANY ('{13,14,18,23,24}'::integer[]))
21. 9.913 25.958 ↓ 335.3 1,006 1

Nested Loop (cost=56.41..3,793.52 rows=3 width=266) (actual time=14.555..25.958 rows=1,006 loops=1)

  • Join Filter: (gh.gl_transaction_type_id = gtty.id)
  • Rows Removed by Join Filter: 26156
22. 0.088 0.088 ↑ 1.0 27 1

Index Scan using pk_gl_transaction_types on gl_transaction_types gtty (cost=0.14..20.65 rows=27 width=24) (actual time=0.012..0.088 rows=27 loops=1)

23. 5.218 15.957 ↓ 335.3 1,006 27

Materialize (cost=56.28..3,771.66 rows=3 width=242) (actual time=0.055..0.591 rows=1,006 loops=27)

24. 0.512 10.739 ↓ 335.3 1,006 1

Nested Loop (cost=56.28..3,771.65 rows=3 width=242) (actual time=1.475..10.739 rows=1,006 loops=1)

25. 0.023 0.023 ↑ 1.0 1 1

Index Scan using idx_properties_id on properties p (cost=0.28..8.30 rows=1 width=33) (actual time=0.022..0.023 rows=1 loops=1)

  • Index Cond: (id = 2458)
  • Filter: (cid = 224)
26. 1.591 10.204 ↓ 335.3 1,006 1

Nested Loop (cost=56.00..3,763.32 rows=3 width=213) (actual time=1.451..10.204 rows=1,006 loops=1)

27. 1.175 4.589 ↓ 3.6 1,006 1

Hash Join (cost=55.56..1,380.67 rows=282 width=117) (actual time=1.427..4.589 rows=1,006 loops=1)

  • Hash Cond: (gd.accrual_gl_account_id = gat.gl_account_id)
28. 2.050 2.050 ↓ 3.4 1,016 1

Index Scan using idx_gl_details_cid_property_id_post_month_lease_id_reference_id on gl_details gd (cost=0.56..1,321.73 rows=299 width=73) (actual time=0.040..2.050 rows=1,016 loops=1)

  • Index Cond: ((cid = 224) AND (property_id = 2458) AND (post_month >= '2019-06-01'::date) AND (post_month <= '2019-06-01'::date) AND (gl_transaction_type_id = ANY ('{13,14,18,23,24,7,8,9,17}'::integer[])))
29. 0.531 1.364 ↑ 1.0 1,160 1

Hash (cost=40.50..40.50 rows=1,160 width=48) (actual time=1.364..1.364 rows=1,160 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 110kB
30. 0.833 0.833 ↑ 1.0 1,160 1

Seq Scan on gat_temp gat (cost=0.00..40.50 rows=1,160 width=48) (actual time=0.013..0.833 rows=1,160 loops=1)

  • Filter: (cid = 224)
31. 4.024 4.024 ↑ 1.0 1 1,006

Index Scan using idx_gl_headers_id on gl_headers gh (cost=0.43..8.45 rows=1 width=100) (actual time=0.004..0.004 rows=1 loops=1,006)

  • Index Cond: (id = gd.gl_header_id)
  • Filter: ((NOT is_template) AND (cid = 224) AND (gl_book_id = 132) AND (gl_header_status_type_id <> ALL ('{2,4,5,6}'::integer[])))
32. 5.030 5.030 ↓ 0.0 0 1,006

Index Scan using pk_ar_allocations on ar_allocations ara (cost=0.43..4.17 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=1,006)

  • Index Cond: ((cid = gh.cid) AND (cid = 224) AND (id = gh.reference_id))
33. 2.012 9.054 ↑ 1.0 1 1,006

Bitmap Heap Scan on ar_transactions art (cost=236.64..240.66 rows=1 width=20) (actual time=0.009..0.009 rows=1 loops=1,006)

  • Recheck Cond: (((cid = 224) AND (id = gh.reference_id)) OR ((cid = 224) AND (id = ara.credit_ar_transaction_id)))
  • Filter: (gh.cid = cid)
  • Heap Blocks: exact=1006
34. 2.012 7.042 ↓ 0.0 0 1,006

BitmapOr (cost=236.64..236.64 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=1,006)

35. 4.024 4.024 ↑ 1.0 1 1,006

Bitmap Index Scan on pk_ar_transactions (cost=0.00..1.37 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1,006)

  • Index Cond: ((cid = 224) AND (id = gh.reference_id))
36. 1.006 1.006 ↓ 0.0 0 1,006

Bitmap Index Scan on pk_ar_transactions (cost=0.00..0.67 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1,006)

  • Index Cond: ((cid = 224) AND (id = ara.credit_ar_transaction_id))
37. 4.024 4.024 ↑ 1.0 1 1,006

Index Scan using pk_ar_transactions on ar_transactions art1 (cost=0.43..2.75 rows=1 width=52) (actual time=0.004..0.004 rows=1 loops=1,006)

  • Index Cond: ((cid = 224) AND (id = COALESCE(ara.charge_ar_transaction_id, art.id)))
38. 2.012 2.012 ↑ 1.0 1 1,006

Index Scan using idx_ar_codes on ar_codes ac (cost=0.29..0.31 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=1,006)

  • Index Cond: (id = art.ar_code_id)
  • Filter: ((cid = 224) AND (cid = art.cid))
39. 5.030 5.030 ↑ 1.0 1 1,006

Index Scan using pk_cached_leases on cached_leases cl (cost=0.42..8.03 rows=1 width=61) (actual time=0.005..0.005 rows=1 loops=1,006)

  • Index Cond: ((cid = gd.cid) AND (cid = 224) AND (id = gd.lease_id))
40. 3.018 3.018 ↓ 0.0 0 1,006

Index Scan using idx_ar_deposits_id on ar_deposits ard (cost=0.42..0.56 rows=1 width=36) (actual time=0.003..0.003 rows=0 loops=1,006)

  • Index Cond: (gh.reference_id = id)
  • Filter: ((cid = 224) AND (gh.cid = cid) AND (gh.gl_transaction_type_id = gl_transaction_type_id))
41. 0.000 0.000 ↓ 0.0 0 1,006

Result (cost=0.00..0.00 rows=0 width=4) (actual time=0.000..0.000 rows=0 loops=1,006)

  • One-Time Filter: false
42. 0.000 0.000 ↓ 0.0 0 1,006

Result (cost=0.00..0.00 rows=0 width=24) (actual time=0.000..0.000 rows=0 loops=1,006)

  • One-Time Filter: false
43. 0.000 0.000 ↓ 0.0 0 1,006

Result (cost=0.00..0.00 rows=0 width=32) (actual time=0.000..0.000 rows=0 loops=1,006)

  • One-Time Filter: false
44. 27.619 28.168 ↓ 49.3 148 1,006

Materialize (cost=50.89..62.62 rows=3 width=13) (actual time=0.000..0.028 rows=148 loops=1,006)

45. 0.277 0.549 ↓ 49.3 148 1

Bitmap Heap Scan on property_units pu (cost=50.89..62.61 rows=3 width=13) (actual time=0.284..0.549 rows=148 loops=1)

  • Recheck Cond: ((property_id = 2458) AND (cid = 224))
  • Heap Blocks: exact=71
46. 0.007 0.272 ↓ 0.0 0 1

BitmapAnd (cost=50.89..50.89 rows=3 width=0) (actual time=0.272..0.272 rows=0 loops=1)

47. 0.027 0.027 ↓ 1.4 148 1

Bitmap Index Scan on idx_property_units_property_id (cost=0.00..5.10 rows=107 width=0) (actual time=0.027..0.027 rows=148 loops=1)

  • Index Cond: (property_id = 2458)
48. 0.238 0.238 ↓ 1.1 2,467 1

Bitmap Index Scan on pk_property_units (cost=0.00..45.54 rows=2,300 width=0) (actual time=0.238..0.238 rows=2,467 loops=1)

  • Index Cond: (cid = 224)
49. 0.000 0.000 ↑ 1.0 1 148,888

Materialize (cost=10.16..14.18 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=148,888)

50. 0.004 0.029 ↑ 1.0 1 1

Bitmap Heap Scan on property_buildings pb (cost=10.16..14.18 rows=1 width=12) (actual time=0.029..0.029 rows=1 loops=1)

  • Recheck Cond: ((property_id = 2458) AND (cid = 224))
  • Heap Blocks: exact=1
51. 0.000 0.025 ↓ 0.0 0 1

BitmapAnd (cost=10.16..10.16 rows=1 width=0) (actual time=0.025..0.025 rows=0 loops=1)

52. 0.007 0.007 ↑ 4.0 1 1

Bitmap Index Scan on idx_property_buildings_property_id (cost=0.00..4.31 rows=4 width=0) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (property_id = 2458)
53. 0.018 0.018 ↓ 1.0 177 1

Bitmap Index Scan on pk_property_buildings (cost=0.00..5.60 rows=176 width=0) (actual time=0.018..0.018 rows=177 loops=1)

  • Index Cond: (cid = 224)
54. 0.000 0.000 ↓ 0.0 0 148,888

Result (cost=0.00..0.00 rows=0 width=37) (actual time=0.000..0.000 rows=0 loops=148,888)

  • One-Time Filter: false
55. 0.000 0.000 ↓ 0.0 0 148,888

Result (cost=0.00..0.00 rows=0 width=4) (actual time=0.000..0.000 rows=0 loops=148,888)

  • One-Time Filter: false
56. 0.000 0.000 ↓ 0.0 0 148,888

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=148,888)

  • One-Time Filter: false
57. 0.000 0.000 ↓ 0.0 0 148,888

Result (cost=0.00..0.00 rows=0 width=8) (actual time=0.000..0.000 rows=0 loops=148,888)

  • One-Time Filter: false
58. 0.000 0.000 ↓ 0.0 0 148,888

Result (cost=0.00..0.00 rows=0 width=35) (actual time=0.000..0.000 rows=0 loops=148,888)

  • One-Time Filter: false
59. 0.000 0.000 ↓ 0.0 0 148,888

Result (cost=0.00..0.00 rows=0 width=10) (actual time=0.000..0.000 rows=0 loops=148,888)

  • One-Time Filter: false
60. 0.000 0.000 ↓ 0.0 0 148,888

Result (cost=0.00..0.00 rows=0 width=10) (actual time=0.000..0.000 rows=0 loops=148,888)

  • One-Time Filter: false
61. 0.000 0.000 ↓ 0.0 0 148,888

Result (cost=0.00..0.00 rows=0 width=27) (actual time=0.000..0.000 rows=0 loops=148,888)

  • One-Time Filter: false
62. 1,637.612 1,637.768 ↑ 14.5 80 148,888

Materialize (cost=0.00..43.40 rows=1,160 width=4) (actual time=0.000..0.011 rows=80 loops=148,888)

63. 0.156 0.156 ↑ 2.5 465 1

Seq Scan on gat_temp (cost=0.00..37.60 rows=1,160 width=4) (actual time=0.006..0.156 rows=465 loops=1)

Planning time : 19.365 ms