explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DosG

Settings
# exclusive inclusive rows x rows loops node
1. 210.557 615.976 ↓ 2.2 99,749 1

Sort (cost=20,919.96..21,035.25 rows=46,118 width=80) (actual time=602.391..615.976 rows=99,749 loops=1)

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

CTE cte_unlocked_ap_payee_locations

3. 4.868 4.868 ↓ 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..4.868 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. 9.954 41.159 ↑ 1.6 13,652 1

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

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

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

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

Merge Anti Join (cost=5.38..1,770.75 rows=44,933 width=16) (actual time=2.908..22.057 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. 13.664 13.664 ↑ 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.013..13.664 rows=32,776 loops=1)

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

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

  • Sort Key: cte.ap_payee_location_id
  • Sort Method: quicksort Memory: 965kB
10. 1.144 1.144 ↓ 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.144 rows=12,382 loops=1)

  • Filter: (cid = 3395)
11. 0.107 3.519 ↑ 1.1 956 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
12. 0.974 3.412 ↑ 1.1 956 1

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

  • Group Key: pga.property_group_id
13. 1.352 2.438 ↓ 1.0 6,153 1

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

  • Hash Cond: ((pga.cid = pg.cid) AND (pga.property_group_id = pg.id))
14. 0.569 0.569 ↑ 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.009..0.569 rows=6,505 loops=1)

15. 0.208 0.517 ↓ 1.0 1,735 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 84kB
16. 0.309 0.309 ↓ 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.309 rows=1,735 loops=1)

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

Append (cost=5,795.19..12,874.11 rows=46,118 width=80) (actual time=136.294..405.419 rows=99,749 loops=1)

18. 1.550 148.973 ↑ 2.9 14,841 1

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

19. 9.019 147.423 ↑ 2.9 14,841 1

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

20. 7.404 138.404 ↑ 2.9 14,841 1

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

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

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

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

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

23. 11.512 65.587 ↓ 130.2 26,034 1

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

  • Group Key: cte_unlocked_ap_payee_locations.ap_payee_location_id
24. 1.972 54.075 ↓ 1.2 26,034 1

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

25. 7.635 7.635 ↓ 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.635 rows=12,382 loops=1)

26. 44.468 44.468 ↑ 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=38.420..44.468 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.035 7.364 ↑ 1.0 13,660 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 995kB
29. 5.329 5.329 ↑ 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.019..5.329 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.881 249.610 ↓ 24.7 84,908 1

Subquery Scan on *SELECT* 2 (cost=5,597.28..5,674.68 rows=3,440 width=64) (actual time=220.419..249.610 rows=84,908 loops=1)

31. 59.431 240.729 ↓ 24.7 84,908 1

HashAggregate (cost=5,597.28..5,631.68 rows=3,440 width=60) (actual time=220.417..240.729 rows=84,908 loops=1)

  • Group Key: ah.ap_payee_location_id, ah.lease_customer_id, ah.header_memo
32. 18.913 181.298 ↓ 31.0 106,759 1

Hash Join (cost=1,417.66..5,571.48 rows=3,440 width=24) (actual time=12.643..181.298 rows=106,759 loops=1)

  • Hash Cond: (ah.ap_payee_id = ap_1.id)
33. 14.769 160.186 ↓ 4.3 124,787 1

Nested Loop (cost=620.93..4,698.19 rows=29,164 width=32) (actual time=10.431..160.186 rows=124,787 loops=1)

34. 10.774 15.247 ↓ 130.2 26,034 1

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

  • Group Key: cte_unlocked_ap_payee_locations_1.ap_payee_location_id
35. 1.801 4.473 ↓ 1.2 26,034 1

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

36. 1.197 1.197 ↓ 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.002..1.197 rows=12,382 loops=1)

37. 1.475 1.475 ↑ 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.475 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..20.23 rows=15 width=32) (actual time=0.001..0.005 rows=5 loops=26,034)

  • Index Cond: ((cid = 3395) AND (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 (ap_header_type_id = 5))
  • Rows Removed by Filter: 2
39. 0.429 2.199 ↓ 1.0 3,695 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 177kB
40. 1.770 1.770 ↓ 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.770 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 : 1.748 ms
Execution time : 620.581 ms