explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WYfu

Settings
# exclusive inclusive rows x rows loops node
1. 32.581 219.462 ↓ 956.2 16,255 1

Sort (cost=2,794.13..2,794.17 rows=17 width=507) (actual time=218.042..219.462 rows=16,255 loops=1)

  • Sort Key: (lower((ap.company_name)::text))
  • Sort Method: quicksort Memory: 8945kB
2.          

CTE cte_unlocked_ap_payee_locations

3. 2.159 2.159 ↓ 9.3 418 1

Index Scan using pk_ap_payee_property_groups on ap_payee_property_groups appg (cost=0.29..787.62 rows=45 width=12) (actual time=0.015..2.159 rows=418 loops=1)

  • Index Cond: (cid = 14181)
  • Filter: (property_group_id = (100000000 + cid))
  • Rows Removed by Filter: 8662
4.          

CTE cte_restricted_ap_payee_locations

5. 2.914 8.662 ↓ 4.3 5,901 1

HashAggregate (cost=719.20..733.04 rows=1,384 width=12) (actual time=7.700..8.662 rows=5,901 loops=1)

  • Group Key: appg_1.cid, appg_1.ap_payee_id, appg_1.ap_payee_location_id
6. 1.416 5.748 ↓ 6.3 8,660 1

Hash Anti Join (cost=82.96..708.82 rows=1,384 width=12) (actual time=0.407..5.748 rows=8,660 loops=1)

  • Hash Cond: ((appg_1.cid = cte.cid) AND (appg_1.ap_payee_id = cte.ap_payee_id) AND (appg_1.ap_payee_location_id = cte.ap_payee_location_id))
7. 0.797 4.232 ↓ 6.3 8,660 1

Nested Loop (cost=81.93..683.05 rows=1,384 width=12) (actual time=0.299..4.232 rows=8,660 loops=1)

8. 0.120 0.300 ↓ 1.9 95 1

HashAggregate (cost=81.64..82.15 rows=51 width=8) (actual time=0.284..0.300 rows=95 loops=1)

  • Group Key: pga.cid, pga.property_group_id
9. 0.086 0.180 ↓ 9.8 507 1

Bitmap Heap Scan on property_group_associations pga (cost=42.97..81.38 rows=52 width=8) (actual time=0.100..0.180 rows=507 loops=1)

  • Recheck Cond: ((cid = 14181) AND (property_id = ANY ('{503484,503485,503486,503989,503990,503991,503992,503993,503994,503995,503996,503997,503998,503999,504000,504001,504002,504003,504004,504005,504006,504007,504008,508477,515896,531478,533403,560803,579358,579359,600104,600105,600106,633329,633330,650536,664906,664907,664908,677197,705098,705099,705100,801783,837894,854345,854346,875985,894585}'::integer[])))
  • Heap Blocks: exact=21
10. 0.002 0.094 ↓ 0.0 0 1

BitmapAnd (cost=42.97..42.97 rows=52 width=0) (actual time=0.094..0.094 rows=0 loops=1)

11. 0.027 0.027 ↑ 1.0 533 1

Bitmap Index Scan on pk_property_group_associations (cost=0.00..6.48 rows=533 width=0) (actual time=0.027..0.027 rows=533 loops=1)

  • Index Cond: (cid = 14181)
12. 0.065 0.065 ↑ 1.0 507 1

Bitmap Index Scan on idx_property_group_associations_property_id (cost=0.00..36.22 rows=508 width=0) (actual time=0.065..0.065 rows=507 loops=1)

  • Index Cond: (property_id = ANY ('{503484,503485,503486,503989,503990,503991,503992,503993,503994,503995,503996,503997,503998,503999,504000,504001,504002,504003,504004,504005,504006,504007,504008,508477,515896,531478,533403,560803,579358,579359,600104,600105,600106,633329,633330,650536,664906,664907,664908,677197,705098,705099,705100,801783,837894,854345,854346,875985,894585}'::integer[]))
13. 3.135 3.135 ↓ 13.0 91 95

Index Scan using idx_ap_payee_property_groups_property_group_id on ap_payee_property_groups appg_1 (cost=0.29..11.71 rows=7 width=16) (actual time=0.004..0.033 rows=91 loops=95)

  • Index Cond: (property_group_id = pga.property_group_id)
  • Filter: ((cid = 14181) AND (property_group_id <> (100000000 + cid)))
  • Rows Removed by Filter: 4
14. 0.049 0.100 ↓ 418.0 418 1

