explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KaiN

Settings
# exclusive inclusive rows x rows loops node
1. 0.112 18,709.275 ↓ 43.0 43 1

GroupAggregate (cost=1,869.65..1,871.74 rows=1 width=40) (actual time=18,708.930..18,709.275 rows=43 loops=1)

  • Group Key: pft.fee_template_id, pft.property_id
2.          

CTE calculated_cash_values

3. 0.900 18,707.786 ↓ 43.0 43 1

Nested Loop Left Join (cost=1,808.09..1,864.78 rows=1 width=168) (actual time=493.295..18,707.786 rows=43 loops=1)

4. 0.685 18,251.516 ↓ 43.0 43 1

Nested Loop Left Join (cost=1,806.81..1,857.73 rows=1 width=168) (actual time=434.914..18,251.516 rows=43 loops=1)

5. 0.556 18,182.246 ↓ 43.0 43 1

Nested Loop Left Join (cost=1,801.77..1,845.36 rows=1 width=104) (actual time=427.739..18,182.246 rows=43 loops=1)

6. 0.424 11,712.598 ↓ 43.0 43 1

Nested Loop Left Join (cost=900.37..943.91 rows=1 width=72) (actual time=282.319..11,712.598 rows=43 loops=1)

7. 0.460 82.480 ↓ 43.0 43 1

Nested Loop Left Join (cost=0.85..44.33 rows=1 width=40) (actual time=13.844..82.480 rows=43 loops=1)

8. 1.438 1.438 ↓ 43.0 43 1

Index Only Scan using pk_properties on properties p (cost=0.29..41.70 rows=1 width=8) (actual time=0.064..1.438 rows=43 loops=1)

  • Index Cond: ((cid = 15489) AND (id = ANY ('{622942,622943,622944,622945,622946,622961,622947,622971,622948,622949,622972,622950,622951,622952,622973,622953,622954,622955,622956,622974,622957,622975,622958,622976,622959,622960,622977,622962,622963,622964,622965,622966,622967,622968,622979,622969,622970,622980,679353,640991,676485,622981,640992}'::integer[])))
  • Heap Fetches: 43
9. 0.387 80.582 ↑ 1.0 1 43

Subquery Scan on at_receipts (cost=0.56..2.62 rows=1 width=40) (actual time=1.874..1.874 rows=1 loops=43)

  • Filter: ((at_receipts.cid = p.cid) AND (at_receipts.property_id = p.id))
10. 4.472 80.195 ↑ 1.0 1 43

GroupAggregate (cost=0.56..2.61 rows=1 width=40) (actual time=1.865..1.865 rows=1 loops=43)

  • Group Key: at.cid, at.property_id
11. 2.365 75.723 ↓ 188.0 188 43

Result (cost=0.56..2.59 rows=1 width=13) (actual time=0.057..1.761 rows=188 loops=43)

  • One-Time Filter: (p.cid = 15489)
12. 73.358 73.358 ↓ 188.0 188 43

Index Scan using idx_ar_transactions_periods_join on ar_transactions at (cost=0.56..2.59 rows=1 width=13) (actual time=0.046..1.706 rows=188 loops=43)

  • Index Cond: ((cid = 15489) AND (property_id = p.id) AND (post_month = '2019-03-01'::date))
  • Filter: ((NOT is_temporary) AND (ar_code_type_id = 1))
  • Rows Removed by Filter: 439
13. 0.473 11,629.694 ↑ 1.0 1 43

Subquery Scan on aa_credit (cost=899.53..899.57 rows=1 width=40) (actual time=270.458..270.458 rows=1 loops=43)

  • Filter: ((aa_credit.cid = p.cid) AND (aa_credit.property_id = p.id))
  • Rows Removed by Filter: 17
14. 13.244 11,629.221 ↓ 18.0 18 43

GroupAggregate (cost=899.53..899.56 rows=1 width=40) (actual time=270.378..270.447 rows=18 loops=43)

  • Group Key: at_1.cid, at_1.property_id
15. 6.837 11,615.977 ↓ 91.0 91 43

