explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hGxw

Settings
# exclusive inclusive rows x rows loops node
1. 28.655 316.422 ↓ 16,255.0 16,255 1

Sort (cost=2,600.12..2,600.12 rows=1 width=506) (actual time=314.965..316.422 rows=16,255 loops=1)

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

CTE cte_unlocked_ap_payee_locations

3. 2.176 2.176 ↓ 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.016..2.176 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.944 8.685 ↓ 4.3 5,901 1

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

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

Hash Anti Join (cost=82.96..708.82 rows=1,384 width=12) (actual time=0.403..5.741 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.778 4.212 ↓ 6.3 8,660 1

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

8. 0.117 0.299 ↓ 1.9 95 1

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

  • Group Key: pga.cid, pga.property_group_id
9. 0.087 0.182 ↓ 9.8 507 1

Bitmap Heap Scan on property_group_associations pga (cost=42.97..81.38 rows=52 width=8) (actual time=0.101..0.182 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.003 0.095 ↓ 0.0 0 1

BitmapAnd (cost=42.97..42.97 rows=52 width=0) (actual time=0.095..0.095 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.048 0.093 ↓ 418.0 418 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
15. 0.045 0.045 ↓ 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.045 rows=418 loops=1)

  • Filter: (cid = 14181)
16. 15.171 287.767 ↓ 16,255.0 16,255 1

Nested Loop Left Join (cost=673.06..1,079.44 rows=1 width=506) (actual time=15.721..287.767 rows=16,255 loops=1)

17. 3.109 191.321 ↓ 16,255.0 16,255 1

Nested Loop Left Join (cost=672.77..1,078.96 rows=1 width=421) (actual time=15.696..191.321 rows=16,255 loops=1)

18. 15.940 171.957 ↓ 16,255.0 16,255 1

Nested Loop (cost=672.48..1,078.29 rows=1 width=392) (actual time=15.693..171.957 rows=16,255 loops=1)

  • Join Filter: (apl.ap_payee_id = ap.id)
19. 6.331 137.518 ↓ 18,499.0 18,499 1

Nested Loop (cost=672.19..1,077.91 rows=1 width=123) (actual time=15.679..137.518 rows=18,499 loops=1)

20. 3.107 36.417 ↓ 6,318.0 6,318 1

Hash Join (cost=671.90..1,077.44 rows=1 width=96) (actual time=15.663..36.417 rows=6,318 loops=1)

  • Hash Cond: ((apl.ap_payee_id = ale.ap_payee_id) AND (apl.ap_legal_entity_id = ale.id))
21. 3.203 31.658 ↓ 1.4 6,318 1

Nested Loop (cost=39.59..422.20 rows=4,368 width=95) (actual time=13.999..31.658 rows=6,318 loops=1)

22. 3.048 15.817 ↓ 31.6 6,319 1

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

  • Group Key: cte_unlocked_ap_payee_locations.ap_payee_location_id
23. 0.465 12.769 ↓ 4.4 6,319 1

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

24. 2.286 2.286 ↓ 9.3 418 1

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

25. 10.018 10.018 ↓ 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.689..10.018 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.454 1.652 ↓ 1.0 3,401 1

Hash (cost=581.67..581.67 rows=3,376 width=13) (actual time=1.652..1.652 rows=3,401 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 192kB
28. 1.066 1.198 ↓ 1.0 3,401 1

Bitmap Heap Scan on ap_legal_entities ale (cost=37.46..581.67 rows=3,376 width=13) (actual time=0.152..1.198 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.132 0.132 ↓ 1.0 3,404 1

Bitmap Index Scan on pk_ap_legal_entities (cost=0.00..36.62 rows=3,377 width=0) (actual time=0.132..0.132 rows=3,404 loops=1)

  • Index Cond: (cid = 14181)
30. 94.770 94.770 ↓ 3.0 3 6,318

Index Scan using idx_ap_payee_accounts_ap_payee_id on ap_payee_accounts apa (cost=0.29..0.46 rows=1 width=39) (actual time=0.006..0.015 rows=3 loops=6,318)

  • Index Cond: (ap_payee_id = apl.ap_payee_id)
  • Filter: ((NOT is_disabled) AND (cid = 14181) AND (apl.id = ap_payee_location_id))
  • Rows Removed by Filter: 30
31. 18.499 18.499 ↑ 1.0 1 18,499

Index Scan using idx_ap_payees_id on ap_payees ap (cost=0.29..0.36 rows=1 width=281) (actual time=0.001..0.001 rows=1 loops=18,499)

  • Index Cond: (id = apa.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
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.67 rows=1 width=37) (actual time=0.001..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 : 4.569 ms