explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uBEX

Settings
# exclusive inclusive rows x rows loops node
1. 0.294 4,880.549 ↓ 99.0 99 1

Nested Loop (cost=3,034.19..7,024.11 rows=1 width=20) (actual time=51.408..4,880.549 rows=99 loops=1)

2. 0.213 4,879.643 ↓ 204.0 204 1

Nested Loop Semi Join (cost=3,034.05..7,023.83 rows=1 width=24) (actual time=32.731..4,879.643 rows=204 loops=1)

3. 0.227 4,857.495 ↓ 205.0 205 1

Nested Loop (cost=19.09..662.94 rows=1 width=28) (actual time=21.242..4,857.495 rows=205 loops=1)

4. 0.338 2.990 ↓ 26.0 26 1

Index Scan using service_categories_name_key on service_categories a_service_categories_1 (cost=0.14..633.36 rows=1 width=20) (actual time=0.213..2.990 rows=26 loops=1)

  • Filter: (((active)::smallint = 1::smallint) AND (id = (SubPlan 3)))
  • Rows Removed by Filter: 152
5.          

SubPlan (for Index Scan)

6. 0.156 2.652 ↓ 0.0 0 156

Limit (cost=0.00..3.45 rows=1 width=4) (actual time=0.017..0.017 rows=0 loops=156)

7. 2.496 2.496 ↓ 0.0 0 156

Seq Scan on service_categories_clients_assoc (cost=0.00..3.45 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=156)

  • Filter: ((client_id = ANY ('{2}'::integer[])) AND (category_id = a_service_categories_1.id))
  • Rows Removed by Filter: 173
8. 1.280 4,854.278 ↓ 8.0 8 26

Bitmap Heap Scan on services a_services_6 (cost=18.94..29.57 rows=1 width=16) (actual time=21.782..186.703 rows=8 loops=26)

  • Recheck Cond: ((cat_id = a_service_categories_1.id) AND (client_id = ANY ('{2}'::integer[])))
  • Filter: (((active)::smallint = 1::smallint) AND (((visibility)::integer & 4) <> 0) AND (SubPlan 1))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=173
9. 0.078 1.118 ↓ 0.0 0 26

BitmapAnd (cost=18.94..18.94 rows=2 width=0) (actual time=0.043..0.043 rows=0 loops=26)

10. 0.234 0.234 ↑ 1.1 10 26

Bitmap Index Scan on services_cat_id_idx (cost=0.00..4.36 rows=11 width=0) (actual time=0.009..0.009 rows=10 loops=26)

  • Index Cond: (cat_id = a_service_categories_1.id)
11. 0.806 0.806 ↓ 1.0 272 26

Bitmap Index Scan on services_service_client_id_key (cost=0.00..14.30 rows=270 width=0) (actual time=0.031..0.031 rows=272 loops=26)

  • Index Cond: (client_id = ANY ('{2}'::integer[]))
12.          

SubPlan (for Bitmap Heap Scan)

13. 5.824 4,851.880 ↑ 149,858.0 1 220

Nested Loop (cost=0.56..187,900.67 rows=149,858 width=0) (actual time=22.054..22.054 rows=1 loops=220)

14. 369.160 4,811.400 ↑ 329.8 53 220

Nested Loop (cost=0.00..44,827.31 rows=17,481 width=4) (actual time=18.637..21.870 rows=53 loops=220)

  • Join Filter: (a.store_id = fa.store_id)
  • Rows Removed by Join Filter: 6,979
15. 4,091.780 4,091.780 ↑ 36.0 1 220

Seq Scan on store_assoc a (cost=0.00..4,233.14 rows=36 width=4) (actual time=18.599..18.599 rows=1 loops=220)

  • Filter: (assoc_id = 53,858)
  • Rows Removed by Filter: 250,092
16. 330.342 350.460 ↑ 10.4 7,031 220

