explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gkmM

Settings
# exclusive inclusive rows x rows loops node
1. 380.614 175,025.391 ↓ 61.0 61 1

GroupAggregate (cost=4,866.67..4,868.02 rows=1 width=1,444) (actual time=174,856.724..175,025.391 rows=61 loops=1)

  • Group Key: ah.cid, ah.id, ap.company_name, gat.cid, gat.id, ad.cid, ad.id, gd.name, cd.name, cd.details, art.name, ap.ap_payee_status_type_id, pb.building_name, pb.details, pu.unit_number, pu.details, apt.name, apl.vendor_code, ba.account_name
  • Filter: (CASE WHEN (ad.transaction_amount >= '0'::numeric) THEN (ad.transaction_amount + sum(COALESCE(aa.allocation_amount, '0'::numeric))) ELSE (ad.transaction_amount - sum(COALESCE(aa.allocation_amount, '0'::numeric))) END <> '0'::numeric)
  • Rows Removed by Filter: 170854
2. 697.204 174,644.777 ↓ 176,340.0 176,340 1

Sort (cost=4,866.67..4,866.67 rows=1 width=1,472) (actual time=174,603.345..174,644.777 rows=176,340 loops=1)

  • Sort Key: ah.id, ap.company_name, gat.id, ad.id, gd.name, cd.name, cd.details, art.name, ap.ap_payee_status_type_id, pb.building_name, pb.details, pu.unit_number, pu.details, apt.name, apl.vendor_code, ba.account_name
  • Sort Method: quicksort Memory: 63194kB
3. 254.896 173,947.573 ↓ 176,340.0 176,340 1

Nested Loop Left Join (cost=34.14..4,866.66 rows=1 width=1,472) (actual time=22.192..173,947.573 rows=176,340 loops=1)

4. 239.048 173,692.677 ↓ 176,340.0 176,340 1

Nested Loop Left Join (cost=34.00..4,866.47 rows=1 width=1,461) (actual time=22.188..173,692.677 rows=176,340 loops=1)

5. 279.601 173,453.629 ↓ 176,340.0 176,340 1

Nested Loop Left Join (cost=33.72..4,866.16 rows=1 width=831) (actual time=22.184..173,453.629 rows=176,340 loops=1)

6. 259.409 173,174.028 ↓ 176,340.0 176,340 1

Nested Loop Left Join (cost=33.58..4,865.97 rows=1 width=827) (actual time=22.180..173,174.028 rows=176,340 loops=1)

  • Join Filter: (ap.id = apl.ap_payee_id)
7. 259.900 172,209.259 ↓ 176,340.0 176,340 1

Nested Loop Left Join (cost=33.29..4,865.62 rows=1 width=827) (actual time=22.166..172,209.259 rows=176,340 loops=1)

8. 232.404 171,243.999 ↓ 176,340.0 176,340 1

Nested Loop (cost=33.00..4,865.27 rows=1 width=800) (actual time=22.147..171,243.999 rows=176,340 loops=1)

9. 83,334.121 170,306.235 ↓ 176,340.0 176,340 1

Nested Loop Left Join (cost=32.73..4,864.97 rows=1 width=783) (actual time=22.135..170,306.235 rows=176,340 loops=1)

  • Join Filter: (vendors.id = ah.ap_payee_id)
  • Rows Removed by Join Filter: 437500529
  • Filter: ((vendors.id IS NOT NULL) OR (ah.lease_customer_id IS NOT NULL))
10. 579.940 10,616.894 ↓ 176,340.0 176,340 1

Nested Loop Left Join (cost=32.73..4,790.86 rows=1 width=783) (actual time=21.748..10,616.894 rows=176,340 loops=1)

11. 302.283 7,985.974 ↓ 170,915.0 170,915 1

Nested Loop (cost=31.65..4,785.70 rows=1 width=777) (actual time=21.717..7,985.974 rows=170,915 loops=1)

  • Join Filter: (gat.gl_tree_id = gt.id)
12. 307.012 7,341.861 ↓ 170,915.0 170,915 1

Nested Loop (cost=31.50..4,777.53 rows=1 width=781) (actual time=21.708..7,341.861 rows=170,915 loops=1)

13. 308.110 4,983.869 ↓ 170,915.0 170,915 1

Nested Loop Left Join (cost=31.08..4,775.46 rows=1 width=523) (actual time=21.683..4,983.869 rows=170,915 loops=1)

14. 298.721 4,333.929 ↓ 170,915.0 170,915 1

Nested Loop Left Join (cost=30.95..4,775.30 rows=1 width=516) (actual time=21.675..4,333.929 rows=170,915 loops=1)

15. 231.533 3,351.548 ↓ 170,915.0 170,915 1

