explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jGFq

Settings
# exclusive inclusive rows x rows loops node
1. 0.069 8,245.245 ↑ 1.0 25 1

Limit (cost=125,699.70..125,714.13 rows=25 width=508) (actual time=8,245.101..8,245.245 rows=25 loops=1)

2. 263.337 8,245.176 ↑ 16,698.9 25 1

WindowAgg (cost=125,699.70..366,790.35 rows=417,473 width=508) (actual time=8,245.098..8,245.176 rows=25 loops=1)

3. 824.742 7,981.839 ↑ 2.1 197,660 1

GroupAggregate (cost=125,699.70..147,617.03 rows=417,473 width=441) (actual time=6,848.254..7,981.839 rows=197,660 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. 972.504 7,157.097 ↓ 1.0 417,906 1

Sort (cost=125,699.70..126,743.38 rows=417,473 width=349) (actual time=6,848.229..7,157.097 rows=417,906 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: 126239kB
5. 650.761 6,184.593 ↓ 1.0 417,906 1

Hash Left Join (cost=25,330.20..86,725.83 rows=417,473 width=349) (actual time=609.244..6,184.593 rows=417,906 loops=1)

  • Hash Cond: (ad.property_id = lp.property_id)
6. 641.050 5,533.550 ↓ 1.0 417,906 1

Hash Join (cost=25,324.85..80,980.23 rows=417,473 width=349) (actual time=608.943..5,533.550 rows=417,906 loops=1)

  • Hash Cond: (ad.property_id = p.id)
7. 655.784 4,892.162 ↓ 1.0 417,906 1

Hash Join (cost=25,313.78..79,850.41 rows=417,473 width=328) (actual time=608.592..4,892.162 rows=417,906 loops=1)

  • Hash Cond: (ah.ap_payee_account_id = apa.id)
8. 650.496 4,209.990 ↓ 1.0 417,906 1

Hash Join (cost=24,671.02..78,111.43 rows=417,498 width=322) (actual time=582.153..4,209.990 rows=417,906 loops=1)

  • Hash Cond: (ah.ap_payee_location_id = apl.id)
9. 651.523 3,532.389 ↓ 1.0 417,906 1

Hash Join (cost=23,965.75..76,309.90 rows=417,523 width=300) (actual time=554.991..3,532.389 rows=417,906 loops=1)

  • Hash Cond: (ah.ap_payee_id = ap.id)
10. 1,180.637 2,853.986 ↓ 1.0 417,906 1

Hash Join (cost=23,160.39..74,408.18 rows=417,549 width=276) (actual time=528.073..2,853.986 rows=417,906 loops=1)

  • Hash Cond: (ad.ap_header_id = ah.id)
11. 1,145.747 1,145.747 ↓ 1.0 979,554 1

Seq Scan on ap_details ad (cost=0.00..48,678.52 rows=978,756 width=27) (actual time=0.012..1,145.747 rows=979,554 loops=1)

  • Filter: ((deleted_on IS NULL) AND (transaction_amount <> '0'::numeric) AND (cid = 13531))
  • Rows Removed by Filter: 11311
12. 202.926 527.602 ↓ 1.2 197,660 1

Hash (cost=21,107.75..21,107.75 rows=164,211 width=253) (actual time=527.600..527.602 rows=197,660 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 27070kB
13. 315.352 324.676 ↓ 1.2 197,660 1

Bitmap Heap Scan on ap_headers ah (cost=2,764.76..21,107.75 rows=164,211 width=253) (actual time=10.747..324.676 rows=197,660 loops=1)

  • Recheck Cond: (ap_payment_id IS NULL)
  • Filter: ((NOT is_batching) 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 (cid = 13531) AND (ap_header_type_id = 5) AND ((ap_header_sub_type_id = ANY ('{5,6,7,8,12,15,100}'::integer[])) OR (lease_customer_id IS NOT NULL)))
  • Rows Removed by Filter: 62320
  • Heap Blocks: exact=9287
14. 9.324 9.324 ↓ 1.0 259,980 1

Bitmap Index Scan on idx_ap_headers_ap_payment_id (cost=0.00..2,723.71 rows=258,678 width=0) (actual time=9.323..9.324 rows=259,980 loops=1)

  • Index Cond: (ap_payment_id IS NULL)
15. 12.907 26.880 ↑ 1.0 16,174 1

Hash (cost=603.19..603.19 rows=16,174 width=32) (actual time=26.879..26.880 rows=16,174 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1177kB
16. 13.973 13.973 ↑ 1.0 16,174 1

Seq Scan on ap_payees ap (cost=0.00..603.19 rows=16,174 width=32) (actual time=0.010..13.973 rows=16,174 loops=1)

  • Filter: (cid = 13531)
  • Rows Removed by Filter: 1
17. 13.650 27.105 ↑ 1.0 16,730 1

Hash (cost=496.14..496.14 rows=16,730 width=34) (actual time=27.104..27.105 rows=16,730 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1219kB
18. 13.455 13.455 ↑ 1.0 16,730 1

Seq Scan on ap_payee_locations apl (cost=0.00..496.14 rows=16,730 width=34) (actual time=0.010..13.455 rows=16,730 loops=1)

  • Filter: (cid = 13531)
  • Rows Removed by Filter: 1
19. 12.911 26.388 ↑ 1.0 16,750 1

Hash (cost=433.39..433.39 rows=16,750 width=18) (actual time=26.386..26.388 rows=16,750 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 911kB
20. 13.477 13.477 ↑ 1.0 16,750 1

Seq Scan on ap_payee_accounts apa (cost=0.00..433.39 rows=16,750 width=18) (actual time=0.006..13.477 rows=16,750 loops=1)

  • Filter: (cid = 13531)
  • Rows Removed by Filter: 1
21. 0.161 0.338 ↑ 1.0 203 1

Hash (cost=8.54..8.54 rows=203 width=33) (actual time=0.337..0.338 rows=203 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
22. 0.177 0.177 ↑ 1.0 203 1

Seq Scan on properties p (cost=0.00..8.54 rows=203 width=33) (actual time=0.009..0.177 rows=203 loops=1)

  • Filter: (cid = 13531)
23. 0.143 0.282 ↑ 1.0 193 1

Hash (cost=2.93..2.93 rows=193 width=4) (actual time=0.280..0.282 rows=193 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
24. 0.139 0.139 ↑ 1.0 193 1

Seq Scan on load_properties_info lp (cost=0.00..2.93 rows=193 width=4) (actual time=0.013..0.139 rows=193 loops=1)

Planning time : 9.371 ms