explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tkV7

Settings
# exclusive inclusive rows x rows loops node
1. 0.057 3,714.116 ↓ 25.0 25 1

Limit (cost=13,559.95..13,563.04 rows=1 width=395) (actual time=3,713.354..3,714.116 rows=25 loops=1)

2. 87.174 3,714.059 ↓ 25.0 25 1

WindowAgg (cost=13,559.95..13,563.04 rows=1 width=395) (actual time=3,713.352..3,714.059 rows=25 loops=1)

3. 366.355 3,626.685 ↓ 61,242.0 61,242 1

GroupAggregate (cost=13,559.95..13,560.00 rows=1 width=324) (actual time=3,110.024..3,626.685 rows=61,242 loops=1)

  • Group Key: ah.id, ah.cid, ap.ap_payee_status_type_id, apa.account_number, ap.company_name, apl.vendor_code, apl.location_name
4. 508.152 3,260.330 ↓ 215,773.0 215,773 1

Sort (cost=13,559.95..13,559.95 rows=1 width=225) (actual time=3,109.996..3,260.330 rows=215,773 loops=1)

  • Sort Key: ah.id DESC, ap.ap_payee_status_type_id, apa.account_number, ap.company_name, apl.vendor_code, apl.location_name
  • Sort Method: quicksort Memory: 71848kB
5. 481.109 2,752.178 ↓ 215,773.0 215,773 1

Nested Loop (cost=4.23..13,559.94 rows=1 width=225) (actual time=0.196..2,752.178 rows=215,773 loops=1)

6. 423.430 1,839.523 ↓ 215,773.0 215,773 1

Nested Loop (cost=3.94..13,559.62 rows=1 width=211) (actual time=0.181..1,839.523 rows=215,773 loops=1)

7. 128.228 864.915 ↓ 61,242.0 61,242 1

Nested Loop (cost=2.19..13,543.68 rows=1 width=191) (actual time=0.102..864.915 rows=61,242 loops=1)

8. 164.706 552.961 ↓ 8,748.9 61,242 1

Nested Loop (cost=1.77..13,540.12 rows=7 width=184) (actual time=0.078..552.961 rows=61,242 loops=1)

9. 97.300 265.771 ↓ 90.9 61,242 1

Merge Join (cost=1.35..13,190.71 rows=674 width=170) (actual time=0.064..265.771 rows=61,242 loops=1)

  • Merge Cond: (ah.ap_payee_id = ap.id)
10. 165.362 165.362 ↓ 1.1 61,242 1

Index Scan using idx_ap_headers_ap_payee_id_remote_primary_key on ap_headers ah (cost=0.42..11,869.18 rows=55,311 width=149) (actual time=0.029..165.362 rows=61,242 loops=1)

  • Index Cond: (cid = 13576)
  • Filter: ((NOT is_batching) AND (ap_payment_id IS NULL) AND (NOT is_template) AND (deleted_on IS NULL) AND (reversal_ap_header_id IS NULL) AND (is_posted OR (NOT is_posted)) AND (transaction_amount <> '0'::numeric) AND (COALESCE(ap_financial_status_type_id, 0) <> 6) AND (ap_header_type_id = 5) AND ((ap_header_sub_type_id = ANY ('{5,6,7,8,12,15,100,17}'::integer[])) OR (lease_customer_id IS NOT NULL)))
  • Rows Removed by Filter: 39119
11. 3.109 3.109 ↑ 1.1 2,188 1

Index Scan using pk_ap_payees on ap_payees ap (cost=0.42..1,170.72 rows=2,318 width=29) (actual time=0.025..3.109 rows=2,188 loops=1)

  • Index Cond: (cid = 13576)
12. 122.484 122.484 ↑ 1.0 1 61,242

Index Scan using idx_ap_payee_locations_id on ap_payee_locations apl (cost=0.42..0.52 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=61,242)

  • Index Cond: (id = ah.ap_payee_location_id)
  • Filter: (cid = 13576)
13. 183.726 183.726 ↑ 1.0 1 61,242

Index Scan using idx_ap_payee_accounts_id on ap_payee_accounts apa (cost=0.42..0.51 rows=1 width=19) (actual time=0.003..0.003 rows=1 loops=61,242)

  • Index Cond: (id = ah.ap_payee_account_id)
  • Filter: (cid = 13576)
  • Rows Removed by Filter: 1
14. 551.135 551.178 ↑ 1.2 4 61,242

Index Scan using idx_ap_details_cid_apheaderid on ap_details ad (cost=1.75..15.89 rows=5 width=27) (actual time=0.004..0.009 rows=4 loops=61,242)

  • Index Cond: ((cid = 13576) AND (ap_header_id = ah.id))
  • Filter: ((deleted_on IS NULL) AND CASE WHEN ((ah.control_total = '0'::numeric) AND (ah.lease_customer_id IS NULL)) THEN (alternatives: SubPlan 2 or hashed SubPlan 3) ELSE (hashed SubPlan 4) END)
  • Rows Removed by Filter: 0
15.          

SubPlan (forIndex Scan)

16. 0.000 0.000 ↓ 0.0 0

Seq Scan on load_properties_info lp (cost=0.00..1.32 rows=1 width=0) (never executed)

  • Filter: (property_id = ad.property_id)
17. 0.021 0.021 ↑ 1.0 26 1

Seq Scan on load_properties_info lp_1 (cost=0.00..1.26 rows=26 width=4) (actual time=0.004..0.021 rows=26 loops=1)

18. 0.022 0.022 ↑ 1.0 26 1

Seq Scan on load_properties_info (cost=0.00..1.26 rows=26 width=4) (actual time=0.004..0.022 rows=26 loops=1)

19. 431.546 431.546 ↑ 1.0 1 215,773

Index Scan using idx_properties_id on properties p (cost=0.29..0.31 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=215,773)

  • Index Cond: (id = ad.property_id)
  • Filter: (cid = 13576)
20.          

SubPlan (forWindowAgg)

21. 0.100 0.200 ↑ 1.0 1 25

Limit (cost=0.29..2.51 rows=1 width=4) (actual time=0.005..0.008 rows=1 loops=25)

22. 0.100 0.100 ↑ 1.0 1 25

Index Scan using idx_file_associations_ap_header_id_partial on file_associations fa (cost=0.29..2.51 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=25)

  • Index Cond: ((cid = ah.cid) AND (ap_header_id = ah.id))
  • Filter: (deleted_on IS NULL)
Planning time : 9.850 ms
Execution time : 3,722.218 ms