Nested Loop Anti Join (cost=30.66..4,774.96 rows=1 width=516) (actual time=21.659..3,351.548 rows=170,915 loops=1)

16. 111.071 3,120.015 ↓ 172,944.0 172,944 1

Nested Loop Left Join (cost=30.23..4,773.99 rows=1 width=520) (actual time=21.656..3,120.015 rows=172,944 loops=1)

17. 160.413 2,836.000 ↓ 172,944.0 172,944 1

Nested Loop Left Join (cost=29.95..4,773.58 rows=1 width=327) (actual time=21.652..2,836.000 rows=172,944 loops=1)

18. 201.873 2,502.643 ↓ 172,944.0 172,944 1

Nested Loop (cost=29.66..4,773.07 rows=1 width=141) (actual time=21.647..2,502.643 rows=172,944 loops=1)

19. 90.507 960.263 ↓ 925.1 191,501 1

Nested Loop (cost=29.24..4,457.84 rows=207 width=77) (actual time=21.627..960.263 rows=191,501 loops=1)

20. 13.937 13.937 ↓ 29.0 29 1

Function Scan on load_properties load_prop (cost=0.25..1.00 rows=1 width=4) (actual time=13.794..13.937 rows=29 loops=1)

  • Filter: ((is_disabled = 0) AND (is_test = 0))
21. 818.322 855.819 ↓ 31.9 6,603 29

Bitmap Heap Scan on ap_details ad (cost=28.99..4,454.77 rows=207 width=77) (actual time=1.856..29.511 rows=6,603 loops=29)

  • Recheck Cond: ((cid = 224) AND (property_id = load_prop.property_id))
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL) AND (post_month <= '2019-06-01'::date) AND (retention_amount = '0'::numeric) AND (bank_account_id = ANY ('{5989,4426,5984,5980,5973,5990,5974,5993,5991,6005,5975,5996,5977,5994,6001,5997,6002,5978,5986,5998,5987,5979,5999,6003,6000,6004,4018,6067,4022,4017,4019,4020,4021,4100,4098,4101,4023,4024,4007,4012,4010,4011,4013,4114,4111,2672,2673,3974,146,3975,3976,152,145,148,135,131,3986,5932,4025,4110,4115,4109,4015,4108,4097,4233,4232,4231,4234,4235}'::integer[])))
  • Rows Removed by Filter: 2562
  • Heap Blocks: exact=96850
22. 37.497 37.497 ↓ 7.3 9,166 29

Bitmap Index Scan on idx_ap_details_cid_property_id (cost=0.00..28.94 rows=1,251 width=0) (actual time=1.293..1.293 rows=9,166 loops=29)

  • Index Cond: ((cid = 224) AND (property_id = load_prop.property_id))
23. 1,340.507 1,340.507 ↑ 1.0 1 191,501

Index Scan using idx_ap_headers on ap_headers ah (cost=0.42..1.51 rows=1 width=80) (actual time=0.007..0.007 rows=1 loops=191,501)

  • Index Cond: (id = ad.ap_header_id)
  • Filter: ((is_template IS FALSE) AND (is_posted OR (is_temporary AND (reversal_ap_header_id IS NULL))) AND (COALESCE(ap_financial_status_type_id, 0) <> 8) AND (ap_financial_status_type_id IS DISTINCT FROM 8) AND (cid = 224) AND (ap_header_type_id = 5) AND (ad.gl_transaction_type_id = gl_transaction_type_id) AND (ad.post_month = post_month))
  • Rows Removed by Filter: 0
24. 172.944 172.944 ↓ 0.0 0 172,944

Index Scan using pk_property_units on property_units pu (cost=0.29..0.50 rows=1 width=194) (actual time=0.001..0.001 rows=0 loops=172,944)

  • Index Cond: ((ad.cid = cid) AND (cid = 224) AND (ad.property_unit_id = id))
25. 172.944 172.944 ↓ 0.0 0 172,944

Index Scan using idx_property_buildings_id on property_buildings pb (cost=0.28..0.40 rows=1 width=209) (actual time=0.001..0.001 rows=0 loops=172,944)

  • Index Cond: (id = pu.property_building_id)
  • Filter: ((cid = 224) AND (cid = pu.cid))
26. 0.000 0.000 ↓ 0.0 0 172,944

Index Scan using idx_ap_headers on ap_headers ah_reversal (cost=0.42..0.96 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=172,944)

  • Index Cond: (id = ah.reversal_ap_header_id)
  • Filter: ((is_template IS FALSE) AND (post_month <= '2019-06-01'::date) AND (cid = 224) AND (cid = ah.cid))
  • Rows Removed by Filter: 0
27. 683.660 683.660 ↑ 1.0 1 170,915