Materialize (cost=0.00..1,416.33 rows=72,889 width=8) (actual time=0.001..1.593 rows=7,031 loops=220)

17. 20.118 20.118 ↑ 1.0 72,889 1

Seq Scan on fsa_store_assoc fa (cost=0.00..1,051.89 rows=72,889 width=8) (actual time=0.006..20.118 rows=72,889 loops=1)

18. 34.656 34.656 ↓ 0.0 0 11,552

Index Only Scan using user_service_map_pkey on user_service_map m (cost=0.56..8.08 rows=10 width=4) (actual time=0.003..0.003 rows=0 loops=11,552)

  • Index Cond: ((fsa_id = fa.fsa_id) AND (service_id = a_services_6.id))
  • Heap Fetches: 205
19. 0.387 21.935 ↑ 3,893.0 1 205

Hash Semi Join (cost=3,014.97..6,360.87 rows=3,893 width=4) (actual time=0.107..0.107 rows=1 loops=205)

  • Hash Cond: (map.provider_group_id = core_groups.id)
20. 2.463 21.525 ↑ 3,893.0 1 205

Hash Join (cost=3,012.82..6,305.20 rows=3,893 width=8) (actual time=0.105..0.105 rows=1 loops=205)

  • Hash Cond: (map.provider_id = u.id)
21. 7.882 8.610 ↑ 177.4 41 205

Index Scan using user_service_map_service_id_provider_id_idx on user_service_map map (cost=1,699.37..4,861.92 rows=7,272 width=12) (actual time=0.017..0.042 rows=41 loops=205)

  • Index Cond: (service_id = a_services_6.id)
  • Filter: (hashed SubPlan 2)
22.          

SubPlan (for Index Scan)

23. 0.213 0.728 ↑ 34.2 511 1

Nested Loop (cost=0.71..1,655.24 rows=17,481 width=4) (actual time=0.021..0.728 rows=511 loops=1)

24. 0.008 0.008 ↑ 36.0 1 1

Index Scan using store_assoc_assoc_id_idx on store_assoc a_1 (cost=0.42..143.47 rows=36 width=4) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (assoc_id = 53,858)
25. 0.507 0.507 ↓ 1.7 511 1

Index Scan using fsa_store_assoc_store_id_idx on fsa_store_assoc fa_1 (cost=0.29..39.01 rows=298 width=8) (actual time=0.011..0.507 rows=511 loops=1)

  • Index Cond: (store_id = a_1.store_id)
26. 2.524 10.452 ↑ 1.0 8,628 1

Hash (cost=1,205.55..1,205.55 rows=8,632 width=4) (actual time=10.452..10.452 rows=8,628 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 304kB
27. 7.928 7.928 ↑ 1.0 8,628 1

Seq Scan on users u (cost=0.00..1,205.55 rows=8,632 width=4) (actual time=0.005..7.928 rows=8,628 loops=1)

  • Filter: ((active)::smallint = 1::smallint)
  • Rows Removed by Filter: 7,484
28. 0.004 0.023 ↑ 1.0 4 1

Hash (cost=2.09..2.09 rows=4 width=4) (actual time=0.023..0.023 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
29. 0.019 0.019 ↑ 1.0 4 1

Seq Scan on core_groups (cost=0.00..2.09 rows=4 width=4) (actual time=0.011..0.019 rows=4 loops=1)

  • Filter: ((name)::text = ANY ('{SP,SAT,PC,ST}'::text[]))
  • Rows Removed by Filter: 70
30. 0.612 0.612 ↓ 0.0 0 204

Index Scan using service_types_pkey on service_types a_service_types_7 (cost=0.14..0.27 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=204)

  • Index Cond: (id = a_services_6.service_type_id)
  • Filter: ((name)::text <> ALL ('{RONA_FNI_MEASURE,RONA_FNI_INSTALL,MEASURE,INSTALL}'::text[]))
  • Rows Removed by Filter: 1
Planning time : 2.435 ms
Execution time : 4,881.041 ms