explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UsWP

Settings
# exclusive inclusive rows x rows loops node
1. 87.270 1,999.327 ↓ 17,188.2 154,694 1

Unique (cost=28,129.17..28,129.31 rows=9 width=84) (actual time=1,862.804..1,999.327 rows=154,694 loops=1)

2.          

Initplan (for Unique)

3. 0.002 0.284 ↑ 277.0 1 1

Nested Loop (cost=0.28..105.84 rows=277 width=0) (actual time=0.283..0.284 rows=1 loops=1)

4. 0.254 0.254 ↑ 1.0 1 1

Seq Scan on property_groups pg (cost=0.00..61.06 rows=1 width=8) (actual time=0.254..0.254 rows=1 loops=1)

  • Filter: ((deleted_on IS NULL) AND (cid = 3395) AND (id = 200006517))
  • Rows Removed by Filter: 1835
5. 0.028 0.028 ↑ 277.0 1 1

Index Only Scan using idx_property_group_associations_master on property_group_associations pga (cost=0.28..42.01 rows=277 width=8) (actual time=0.028..0.028 rows=1 loops=1)

  • Index Cond: ((cid = 3395) AND (property_group_id = 200006517))
  • Heap Fetches: 1
6. 799.937 1,911.773 ↓ 60,516.0 544,644 1

Sort (cost=28,128.51..28,128.53 rows=9 width=84) (actual time=1,862.803..1,911.773 rows=544,644 loops=1)

  • Sort Key: (CASE WHEN (ah.lease_customer_id IS NOT NULL) THEN ah.header_memo ELSE ap.company_name END), (CASE WHEN (ah.lease_customer_id IS NULL) THEN COALESCE(apl.vendor_code, apl.location_name) ELSE NULL::character varying END), apl.id, (CASE WHEN (ah.lease_customer_id IS NOT NULL) THEN ah.lease_customer_id ELSE NULL::integer END), (count(apl.id) OVER (?))
  • Sort Method: quicksort Memory: 80932kB
7. 265.465 1,111.836 ↓ 60,516.0 544,644 1

WindowAgg (cost=28,128.21..28,128.37 rows=9 width=84) (actual time=822.096..1,111.836 rows=544,644 loops=1)

8. 218.612 846.371 ↓ 60,516.0 544,644 1

Sort (cost=28,128.21..28,128.23 rows=9 width=64) (actual time=769.488..846.371 rows=544,644 loops=1)

  • Sort Key: apl.ap_payee_id
  • Sort Method: quicksort Memory: 91449kB
9. 18.089 627.759 ↓ 60,516.0 544,644 1

Gather (cost=6,381.15..28,128.07 rows=9 width=64) (actual time=42.366..627.759 rows=544,644 loops=1)

  • Workers Planned: 2
  • Params Evaluated: $0
  • Workers Launched: 2
10. 21.092 609.670 ↓ 45,387.0 181,548 3 / 3

Result (cost=5,381.15..27,127.17 rows=4 width=64) (actual time=37.776..609.670 rows=181,548 loops=3)

  • One-Time Filter: $0
11. 0.000 588.578 ↓ 45,387.0 181,548 3 / 3

Nested Loop (cost=5,381.15..27,127.17 rows=4 width=64) (actual time=37.775..588.578 rows=181,548 loops=3)

  • Join Filter: (apl.ap_payee_id = ap.id)
12. 67.435 215.887 ↓ 5,380.3 188,309 3 / 3

Parallel Hash Join (cost=5,380.86..27,115.47 rows=35 width=58) (actual time=37.739..215.887 rows=188,309 loops=3)

  • Hash Cond: ((ah.ap_payee_id = apl.ap_payee_id) AND (ah.ap_payee_location_id = apl.id))
13. 111.312 111.312 ↑ 1.3 188,310 3 / 3

Parallel Seq Scan on ap_headers ah (cost=0.00..20,498.82 rows=235,388 width=33) (actual time=0.012..111.312 rows=188,310 loops=3)

  • Filter: ((deleted_on IS NULL) AND (ap_header_type_id = ANY ('{4,5}'::integer[])) AND (cid = 3395))
  • Rows Removed by Filter: 1
14. 18.459 37.140 ↑ 1.3 65,698 3 / 3

Parallel Hash (cost=4,148.81..4,148.81 rows=82,137 width=29) (actual time=37.140..37.140 rows=65,698 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 13120kB
15. 18.681 18.681 ↑ 1.3 65,698 3 / 3

Parallel Seq Scan on ap_payee_locations apl (cost=0.00..4,148.81 rows=82,137 width=29) (actual time=0.020..18.681 rows=65,698 loops=3)

  • Filter: ((deleted_on IS NULL) AND (disabled_on IS NULL) AND (cid = 3395))
  • Rows Removed by Filter: 81
16. 376.618 376.618 ↑ 1.0 1 564,927 / 3

Index Scan using idx_ap_payees_id on ap_payees ap (cost=0.29..0.32 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=564,927)

  • Index Cond: (id = ah.ap_payee_id)
  • Filter: ((company_name IS NOT NULL) AND (cid = 3395) AND (ap_payee_status_type_id = 1) AND (ap_payee_type_id = ANY ('{1,2,3,4,7,8}'::integer[])))
  • Rows Removed by Filter: 0
Planning time : 1.265 ms