explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 11Ont

Settings
# exclusive inclusive rows x rows loops node
1. 3.206 4,814.304 ↓ 25.0 1,449 1

Unique (cost=23,561.45..23,561.74 rows=58 width=4) (actual time=4,808.245..4,814.304 rows=1,449 loops=1)

2. 16.285 4,811.098 ↓ 940.0 54,520 1

Sort (cost=23,561.45..23,561.59 rows=58 width=4) (actual time=4,808.243..4,811.098 rows=54,520 loops=1)

  • Sort Method: quicksort Memory: 3,921kB
  • Sort Key: office.cmh_id
3. 0.000 4,794.813 ↓ 940.0 54,520 1

Nested Loop Semi Join (cost=10,034.37..23,559.75 rows=58 width=4) (actual time=119.739..4,794.813 rows=54,520 loops=1)

  • Join Filter: (office.cmh_id = u0_1.cmh_id)
4. 351.379 351.379 ↓ 32.2 57,024 1

Nested Loop (cost=10,033.10..15,435.61 rows=1,770 width=16) (actual time=119.645..351.379 rows=57,024 loops=1)

5. 0.000 4,447.872 ↑ 1.0 1 57,024

Nested Loop (cost=1.26..4.58 rows=1 width=4) (actual time=0.078..0.078 rows=1 loops=57,024)

  • Join Filter: (office.cmh_id = office_provider.cmh_id)
6. 241.240 241.240 ↓ 15.6 7,925 1

Nested Loop (cost=10,032.68..14,750.96 rows=507 width=12) (actual time=119.627..241.240 rows=7,925 loops=1)

7. 3,706.560 3,706.560 ↑ 1.0 1 57,024

Nested Loop (cost=0.84..4.03 rows=1 width=42) (actual time=0.065..0.065 rows=1 loops=57,024)

8. 95.100 95.100 ↓ 1.4 7 7,925

Index Scan using office_provider_cmh_id_50ca7182 on office_provider (cost=0.42..1.29 rows=5 width=4) (actual time=0.004..0.012 rows=7 loops=7,925)

9. 0.000 708.760 ↑ 1.0 1 54,520

Index Only Scan using provider_pkey on provider u1_1 (cost=0.42..0.55 rows=1 width=19) (actual time=0.013..0.013 rows=1 loops=54,520)

  • Rows Removed by Filter: 5
  • Join Filter: (office.cmh_id = asset.cmh_id)
  • Index Cond: (id = (u0_1.contact_id)::text)
  • Index Cond: (cmh_id = asset.cmh_id)
  • Heap Fetches: 54,520
  • Filter: is_active
10. 3,185.919 3,185.919 ↓ 0.0 0 289,629

Index Scan using target_lists_targetlistattributevalue_provider_id_7e3631cf on target_lists_targetlistattributevalue u2_1 (cost=0.42..0.65 rows=1 width=19) (actual time=0.011..0.011 rows=0 loops=289,629)

11. 456.192 456.192 ↑ 1.0 5 57,024

Index Scan using office_provider_cmh_id_50ca7182 on office_provider u0_1 (cost=0.42..0.75 rows=5 width=23) (actual time=0.004..0.008 rows=5 loops=57,024)

12. 84.530 84.530 ↑ 2.0 1 8,453

Index Scan using asset_cmh_id_3cb6b155 on asset (cost=0.42..1.49 rows=2 width=4) (actual time=0.007..0.010 rows=1 loops=8,453)

13. 16.173 155.198 ↓ 13.0 8,453 1

Hash Semi Join (cost=10,032.26..13,770.09 rows=648 width=8) (actual time=119.510..155.198 rows=8,453 loops=1)

  • Rows Removed by Filter: 4
  • Rows Removed by Filter: 3
  • Rows Removed by Filter: 1
  • Index Cond: (cmh_id = u0.cmh_id)
  • Index Cond: (cmh_id = office_provider.cmh_id)
  • Index Cond: ((provider_id)::text = (u0_1.contact_id)::text)
  • Hash Cond: (office.cmh_id = u0.cmh_id)
  • Filter: is_active
  • Filter: ((target_list_attribute_id = 183) AND ((string_value)::text = ANY ('{FM,IM,END,DIA,NRP}'::text[])))
  • Filter: (((asset_type_id)::text = 'WB-S'::text) AND ((status)::text = 'Installed'::text))
14. 19.545 19.545 ↓ 2.0 49,782 1

Seq Scan on office (cost=8.16..3,666.15 rows=24,893 width=4) (actual time=0.011..19.545 rows=49,782 loops=1)

15. 2.856 119.480 ↓ 9.7 12,689 1

Hash (cost=10,007.81..10,007.81 rows=1,303 width=4) (actual time=119.480..119.480 rows=12,689 loops=1)

  • Rows Removed by Filter: 297
  • Filter: (((NOT sponsor_opt_out) OR (sponsor_opt_out IS NULL)) AND (NOT (hashed SubPlan 1)))
  • Buckets: 16,384 (originally 2048) Batches: 1 (originally 1) Memory Usage: 575kB
16. 116.619 116.624 ↓ 9.7 12,689 1

Gather (cost=2,852.01..10,007.81 rows=1,303 width=4) (actual time=29.123..116.624 rows=12,689 loops=1)

17. 0.000 0.005 ↓ 0.0 0 1

Index Scan using office_opportunity_outs_opportunity_id_4491a0ef_like on office_opportunity_outs u1_2 (cost=0.14..8.16 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
18. 1.948 101.150 ↓ 7.8 4,230 3 / 3

Nested Loop (cost=1,852.01..8,877.51 rows=543 width=4) (actual time=26.419..101.150 rows=4,230 loops=3)

  • Index Cond: ((opportunity_id)::text = '0060h00001Bu7N5AAJ'::text)
19. 27.371 27.371 ↑ 1.0 1 10,264 / 3

Index Scan using office_provider_contact_id_662b1581_like on office_provider u0 (cost=0.42..0.58 rows=1 width=23) (actual time=0.007..0.008 rows=1 loops=10,264)

20. 29.591 71.831 ↓ 4.0 3,421 3 / 3

Hash Join (cost=1,851.59..8,365.48 rows=862 width=38) (actual time=26.396..71.831 rows=3,421 loops=3)

  • Rows Removed by Filter: 0
  • Index Cond: ((contact_id)::text = (u1.id)::text)
  • Hash Cond: ((u1.id)::text = (u2.provider_id)::text)
  • Filter: is_active
21. 15.937 15.937 ↑ 1.2 92,562 3 / 3

Parallel Seq Scan on provider u1 (cost=0.00..4,481.46 rows=115,646 width=19) (actual time=0.005..15.937 rows=92,562 loops=3)

22. 2.782 26.303 ↓ 5.0 10,264 3 / 3

Hash (cost=1,825.72..1,825.72 rows=2,069 width=19) (actual time=26.303..26.303 rows=10,264 loops=3)

  • Buckets: 16,384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 640kB
23. 23.521 23.521 ↓ 5.0 10,264 3 / 3

Seq Scan on target_lists_targetlistattributevalue u2 (cost=0.00..1,825.72 rows=2,069 width=19) (actual time=4.178..23.521 rows=10,264 loops=3)

  • Rows Removed by Filter: 61,051
  • Filter: ((float_value = '0'::double precision) AND (target_list_attribute_id = 186))
Planning time : 6.367 ms
Execution time : 4,814.886 ms