Sort (cost=899.53..899.53 rows=1 width=12) (actual time=270.127..270.139 rows=91 loops=43)

  • Sort Key: at_1.property_id
  • Sort Method: quicksort Memory: 31kB
16. 1.978 11,609.140 ↓ 134.0 134 43

Result (cost=1.43..899.52 rows=1 width=12) (actual time=0.069..269.980 rows=134 loops=43)

  • One-Time Filter: (p.cid = 15489)
17. 381.711 11,607.162 ↓ 134.0 134 43

Nested Loop (cost=1.43..899.52 rows=1 width=12) (actual time=0.068..269.934 rows=134 loops=43)

18. 665.812 6,737.541 ↓ 20,874.0 20,874 43

Nested Loop (cost=0.99..897.05 rows=1 width=12) (actual time=0.049..156.687 rows=20,874 loops=43)

19. 1,583.819 1,583.819 ↓ 80.3 20,874 43

Index Only Scan using idx_ar_allocations_ar_trans_join on ar_allocations aa (cost=0.56..262.05 rows=260 width=16) (actual time=0.029..36.833 rows=20,874 loops=43)

  • Index Cond: ((cid = 15489) AND (post_month = '2019-03-01'::date))
  • Heap Fetches: 897582
20. 4,487.910 4,487.910 ↑ 1.0 1 897,582

Index Only Scan using idx_ar_transactions_cache_charge_master_offset on ar_transactions at_offset (cost=0.43..2.44 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=897,582)

  • Index Cond: ((cid = 15489) AND (id = aa.credit_ar_transaction_id))
  • Heap Fetches: 897582
21. 4,487.910 4,487.910 ↓ 0.0 0 897,582

Index Scan using idx_ar_transactions_cache_credit_master_offset on ar_transactions at_1 (cost=0.43..2.46 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=897,582)

  • Index Cond: ((cid = 15489) AND (id = aa.charge_ar_transaction_id))
  • Filter: ((property_id = property_id) AND (ar_code_type_id = 7))
  • Rows Removed by Filter: 1
22. 0.602 6,469.092 ↓ 0.0 0 43

Subquery Scan on aa_charge (cost=901.40..901.44 rows=1 width=40) (actual time=150.444..150.444 rows=0 loops=43)

  • Filter: ((aa_charge.cid = p.cid) AND (aa_charge.property_id = p.id))
  • Rows Removed by Filter: 13
23. 3.225 6,468.490 ↓ 14.0 14 43

GroupAggregate (cost=901.40..901.43 rows=1 width=40) (actual time=150.392..150.430 rows=14 loops=43)

  • Group Key: at_2.cid, at_2.property_id
24. 5.203 6,465.265 ↓ 55.0 55 43

Sort (cost=901.40..901.41 rows=1 width=12) (actual time=150.348..150.355 rows=55 loops=43)

  • Sort Key: at_2.property_id
  • Sort Method: quicksort Memory: 28kB
25. 1.118 6,460.062 ↓ 70.0 70 43

Result (cost=1.43..901.39 rows=1 width=12) (actual time=0.673..150.234 rows=70 loops=43)

  • One-Time Filter: (p.cid = 15489)
26. 2.838 6,458.944 ↓ 70.0 70 43

Nested Loop (cost=1.43..901.39 rows=1 width=12) (actual time=0.672..150.208 rows=70 loops=43)

27. 491.791 6,435.036 ↓ 70.0 70 43

Nested Loop (cost=0.99..898.93 rows=1 width=20) (actual time=0.650..149.652 rows=70 loops=43)

28. 1,455.335 1,455.335 ↓ 80.3 20,874 43

Index Only Scan using idx_ar_allocations_ar_trans_join on ar_allocations aa_1 (cost=0.56..262.05 rows=260 width=16) (actual time=0.025..33.845 rows=20,874 loops=43)

  • Index Cond: ((cid = 15489) AND (post_month = '2019-03-01'::date))
  • Heap Fetches: 897582
29. 4,487.910 4,487.910 ↓ 0.0 0 897,582

Index Scan using idx_ar_transactions_cache_charge_master_offset on ar_transactions at_2 (cost=0.43..2.45 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=897,582)

  • Index Cond: ((cid = 15489) AND (id = aa_1.credit_ar_transaction_id))
  • Filter: ((property_id = property_id) AND (ar_code_type_id = 7))
  • Rows Removed by Filter: 1
