explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a4M4

Settings
# exclusive inclusive rows x rows loops node
1. 204.823 614.501 ↓ 2.2 99,749 1

Sort (cost=23,276.43..23,392.00 rows=46,226 width=80) (actual time=602.222..614.501 rows=99,749 loops=1)

  • Sort Key: "*SELECT* 1".vendor_name
  • Sort Method: quicksort Memory: 11161kB
2.          

CTE cte_unlocked_ap_payee_locations

3. 5.143 5.143 ↓ 54.8 12,382 1

Seq Scan on ap_payee_property_groups appg (cost=0.00..1,256.28 rows=226 width=12) (actual time=0.010..5.143 rows=12,382 loops=1)

  • Filter: ((cid = 3395) AND (property_group_id = (100000000 + cid)))
  • Rows Removed by Filter: 32777
4.          

CTE cte_restricted_ap_payee_locations

5. 10.329 42.378 ↑ 1.6 13,652 1

HashAggregate (cost=2,993.65..3,217.03 rows=22,338 width=12) (actual time=39.483..42.378 rows=13,652 loops=1)

  • Group Key: appg_1.cid, appg_1.ap_payee_id, appg_1.ap_payee_location_id
6. 5.693 32.049 ↑ 1.4 31,856 1

Hash Join (cost=273.46..2,656.65 rows=44,933 width=12) (actual time=6.574..32.049 rows=31,856 loops=1)

  • Hash Cond: (appg_1.property_group_id = pg.id)
7. 4.764 22.697 ↑ 1.4 31,856 1

Merge Anti Join (cost=5.38..1,770.75 rows=44,933 width=16) (actual time=2.908..22.697 rows=31,856 loops=1)

  • Merge Cond: (appg_1.ap_payee_location_id = cte.ap_payee_location_id)
  • Join Filter: ((cte.cid = appg_1.cid) AND (cte.ap_payee_id = appg_1.ap_payee_id))
8. 14.316 14.316 ↑ 1.4 32,776 1

Index Scan using idx_ap_payee_property_groups_ap_payee_location_id on ap_payee_property_groups appg_1 (cost=0.29..1,653.27 rows=44,933 width=16) (actual time=0.012..14.316 rows=32,776 loops=1)

  • Filter: ((cid = 3395) AND (property_group_id <> (100000000 + cid)))
  • Rows Removed by Filter: 12383
9. 2.480 3.617 ↓ 12,382.0 12,382 1

Sort (cost=5.09..5.10 rows=1 width=12) (actual time=2.891..3.617 rows=12,382 loops=1)

  • Sort Key: cte.ap_payee_location_id
  • Sort Method: quicksort Memory: 965kB
10. 1.137 1.137 ↓ 12,382.0 12,382 1

CTE Scan on cte_unlocked_ap_payee_locations cte (cost=0.00..5.08 rows=1 width=12) (actual time=0.001..1.137 rows=12,382 loops=1)

  • Filter: (cid = 3395)
11. 0.104 3.659 ↑ 1.1 956 1

Hash (cost=255.39..255.39 rows=1,015 width=8) (actual time=3.658..3.659 rows=956 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
12. 0.945 3.555 ↑ 1.1 956 1

HashAggregate (cost=245.24..255.39 rows=1,015 width=8) (actual time=3.437..3.555 rows=956 loops=1)

  • Group Key: pga.property_group_id
13. 1.358 2.610 ↓ 1.0 6,153 1

Hash Join (cost=70.05..230.30 rows=5,977 width=8) (actual time=0.562..2.610 rows=6,153 loops=1)

  • Hash Cond: ((pga.cid = pg.cid) AND (pga.property_group_id = pg.id))
14. 0.714 0.714 ↑ 1.0 6,505 1

Seq Scan on property_group_associations pga (cost=0.00..126.05 rows=6,505 width=8) (actual time=0.019..0.714 rows=6,505 loops=1)

15. 0.210 0.538 ↓ 1.0 1,735 1

Hash (cost=45.10..45.10 rows=1,663 width=8) (actual time=0.538..0.538 rows=1,735 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 84kB
16. 0.328 0.328 ↓ 1.0 1,735 1

Seq Scan on property_groups pg (cost=0.00..45.10 rows=1,663 width=8) (actual time=0.007..0.328 rows=1,735 loops=1)

  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL))
  • Rows Removed by Filter: 75
17. 6.863 409.678 ↓ 2.2 99,749 1

Append (cost=5,795.19..15,221.43 rows=46,226 width=80) (actual time=144.655..409.678 rows=99,749 loops=1)

18. 1.421 157.053 ↑ 2.9 14,841 1

Subquery Scan on *SELECT* 1 (cost=5,795.19..6,968.84 rows=42,678 width=66) (actual time=144.654..157.053 rows=14,841 loops=1)

19. 9.011 155.632 ↑ 2.9 14,841 1

WindowAgg (cost=5,795.19..6,542.06 rows=42,678 width=70) (actual time=144.653..155.632 rows=14,841 loops=1)

20. 7.753 146.621 ↑ 2.9 14,841 1

Sort (cost=5,795.19..5,901.89 rows=42,678 width=43) (actual time=144.642..146.621 rows=14,841 loops=1)

  • Sort Key: apl.ap_payee_id
  • Sort Method: quicksort Memory: 2153kB
21. 7.500 138.868 ↑ 2.9 14,841 1

Hash Join (cost=1,836.91..2,513.00 rows=42,678 width=43) (actual time=69.938..138.868 rows=14,841 loops=1)

  • Hash Cond: (apl.ap_payee_id = ap.id)
22. 3.513 122.697 ↑ 3.7 26,034 1

