explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lj7E

Settings
# exclusive inclusive rows x rows loops node
1. 0.061 106.471 ↓ 54.0 108 1

HashAggregate (cost=305.60..305.63 rows=2 width=40) (actual time=106.452..106.471 rows=108 loops=1)

  • Group Key: locations_business_types.location_id, locations_business_types.new_business_type_id
2.          

CTE subcategories_business_types

3. 0.057 0.057 ↑ 1.0 179 1

Values Scan on "*VALUES*" (cost=0.00..2.24 rows=179 width=64) (actual time=0.002..0.057 rows=179 loops=1)

4.          

CTE locations_without_secondary_business_types

5. 0.024 0.113 ↓ 42.0 42 1

Hash Right Join (cost=193.94..222.25 rows=1 width=12) (actual time=0.099..0.113 rows=42 loops=1)

  • Hash Cond: (lsbt.location_id = l.id)
  • Filter: (lsbt.id IS NULL)
6. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on location_secondary_business_types lsbt (cost=0.00..24.50 rows=1,450 width=8) (actual time=0.003..0.003 rows=0 loops=1)

7. 0.014 0.086 ↑ 1.2 42 1

Hash (cost=193.33..193.33 rows=49 width=12) (actual time=0.086..0.086 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
8. 0.057 0.072 ↑ 1.2 42 1

Bitmap Heap Scan on locations l (cost=4.80..193.33 rows=49 width=12) (actual time=0.023..0.072 rows=42 loops=1)

  • Recheck Cond: (provider_id = 9837)
  • Heap Blocks: exact=38
9. 0.015 0.015 ↑ 1.2 42 1

Bitmap Index Scan on index_locations_on_provider_id (cost=0.00..4.79 rows=49 width=0) (actual time=0.015..0.015 rows=42 loops=1)

  • Index Cond: (provider_id = 9837)
10.          

CTE locations_business_types

11. 5.691 106.379 ↓ 54.0 108 1

GroupAggregate (cost=81.02..81.06 rows=2 width=16) (actual time=99.207..106.379 rows=108 loops=1)

  • Group Key: l_1.id, nbt.id
12. 7.716 100.688 ↓ 15,818.5 31,637 1

Sort (cost=81.02..81.03 rows=2 width=12) (actual time=99.189..100.688 rows=31,637 loops=1)

  • Sort Key: l_1.id, nbt.id
  • Sort Method: quicksort Memory: 2251kB
13. 6.519 92.972 ↓ 15,818.5 31,637 1

Hash Join (cost=13.84..81.01 rows=2 width=12) (actual time=0.801..92.972 rows=31,637 loops=1)

  • Hash Cond: (s.subcategory_id = sub.id)
  • Join Filter: (l_1.primary_business_type_id <> nbt.id)
14. 24.550 85.804 ↓ 597.2 32,248 1

Nested Loop (cost=1.72..68.67 rows=54 width=16) (actual time=0.144..85.804 rows=32,248 loops=1)

15. 4.527 19.856 ↓ 600.0 41,398 1

Nested Loop (cost=1.29..36.21 rows=69 width=12) (actual time=0.133..19.856 rows=41,398 loops=1)

16. 0.027 1.341 ↓ 67.2 269 1

Nested Loop (cost=0.85..17.03 rows=4 width=16) (actual time=0.120..1.341 rows=269 loops=1)

17. 0.041 0.507 ↓ 53.8 269 1

Nested Loop (cost=0.42..13.40 rows=5 width=12) (actual time=0.110..0.507 rows=269 loops=1)

18. 0.130 0.130 ↓ 42.0 42 1

CTE Scan on locations_without_secondary_business_types l_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.100..0.130 rows=42 loops=1)

  • Filter: (provider_id = 9837)
19. 0.336 0.336 ↓ 1.2 6 42

Index Scan using index_employee_location_assignments_on_location_id on employee_location_assignments ela (cost=0.42..13.33 rows=5 width=8) (actual time=0.003..0.008 rows=6 loops=42)

  • Index Cond: (location_id = l_1.id)
20. 0.807 0.807 ↑ 1.0 1 269

Index Scan using employees_pkey on employees e (cost=0.43..0.73 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=269)

  • Index Cond: (id = ela.employee_id)
  • Filter: (deleted_at IS NULL)
21. 13.988 13.988 ↓ 1.2 154 269

Index Scan using index_employees_services_on_employee_id on employees_services es (cost=0.44..3.50 rows=129 width=8) (actual time=0.005..0.052 rows=154 loops=269)

  • Index Cond: (employee_id = e.id)
22. 41.398 41.398 ↑ 1.0 1 41,398

Index Scan using services_pkey on services s (cost=0.43..0.47 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=41,398)

  • Index Cond: (id = es.service_id)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 0
23. 0.027 0.649 ↓ 11.9 167 1

Hash (cost=11.94..11.94 rows=14 width=8) (actual time=0.649..0.649 rows=167 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
24. 0.093 0.622 ↓ 11.9 167 1

Nested Loop (cost=1.59..11.94 rows=14 width=8) (actual time=0.056..0.622 rows=167 loops=1)

25. 0.064 0.193 ↓ 12.0 168 1

Hash Join (cost=1.32..5.38 rows=14 width=36) (actual time=0.036..0.193 rows=168 loops=1)

  • Hash Cond: (sub_bt.business_type_name = (nbt.name)::text)
26. 0.120 0.120 ↑ 1.0 179 1

CTE Scan on subcategories_business_types sub_bt (cost=0.00..3.58 rows=179 width=64) (actual time=0.003..0.120 rows=179 loops=1)

27. 0.005 0.009 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=16) (actual time=0.009..0.009 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.004 0.004 ↑ 1.0 14 1

Seq Scan on new_business_types nbt (cost=0.00..1.14 rows=14 width=16) (actual time=0.002..0.004 rows=14 loops=1)

29. 0.336 0.336 ↑ 1.0 1 168

Index Scan using index_subcategories_on_name on subcategories sub (cost=0.27..0.47 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=168)

  • Index Cond: ((name)::text = sub_bt.subcategory_name)
30. 106.410 106.410 ↓ 54.0 108 1

CTE Scan on locations_business_types (cost=0.00..0.04 rows=2 width=16) (actual time=99.209..106.410 rows=108 loops=1)

Planning time : 3.386 ms
Execution time : 106.815 ms