explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gUf0

Settings
# exclusive inclusive rows x rows loops node
1. 0.818 93.795 ↓ 2.4 22 1

GroupAggregate (cost=2,058.85..2,059.03 rows=9 width=16) (actual time=92.773..93.795 rows=22 loops=1)

  • Group Key: l.id, nbt.id
2.          

CTE subcategories_business_types

3. 0.003 0.003 ↑ 1.0 3 1

Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=64) (actual time=0.002..0.003 rows=3 loops=1)

4. 1.219 92.977 ↓ 504.3 4,539 1

Sort (cost=2,058.82..2,058.84 rows=9 width=12) (actual time=92.736..92.977 rows=4,539 loops=1)

  • Sort Key: l.id, nbt.id
  • Sort Method: quicksort Memory: 405kB
5. 3.126 91.758 ↓ 504.3 4,539 1

Nested Loop (cost=15.74..2,058.67 rows=9 width=12) (actual time=0.231..91.758 rows=4,539 loops=1)

  • Join Filter: ((l.primary_business_type_id <> nbt.id) AND (sub_bt.business_type_name = (nbt.name)::text))
  • Rows Removed by Join Filter: 13617
6. 3.010 88.632 ↓ 453.9 4,539 1

Hash Join (cost=15.74..2,055.26 rows=10 width=44) (actual time=0.224..88.632 rows=4,539 loops=1)

  • Hash Cond: (s.subcategory_id = sub.id)
7. 24.335 85.526 ↓ 27.4 32,248 1

Nested Loop (cost=6.52..2,041.54 rows=1,175 width=16) (actual time=0.063..85.526 rows=32,248 loops=1)

8. 4.804 19.793 ↓ 27.7 41,398 1

Nested Loop (cost=6.09..1,339.08 rows=1,493 width=12) (actual time=0.052..19.793 rows=41,398 loops=1)

9. 0.023 1.270 ↓ 3.1 269 1

Nested Loop (cost=5.65..926.74 rows=86 width=16) (actual time=0.041..1.270 rows=269 loops=1)

10. 0.037 0.440 ↓ 2.4 269 1

Nested Loop (cost=5.23..844.05 rows=114 width=12) (actual time=0.032..0.440 rows=269 loops=1)

11. 0.057 0.067 ↑ 1.2 42 1

Bitmap Heap Scan on locations l (cost=4.80..193.33 rows=49 width=8) (actual time=0.019..0.067 rows=42 loops=1)

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

  • Index Cond: (provider_id = 9837)
13. 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.23 rows=5 width=8) (actual time=0.003..0.008 rows=6 loops=42)

  • Index Cond: (location_id = l.id)
14. 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)
15. 13.719 13.719 ↓ 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.051 rows=154 loops=269)

  • Index Cond: (employee_id = e.id)
16. 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
17. 0.003 0.096 ↑ 1.0 3 1

Hash (cost=9.17..9.17 rows=3 width=36) (actual time=0.096..0.096 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.058 0.093 ↑ 1.0 3 1

Hash Join (cost=0.10..9.17 rows=3 width=36) (actual time=0.056..0.093 rows=3 loops=1)

  • Hash Cond: ((sub.name)::text = sub_bt.subcategory_name)
19. 0.025 0.025 ↑ 1.0 367 1

Seq Scan on subcategories sub (cost=0.00..7.67 rows=367 width=21) (actual time=0.003..0.025 rows=367 loops=1)

20. 0.005 0.010 ↑ 1.0 3 1

Hash (cost=0.06..0.06 rows=3 width=64) (actual time=0.010..0.010 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.005 0.005 ↑ 1.0 3 1

CTE Scan on subcategories_business_types sub_bt (cost=0.00..0.06 rows=3 width=64) (actual time=0.004..0.005 rows=3 loops=1)

22. 0.000 0.000 ↑ 3.5 4 4,539

Materialize (cost=0.00..1.21 rows=14 width=16) (actual time=0.000..0.000 rows=4 loops=4,539)

23. 0.003 0.003 ↑ 3.5 4 1

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