30. 21.070 21.070 ↑ 1.0 1 3,010

Index Only Scan using idx_ar_transactions_cache_credit_master_offset on ar_transactions at_offset_1 (cost=0.43..2.45 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=3,010)

  • Index Cond: ((cid = 15489) AND (id = aa_1.charge_ar_transaction_id))
  • Heap Fetches: 3010
31. 0.387 68.585 ↓ 0.0 0 43

Subquery Scan on at_refund (cost=5.03..12.36 rows=1 width=72) (actual time=1.595..1.595 rows=0 loops=43)

  • Filter: ((at_refund.cid = p.cid) AND (at_refund.property_id = p.id))
32. 0.817 68.198 ↓ 0.0 0 43

GroupAggregate (cost=5.03..12.35 rows=1 width=72) (actual time=1.586..1.586 rows=0 loops=43)

  • Group Key: at_3.cid, at_3.property_id
33. 0.387 67.381 ↓ 2.0 2 43

Result (cost=5.03..12.29 rows=1 width=26) (actual time=1.118..1.567 rows=2 loops=43)

  • One-Time Filter: (p.cid = 15489)
34. 0.383 66.994 ↓ 2.0 2 43

Nested Loop Left Join (cost=5.03..12.29 rows=1 width=26) (actual time=1.110..1.558 rows=2 loops=43)

  • Filter: CASE WHEN (arp.returned_on IS NULL) THEN true ELSE ((ah.id IS NOT NULL) AND (aa_2.created_on < arp.returned_on)) END
35. 0.402 65.575 ↓ 2.0 2 43

Nested Loop Left Join (cost=4.61..9.84 rows=1 width=42) (actual time=1.092..1.525 rows=2 loops=43)

36. 0.560 64.285 ↓ 2.0 2 43

Nested Loop (cost=4.18..8.78 rows=1 width=46) (actual time=1.075..1.495 rows=2 loops=43)

  • Join Filter: CASE WHEN (at_3.is_reversal IS FALSE) THEN true ELSE (ac_credit.default_ar_code_id = 1701) END
  • Rows Removed by Join Filter: 0
37. 0.444 63.081 ↓ 2.0 2 43

Nested Loop (cost=3.89..8.45 rows=1 width=42) (actual time=1.055..1.467 rows=2 loops=43)

38. 10.500 61.533 ↓ 2.0 2 43

Nested Loop (cost=3.46..6.51 rows=1 width=38) (actual time=1.038..1.431 rows=2 loops=43)

39. 49.235 49.235 ↑ 1.0 1 43

Index Scan using idx_ar_transactions_periods_join on ar_transactions at_3 (cost=0.56..2.59 rows=1 width=18) (actual time=0.771..1.145 rows=1 loops=43)

  • Index Cond: ((cid = 15489) AND (property_id = p.id) AND (post_month = '2019-03-01'::date))
  • Filter: (ar_code_type_id = 8)
  • Rows Removed by Filter: 627
40. 0.434 1.798 ↓ 3.0 3 31

Bitmap Heap Scan on ar_allocations aa_2 (cost=2.90..3.92 rows=1 width=24) (actual time=0.054..0.058 rows=3 loops=31)

  • Recheck Cond: (((cid = 15489) AND (charge_ar_transaction_id = at_3.id)) OR ((cid = 15489) AND (credit_ar_transaction_id = at_3.id)))
  • Filter: (allocation_amount <> '0'::numeric)
  • Heap Blocks: exact=41
41. 0.217 1.364 ↓ 0.0 0 31

BitmapOr (cost=2.90..2.90 rows=1 width=0) (actual time=0.044..0.044 rows=0 loops=31)

42. 0.620 0.620 ↓ 3.0 3 31

Bitmap Index Scan on idx_ar_allocations_cid_charge_ar_transaction_id (cost=0.00..1.45 rows=1 width=0) (actual time=0.020..0.020 rows=3 loops=31)

  • Index Cond: ((cid = 15489) AND (charge_ar_transaction_id = at_3.id))
