explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T4B2

Settings
# exclusive inclusive rows x rows loops node
1. 0.056 3,037.318 ↓ 25.0 25 1

Limit (cost=13,559.95..13,563.03 rows=1 width=387) (actual time=3,036.743..3,037.318 rows=25 loops=1)

2. 0.457 3,037.262 ↓ 25.0 25 1

GroupAggregate (cost=13,559.95..13,563.03 rows=1 width=387) (actual time=3,036.741..3,037.262 rows=25 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
3. 353.970 3,036.630 ↓ 69.0 69 1

Sort (cost=13,559.95..13,559.95 rows=1 width=225) (actual time=3,036.582..3,036.630 rows=69 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
4. 462.626 2,682.660 ↓ 215,773.0 215,773 1

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

5. 389.986 1,788.488 ↓ 215,773.0 215,773 1

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

6. 128.377 847.324 ↓ 61,242.0 61,242 1

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

7. 159.075 535.221 ↓ 8,748.9 61,242 1

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

8. 93.499 253.662 ↓ 90.9 61,242 1

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

  • Merge Cond: (ah.ap_payee_id = ap.id)
9. 157.293 157.293 ↓ 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.022..157.293 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
10. 2.870 2.870 ↑ 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.015..2.870 rows=2,188 loops=1)

  • Index Cond: (cid = 13576)
11. 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)
12. 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
13. 551.134 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
14.          

SubPlan (forIndex Scan)

15. 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)
16. 0.022 0.022 ↑ 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.022 rows=26 loops=1)

17. 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.005..0.022 rows=26 loops=1)

18. 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)
19.          

SubPlan (forGroupAggregate)

20. 0.075 0.175 ↑ 1.0 1 25

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

21. 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.003..0.004 rows=1 loops=25)

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