Index Scan using pk_ap_remittances on ap_remittances apr (cost=0.29..0.34 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=170,915)

  • Index Cond: ((cid = ah.cid) AND (cid = 224) AND (id = ah.ap_remittance_id))
28. 341.830 341.830 ↑ 1.0 1 170,915

Index Scan using pk_ap_payment_types on ap_payment_types apt (cost=0.14..0.16 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=170,915)

  • Index Cond: (id = apr.ap_payment_type_id)
29. 2,050.980 2,050.980 ↑ 1.0 1 170,915

Index Scan using idx_gl_account_trees_gl_account_id on gl_account_trees gat (cost=0.42..2.06 rows=1 width=266) (actual time=0.008..0.012 rows=1 loops=170,915)

  • Index Cond: (gl_account_id = ad.gl_account_id)
  • Filter: ((cid = 224) AND (is_default = 1))
  • Rows Removed by Filter: 6
30. 341.830 341.830 ↑ 1.0 1 170,915

Index Scan using idx_gl_trees_cid_is_system on gl_trees gt (cost=0.14..8.16 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=170,915)

  • Index Cond: (cid = 224)
31. 512.745 2,050.980 ↑ 1.0 1 170,915

Bitmap Heap Scan on ap_allocations aa (cost=1.08..5.15 rows=1 width=18) (actual time=0.012..0.012 rows=1 loops=170,915)

  • Recheck Cond: ((ad.id = charge_ap_detail_id) OR (ad.id = credit_ap_detail_id))
  • Filter: ((post_month <= '2019-06-01'::date) AND (cid = 224) AND (cid = ad.cid))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=180778
32. 170.915 1,538.235 ↓ 0.0 0 170,915

BitmapOr (cost=1.08..1.08 rows=3 width=0) (actual time=0.009..0.009 rows=0 loops=170,915)

33. 683.660 683.660 ↑ 1.0 1 170,915

Bitmap Index Scan on idx_ap_allocations_charge_ap_detail_id (cost=0.00..0.54 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=170,915)

  • Index Cond: (ad.id = charge_ap_detail_id)
34. 683.660 683.660 ↑ 1.0 1 170,915

Bitmap Index Scan on idx_ap_allocations_credit_ap_detail_id (cost=0.00..0.54 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=170,915)

  • Index Cond: (ad.id = credit_ap_detail_id)
35. 76,355.220 76,355.220 ↑ 1.1 2,482 176,340

Seq Scan on vendor_id_temp vendors (cost=0.00..39.05 rows=2,805 width=4) (actual time=0.006..0.433 rows=2,482 loops=176,340)

36. 705.360 705.360 ↑ 1.0 1 176,340

Index Scan using idx_bank_accounts on bank_accounts ba (cost=0.28..0.30 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=176,340)

  • Index Cond: (id = ad.bank_account_id)
  • Filter: (cid = 224)
  • Rows Removed by Filter: 0
37. 705.360 705.360 ↑ 1.0 1 176,340

Index Scan using idx_ap_payees_id on ap_payees ap (cost=0.29..0.34 rows=1 width=31) (actual time=0.004..0.004 rows=1 loops=176,340)

  • Index Cond: (id = ah.ap_payee_id)
  • Filter: ((cid = 224) AND (cid = ah.cid))
  • Rows Removed by Filter: 0
38. 705.360 705.360 ↑ 1.0 1 176,340

Index Scan using idx_ap_payee_locations_id on ap_payee_locations apl (cost=0.29..0.34 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=176,340)

  • Index Cond: (id = ah.ap_payee_location_id)
  • Filter: ((cid = 224) AND (cid = ah.cid))
  • Rows Removed by Filter: 0
39. 0.000 0.000 ↓ 0.0 0 176,340

Index Scan using pk_gl_dimensions on gl_dimensions gd (cost=0.14..0.17 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=176,340)

  • Index Cond: ((ad.cid = cid) AND (cid = 224) AND (ad.gl_dimension_id = id))
40. 0.000 0.000 ↓ 0.0 0 176,340

Index Scan using idx_company_departments_id on company_departments cd (cost=0.28..0.30 rows=1 width=642) (actual time=0.000..0.000 rows=0 loops=176,340)

  • Index Cond: (ad.company_department_id = id)
  • Filter: ((cid = 224) AND (ad.cid = cid))
41. 0.000 0.000 ↓ 0.0 0 176,340

Index Scan using pk_ap_routing_tags on ap_routing_tags art (cost=0.14..0.16 rows=1 width=23) (actual time=0.000..0.000 rows=0 loops=176,340)

  • Index Cond: ((ah.cid = cid) AND (cid = 224) AND (ah.ap_routing_tag_id = id))
Planning time : 32.117 ms