43. 0.527 0.527 ↓ 0.0 0 31

Bitmap Index Scan on idx_ar_allocations_cid_credit_ar_transaction_id (cost=0.00..1.45 rows=1 width=0) (actual time=0.017..0.017 rows=0 loops=31)

  • Index Cond: ((cid = 15489) AND (credit_ar_transaction_id = at_3.id))
44. 1.104 1.104 ↑ 1.0 1 92

Index Scan using pk_ar_transactions on ar_transactions at_credit (cost=0.43..1.94 rows=1 width=16) (actual time=0.012..0.012 rows=1 loops=92)

  • Index Cond: ((cid = 15489) AND (id = aa_2.credit_ar_transaction_id))
45. 0.644 0.644 ↑ 1.0 1 92

Index Scan using pk_ar_codes on ar_codes ac_credit (cost=0.29..0.32 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=92)

  • Index Cond: ((cid = 15489) AND (id = at_credit.ar_code_id))
46. 0.222 0.888 ↓ 0.0 0 74

Result (cost=0.43..1.06 rows=1 width=12) (actual time=0.012..0.012 rows=0 loops=74)

  • One-Time Filter: (p.cid = 15489)
47. 0.666 0.666 ↓ 0.0 0 74

Index Scan using pk_ar_payments on ar_payments arp (cost=0.43..1.06 rows=1 width=12) (actual time=0.009..0.009 rows=0 loops=74)

  • Index Cond: ((cid = at_credit.cid) AND (cid = 15489) AND (id = at_credit.ar_payment_id))
48. 0.148 1.036 ↑ 1.0 1 74

Result (cost=0.42..2.44 rows=1 width=12) (actual time=0.013..0.014 rows=1 loops=74)

  • One-Time Filter: (p.cid = 15489)
49. 0.888 0.888 ↑ 1.0 1 74

Index Scan using idx_ap_headers_refund_ar_transaction_id on ap_headers ah (cost=0.42..2.44 rows=1 width=12) (actual time=0.011..0.012 rows=1 loops=74)

  • Index Cond: (refund_ar_transaction_id = at_3.id)
  • Filter: ((reversal_ap_header_id IS NULL) AND (cid = 15489) AND (cid = at_3.cid))
  • Rows Removed by Filter: 0
50. 0.473 455.370 ↑ 1.0 1 43

Subquery Scan on at_transit (cost=1.28..7.04 rows=1 width=40) (actual time=10.590..10.590 rows=1 loops=43)

  • Filter: ((at_transit.cid = p.cid) AND (at_transit.property_id = p.id))
51. 4.300 454.897 ↑ 1.0 1 43

GroupAggregate (cost=1.28..7.02 rows=1 width=40) (actual time=10.579..10.579 rows=1 loops=43)

  • Group Key: art.cid, art.property_id
52. 0.989 450.597 ↓ 58.0 58 43

Result (cost=1.28..6.99 rows=1 width=17) (actual time=0.846..10.479 rows=58 loops=43)

  • One-Time Filter: (p.cid = 15489)
53. 13.022 449.608 ↓ 58.0 58 43

Nested Loop Anti Join (cost=1.28..6.99 rows=1 width=17) (actual time=0.838..10.456 rows=58 loops=43)

54. 19.456 366.790 ↓ 406.0 406 43

Nested Loop Left Join (cost=0.99..6.62 rows=1 width=29) (actual time=0.151..8.530 rows=406 loops=43)

55. 225.191 225.191 ↓ 406.0 406 43

Index Scan using idx_ar_transactions_periods_join on ar_transactions art (cost=0.56..4.16 rows=1 width=21) (actual time=0.077..5.237 rows=406 loops=43)

  • Index Cond: ((cid = 15489) AND (property_id = p.id) AND (post_month = ANY ('{""2019-03-01 00:00:00"",""2019-02-01 00:00:00""}'::timestamp without time zone[])))
  • Filter: ((NOT is_temporary) AND (ar_code_type_id = 1))
  • Rows Removed by Filter: 961
56. 17.449 122.143 ↑ 1.0 1 17,449

Result (cost=0.43..2.46 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=17,449)

  • One-Time Filter: (p.cid = 15489)
