explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UtXR

Settings
# exclusive inclusive rows x rows loops node
1. 74.421 1,973.375 ↓ 68.7 3,162 1

Unique (cost=21,120.17..21,120.86 rows=46 width=84) (actual time=1,868.791..1,973.375 rows=3,162 loops=1)

2.          

Initplan (for Unique)

3. 0.001 0.080 ↑ 6.0 1 1

Nested Loop (cost=0.55..7.46 rows=6 width=0) (actual time=0.080..0.080 rows=1 loops=1)

4. 0.044 0.044 ↑ 2.0 1 1

Index Scan using pk_property_groups on property_groups pg (cost=0.27..4.06 rows=2 width=8) (actual time=0.044..0.044 rows=1 loops=1)

  • Index Cond: ((cid = 15573) AND (id = ANY ('{631399,631408}'::integer[])))
  • Filter: (deleted_on IS NULL)
5. 0.035 0.035 ↑ 6.0 1 1

Index Only Scan using idx_property_group_associations_master on property_group_associations pga (cost=0.28..1.52 rows=6 width=8) (actual time=0.035..0.035 rows=1 loops=1)

  • Index Cond: ((cid = 15573) AND (property_group_id = pg.id))
  • Heap Fetches: 0
6. 392.596 1,898.874 ↓ 8,342.5 383,753 1

Sort (cost=21,118.47..21,118.58 rows=46 width=84) (actual time=1,868.788..1,898.874 rows=383,753 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: 57531kB
7. 215.489 1,506.278 ↓ 8,342.5 383,753 1

WindowAgg (cost=21,115.47..21,117.20 rows=46 width=84) (actual time=1,212.418..1,506.278 rows=383,753 loops=1)

8. 232.657 1,290.789 ↓ 8,342.5 383,753 1

Sort (cost=21,115.47..21,115.59 rows=46 width=80) (actual time=1,212.394..1,290.789 rows=383,753 loops=1)

  • Sort Key: apl.ap_payee_id
  • Sort Method: quicksort Memory: 62886kB
9. 44.923 1,058.132 ↓ 8,342.5 383,753 1

Result (cost=1,675.88..21,114.20 rows=46 width=80) (actual time=6.195..1,058.132 rows=383,753 loops=1)

  • One-Time Filter: $1
10. 241.593 1,013.209 ↓ 8,342.5 383,753 1

Nested Loop (cost=1,675.88..21,114.20 rows=46 width=80) (actual time=6.113..1,013.209 rows=383,753 loops=1)

  • Join Filter: (apl.ap_payee_id = ap.id)
11. 183.421 375.656 ↓ 2,749.7 395,960 1

Gather (cost=1,675.60..21,062.12 rows=144 width=70) (actual time=6.069..375.656 rows=395,960 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 56.251 192.235 ↓ 2,199.8 131,987 3 / 3

Hash Join (cost=675.60..20,047.72 rows=60 width=70) (actual time=5.600..192.235 rows=131,987 loops=3)

  • Hash Cond: ((ah.ap_payee_id = apl.ap_payee_id) AND (ah.ap_payee_location_id = apl.id))
13. 130.562 130.562 ↑ 1.2 131,987 3 / 3

Parallel Seq Scan on ap_headers ah (cost=0.00..18,506.72 rows=164,838 width=37) (actual time=0.031..130.562 rows=131,987 loops=3)

  • Filter: ((deleted_on IS NULL) AND (ap_header_type_id = ANY ('{4,5}'::integer[])) AND (cid = 15573))
  • Rows Removed by Filter: 1251
14. 1.712 5.422 ↓ 1.0 6,514 3 / 3

Hash (cost=447.65..447.65 rows=6,513 width=37) (actual time=5.422..5.422 rows=6,514 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 511kB
15. 3.710 3.710 ↓ 1.0 6,514 3 / 3

Seq Scan on ap_payee_locations apl (cost=0.00..447.65 rows=6,513 width=37) (actual time=0.019..3.710 rows=6,514 loops=3)

  • Filter: ((deleted_on IS NULL) AND (disabled_on IS NULL) AND (cid = 15573))
  • Rows Removed by Filter: 30
16. 395.960 395.960 ↑ 1.0 1 395,960

Index Scan using idx_ap_payees_id on ap_payees ap (cost=0.28..0.33 rows=1 width=30) (actual time=0.001..0.001 rows=1 loops=395,960)

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