Hash (cost=1.01..1.01 rows=1 width=12) (actual time=0.100..0.100 rows=418 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
15. 0.051 0.051 ↓ 418.0 418 1

CTE Scan on cte_unlocked_ap_payee_locations cte (cost=0.00..1.01 rows=1 width=12) (actual time=0.001..0.051 rows=418 loops=1)

  • Filter: (cid = 14181)
16. 13.743 186.881 ↓ 956.2 16,255 1

Nested Loop Left Join (cost=665.54..1,273.12 rows=17 width=507) (actual time=15.542..186.881 rows=16,255 loops=1)

17. 3.425 91.863 ↓ 956.2 16,255 1

Nested Loop Left Join (cost=665.25..1,264.98 rows=17 width=422) (actual time=15.520..91.863 rows=16,255 loops=1)

18. 3.410 72.183 ↓ 956.2 16,255 1

Nested Loop (cost=664.96..1,253.54 rows=17 width=393) (actual time=15.515..72.183 rows=16,255 loops=1)

19. 0.553 47.845 ↓ 158.5 5,232 1

Nested Loop (cost=664.67..1,238.27 rows=33 width=370) (actual time=15.503..47.845 rows=5,232 loops=1)

20. 2.505 34.656 ↓ 14.9 6,318 1

Hash Join (cost=664.38..1,058.55 rows=424 width=92) (actual time=15.489..34.656 rows=6,318 loops=1)

  • Hash Cond: (apl.ap_legal_entity_id = ale.id)
21. 2.348 30.577 ↓ 1.4 6,318 1

Nested Loop (cost=39.59..422.20 rows=4,406 width=95) (actual time=13.907..30.577 rows=6,318 loops=1)

22. 2.867 15.591 ↓ 31.6 6,319 1

HashAggregate (cost=39.30..41.30 rows=200 width=4) (actual time=13.894..15.591 rows=6,319 loops=1)

  • Group Key: cte_unlocked_ap_payee_locations.ap_payee_location_id
23. 0.461 12.724 ↓ 4.4 6,319 1

Append (cost=0.00..35.73 rows=1,429 width=4) (actual time=0.017..12.724 rows=6,319 loops=1)

24. 2.267 2.267 ↓ 9.3 418 1

CTE Scan on cte_unlocked_ap_payee_locations (cost=0.00..0.90 rows=45 width=4) (actual time=0.017..2.267 rows=418 loops=1)

25. 9.996 9.996 ↓ 4.3 5,901 1

CTE Scan on cte_restricted_ap_payee_locations (cost=0.00..27.68 rows=1,384 width=4) (actual time=7.702..9.996 rows=5,901 loops=1)

26. 12.638 12.638 ↑ 1.0 1 6,319

Index Scan using idx_ap_payee_locations_id on ap_payee_locations apl (cost=0.29..1.90 rows=1 width=91) (actual time=0.002..0.002 rows=1 loops=6,319)

  • Index Cond: (id = cte_unlocked_ap_payee_locations.ap_payee_location_id)
  • Filter: ((disabled_by IS NULL) AND (deleted_by IS NULL) AND (deleted_on IS NULL) AND (cid = 14181))
  • Rows Removed by Filter: 0
27. 0.397 1.574 ↑ 1.0 3,401 1

Hash (cost=582.24..582.24 rows=3,404 width=9) (actual time=1.574..1.574 rows=3,401 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 179kB
28. 1.044 1.177 ↑ 1.0 3,401 1

Bitmap Heap Scan on ap_legal_entities ale (cost=37.68..582.24 rows=3,404 width=9) (actual time=0.153..1.177 rows=3,401 loops=1)

  • Recheck Cond: (cid = 14181)
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=160
29. 0.133 0.133 ↑ 1.0 3,404 1

Bitmap Index Scan on pk_ap_legal_entities (cost=0.00..36.83 rows=3,405 width=0) (actual time=0.133..0.133 rows=3,404 loops=1)

  • Index Cond: (cid = 14181)
30. 12.636 12.636 ↑ 1.0 1 6,318

Index Scan using idx_ap_payees_id on ap_payees ap (cost=0.29..0.42 rows=1 width=282) (actual time=0.002..0.002 rows=1 loops=6,318)

  • Index Cond: (id = apl.ap_payee_id)
  • Filter: ((cid = 14181) AND (ap_payee_status_type_id = 1) AND (ap_payee_type_id = ANY ('{1,3,8,2}'::integer[])))
  • Rows Removed by Filter: 0
31. 20.928 20.928 ↓ 3.0 3 5,232

Index Scan using idx_ap_payee_accounts_ap_payee_location_id on ap_payee_accounts apa (cost=0.29..0.45 rows=1 width=35) (actual time=0.002..0.004 rows=3 loops=5,232)

  • Index Cond: (ap_payee_location_id = apl.id)
  • Filter: ((NOT is_disabled) AND (cid = 14181))
  • Rows Removed by Filter: 0
32. 16.255 16.255 ↓ 0.0 0 16,255

Index Scan using idx_gl_account_trees_gl_account_id on gl_account_trees gat (cost=0.29..0.66 rows=1 width=37) (actual time=0.000..0.001 rows=0 loops=16,255)

  • Index Cond: (ap.gl_account_id = gl_account_id)
  • Filter: ((cid = 14181) AND (is_default = 1) AND (ap.cid = cid))
  • Rows Removed by Filter: 1
33. 81.275 81.275 ↑ 1.0 1 16,255

Index Scan using idx_ap_remittances_ap_payee_location_id on ap_remittances ar (cost=0.29..0.46 rows=1 width=12) (actual time=0.002..0.005 rows=1 loops=16,255)

  • Index Cond: ((apl.cid = cid) AND (cid = 14181) AND (apl.id = ap_payee_location_id))
  • Filter: (is_default AND is_published)
  • Rows Removed by Filter: 11
Planning time : 3.387 ms