57. 104.694 104.694 ↑ 1.0 1 17,449

Index Scan using idx_ar_deposit_transactions_ar_transaction_id on ar_deposit_transactions adt (cost=0.43..2.46 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=17,449)

  • Index Cond: (art.id = ar_transaction_id)
  • Filter: ((cid = 15489) AND (art.cid = cid) AND (post_month = art.post_month))
  • Rows Removed by Filter: 0
58. 17.449 69.796 ↑ 1.0 1 17,449

Result (cost=0.29..0.36 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=17,449)

  • One-Time Filter: (p.cid = 15489)
59. 52.347 52.347 ↑ 1.0 1 17,449

Index Scan using idx_ar_deposits_id on ar_deposits ad (cost=0.29..0.36 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=17,449)

  • Index Cond: (adt.ar_deposit_id = id)
  • Filter: ((cid = 15489) AND (adt.cid = cid) AND (post_month = adt.post_month))
60. 0.038 18,709.163 ↓ 43.0 43 1

Nested Loop (cost=4.87..6.93 rows=1 width=176) (actual time=18,708.897..18,709.163 rows=43 loops=1)

61. 0.045 18,708.867 ↓ 43.0 43 1

Merge Join (cost=4.60..4.62 rows=1 width=172) (actual time=18,708.819..18,708.867 rows=43 loops=1)

  • Merge Cond: (pft.property_id = ccv.property_id)
62. 0.029 0.191 ↓ 43.0 43 1

Sort (cost=4.56..4.57 rows=1 width=12) (actual time=0.185..0.191 rows=43 loops=1)

  • Sort Key: pft.property_id
  • Sort Method: quicksort Memory: 27kB
63. 0.060 0.162 ↓ 43.0 43 1

Bitmap Heap Scan on property_fee_templates pft (cost=3.49..4.55 rows=1 width=12) (actual time=0.145..0.162 rows=43 loops=1)

  • Recheck Cond: ((cid = 15489) AND (fee_template_id = 260))
  • Filter: ((deleted_on IS NULL) AND (property_id = ANY ('{622942,622943,622944,622945,622946,622961,622947,622971,622948,622949,622972,622950,622951,622952,622973,622953,622954,622955,622956,622974,622957,622975,622958,622976,622959,622960,622977,622962,622963,622964,622965,622966,622967,622968,622979,622969,622970,622980,679353,640991,676485,622981,640992}'::integer[])))
  • Heap Blocks: exact=3
64. 0.004 0.102 ↓ 0.0 0 1

BitmapAnd (cost=3.49..3.49 rows=1 width=0) (actual time=0.102..0.102 rows=0 loops=1)

65. 0.062 0.062 ↓ 6.0 258 1

Bitmap Index Scan on pk_property_fee_templates (cost=0.00..1.61 rows=43 width=0) (actual time=0.062..0.062 rows=258 loops=1)

  • Index Cond: (cid = 15489)
66. 0.036 0.036 ↓ 2.0 88 1

Bitmap Index Scan on idx_property_fee_templates_fee_template_id (cost=0.00..1.62 rows=45 width=0) (actual time=0.036..0.036 rows=88 loops=1)

  • Index Cond: (fee_template_id = 260)
67. 0.346 18,708.631 ↓ 43.0 43 1

Sort (cost=0.03..0.04 rows=1 width=168) (actual time=18,708.625..18,708.631 rows=43 loops=1)

  • Sort Key: ccv.property_id
  • Sort Method: quicksort Memory: 28kB
68. 18,708.285 18,708.285 ↓ 43.0 43 1

CTE Scan on calculated_cash_values ccv (cost=0.00..0.02 rows=1 width=168) (actual time=493.304..18,708.285 rows=43 loops=1)

  • Filter: (cid = 15489)
69. 0.258 0.258 ↑ 1.0 1 43

Index Scan using idx_fee_templates_ap_routing_tag_id on fee_templates ft (cost=0.28..2.29 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=43)

  • Index Cond: (cid = 15489)
  • Filter: ((deleted_on IS NULL) AND (id = 260))
  • Rows Removed by Filter: 4