explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aquE

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.273 ↓ 0.0 0 1

HashAggregate (cost=875.84..875.86 rows=2 width=40) (actual time=0.273..0.273 rows=0 loops=1)

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

CTE subcategories_business_types

3. 0.000 0.000 ↓ 0.0 0

Values Scan on "*VALUES*" (cost=0.00..2.24 rows=179 width=64) (never executed)

4.          

CTE locations_without_secondary_business_types

5. 0.012 0.206 ↓ 19.0 19 1

Nested Loop Left Join (cost=5.22..792.48 rows=1 width=12) (actual time=0.047..0.206 rows=19 loops=1)

  • Filter: (lsbt.id IS NULL)
  • Rows Removed by Filter: 46
6. 0.051 0.068 ↑ 1.2 42 1

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

  • Recheck Cond: (provider_id = 9837)
  • Heap Blocks: exact=38
7. 0.017 0.017 ↑ 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.017..0.017 rows=42 loops=1)

  • Index Cond: (provider_id = 9837)
8. 0.126 0.126 ↑ 2.0 1 42

Index Scan using index_location_secondary_business_types_on_location_id on location_secondary_business_types lsbt (cost=0.42..12.21 rows=2 width=8) (actual time=0.002..0.003 rows=1 loops=42)

  • Index Cond: (l.id = location_id)
9.          

CTE locations_business_types

10. 0.001 0.272 ↓ 0.0 0 1

GroupAggregate (cost=81.02..81.06 rows=2 width=16) (actual time=0.272..0.272 rows=0 loops=1)

  • Group Key: l_1.id, nbt.id
11. 0.006 0.271 ↓ 0.0 0 1

Sort (cost=81.02..81.03 rows=2 width=12) (actual time=0.271..0.271 rows=0 loops=1)

  • Sort Key: l_1.id, nbt.id
  • Sort Method: quicksort Memory: 25kB
12. 0.000 0.265 ↓ 0.0 0 1

Hash Join (cost=13.84..81.01 rows=2 width=12) (actual time=0.265..0.265 rows=0 loops=1)

  • Hash Cond: (s.subcategory_id = sub.id)
  • Join Filter: (l_1.primary_business_type_id <> nbt.id)
13. 0.000 0.265 ↓ 0.0 0 1

Nested Loop (cost=1.72..68.67 rows=54 width=16) (actual time=0.265..0.265 rows=0 loops=1)

14. 0.001 0.265 ↓ 0.0 0 1

Nested Loop (cost=1.29..36.21 rows=69 width=12) (actual time=0.265..0.265 rows=0 loops=1)

15. 0.000 0.264 ↓ 0.0 0 1

Nested Loop (cost=0.85..17.03 rows=4 width=16) (actual time=0.264..0.264 rows=0 loops=1)

16. 0.000 0.264 ↓ 0.0 0 1

Nested Loop (cost=0.42..13.40 rows=5 width=12) (actual time=0.264..0.264 rows=0 loops=1)

17. 0.212 0.212 ↓ 19.0 19 1

CTE Scan on locations_without_secondary_business_types l_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.048..0.212 rows=19 loops=1)

  • Filter: (provider_id = 9837)
18. 0.057 0.057 ↓ 0.0 0 19

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.003 rows=0 loops=19)

  • Index Cond: (location_id = l_1.id)
19. 0.000 0.000 ↓ 0.0 0

Index Scan using employees_pkey on employees e (cost=0.43..0.73 rows=1 width=4) (never executed)

  • Index Cond: (id = ela.employee_id)
  • Filter: (deleted_at IS NULL)
20. 0.000 0.000 ↓ 0.0 0

Index Scan using index_employees_services_on_employee_id on employees_services es (cost=0.44..3.50 rows=129 width=8) (never executed)

  • Index Cond: (employee_id = e.id)
21. 0.000 0.000 ↓ 0.0 0

Index Scan using services_pkey on services s (cost=0.43..0.47 rows=1 width=8) (never executed)

  • Index Cond: (id = es.service_id)
  • Filter: (deleted_at IS NULL)
22. 0.000 0.000 ↓ 0.0 0

Hash (cost=11.94..11.94 rows=14 width=8) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.59..11.94 rows=14 width=8) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1.32..5.38 rows=14 width=36) (never executed)

  • Hash Cond: (sub_bt.business_type_name = (nbt.name)::text)
25. 0.000 0.000 ↓ 0.0 0

CTE Scan on subcategories_business_types sub_bt (cost=0.00..3.58 rows=179 width=64) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.14..1.14 rows=14 width=16) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Seq Scan on new_business_types nbt (cost=0.00..1.14 rows=14 width=16) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Index Scan using index_subcategories_on_name on subcategories sub (cost=0.27..0.47 rows=1 width=21) (never executed)

  • Index Cond: ((name)::text = sub_bt.subcategory_name)
29. 0.272 0.272 ↓ 0.0 0 1

CTE Scan on locations_business_types (cost=0.00..0.04 rows=2 width=16) (actual time=0.272..0.272 rows=0 loops=1)

Planning time : 3.243 ms
Execution time : 0.440 ms