Nested Loop (cost=620.93..1,043.26 rows=96,657 width=29) (actual time=61.213..122.697 rows=26,034 loops=1)

23. 11.601 67.116 ↓ 130.2 26,034 1

HashAggregate (cost=620.51..622.51 rows=200 width=4) (actual time=61.157..67.116 rows=26,034 loops=1)

  • Group Key: cte_unlocked_ap_payee_locations.ap_payee_location_id
24. 1.917 55.515 ↓ 1.2 26,034 1

Append (cost=0.00..564.10 rows=22,564 width=4) (actual time=0.012..55.515 rows=26,034 loops=1)

25. 7.895 7.895 ↓ 54.8 12,382 1

CTE Scan on cte_unlocked_ap_payee_locations (cost=0.00..4.52 rows=226 width=4) (actual time=0.012..7.895 rows=12,382 loops=1)

26. 45.703 45.703 ↑ 1.6 13,652 1

CTE Scan on cte_restricted_ap_payee_locations (cost=0.00..446.76 rows=22,338 width=4) (actual time=39.486..45.703 rows=13,652 loops=1)

27. 52.068 52.068 ↑ 1.0 1 26,034

Index Scan using idx_ap_payee_locations_id on ap_payee_locations apl (cost=0.42..2.44 rows=1 width=29) (actual time=0.002..0.002 rows=1 loops=26,034)

  • Index Cond: (id = cte_unlocked_ap_payee_locations.ap_payee_location_id)
  • Filter: (cid = 3395)
28. 2.137 8.671 ↑ 1.0 13,660 1

Hash (cost=1,044.08..1,044.08 rows=13,752 width=26) (actual time=8.671..8.671 rows=13,660 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 995kB
29. 6.534 6.534 ↑ 1.0 13,660 1

Index Scan using idx_ap_payees_ap_payee_type_id on ap_payees ap (cost=0.29..1,044.08 rows=13,752 width=26) (actual time=0.033..6.534 rows=13,660 loops=1)

  • Index Cond: (ap_payee_type_id = ANY ('{1,2,3,8}'::integer[]))
  • Filter: ((company_name IS NOT NULL) AND (cid = 3395) AND (ap_payee_status_type_id = 1))
  • Rows Removed by Filter: 177
30. 8.763 245.762 ↓ 23.9 84,908 1

Subquery Scan on *SELECT* 2 (cost=7,941.64..8,021.47 rows=3,548 width=64) (actual time=217.254..245.762 rows=84,908 loops=1)

31. 58.657 236.999 ↓ 23.9 84,908 1

HashAggregate (cost=7,941.64..7,977.12 rows=3,548 width=60) (actual time=217.253..236.999 rows=84,908 loops=1)

  • Group Key: ah.ap_payee_location_id, ah.lease_customer_id, ah.header_memo
32. 19.223 178.342 ↓ 30.1 106,759 1

Hash Join (cost=1,417.66..7,915.03 rows=3,548 width=24) (actual time=14.228..178.342 rows=106,759 loops=1)

  • Hash Cond: (ah.ap_payee_id = ap_1.id)
33. 9.939 156.833 ↓ 4.1 124,787 1

Nested Loop (cost=620.93..7,039.34 rows=30,076 width=32) (actual time=11.931..156.833 rows=124,787 loops=1)

34. 12.179 16.724 ↓ 130.2 26,034 1

HashAggregate (cost=620.51..622.51 rows=200 width=4) (actual time=11.559..16.724 rows=26,034 loops=1)

  • Group Key: cte_unlocked_ap_payee_locations_1.ap_payee_location_id
35. 1.772 4.545 ↓ 1.2 26,034 1

Append (cost=0.00..564.10 rows=22,564 width=4) (actual time=0.002..4.545 rows=26,034 loops=1)

36. 1.223 1.223 ↓ 54.8 12,382 1

CTE Scan on cte_unlocked_ap_payee_locations cte_unlocked_ap_payee_locations_1 (cost=0.00..4.52 rows=226 width=4) (actual time=0.001..1.223 rows=12,382 loops=1)

37. 1.550 1.550 ↑ 1.6 13,652 1

CTE Scan on cte_restricted_ap_payee_locations cte_restricted_ap_payee_locations_1 (cost=0.00..446.76 rows=22,338 width=4) (actual time=0.001..1.550 rows=13,652 loops=1)

38. 130.170 130.170 ↑ 3.0 5 26,034

Index Scan using idx_ap_headers_ap_payee_location_id_partial on ap_headers ah (cost=0.42..31.93 rows=15 width=32) (actual time=0.001..0.005 rows=5 loops=26,034)

  • Index Cond: (ap_payee_location_id = cte_unlocked_ap_payee_locations_1.ap_payee_location_id)
  • Filter: ((deleted_on IS NULL) AND (header_memo IS NOT NULL) AND (cid = 3395) AND (ap_header_type_id = 5))
  • Rows Removed by Filter: 2
39. 0.426 2.286 ↓ 1.0 3,695 1

Hash (cost=750.80..750.80 rows=3,674 width=8) (actual time=2.285..2.286 rows=3,695 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 177kB
40. 1.860 1.860 ↓ 1.0 3,695 1

Index Scan using idx_ap_payees_ap_payee_type_id on ap_payees ap_1 (cost=0.29..750.80 rows=3,674 width=8) (actual time=0.012..1.860 rows=3,695 loops=1)

  • Index Cond: (ap_payee_type_id = ANY ('{4,7}'::integer[]))
  • Filter: ((cid = 3395) AND (ap_payee_status_type_id = 1))
Planning time : 3.155 ms
Execution time : 620.059 ms