explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CjB1

Settings
# exclusive inclusive rows x rows loops node
1. 1.289 142,756.806 ↓ 227.0 227 1

Nested Loop (cost=1,487.05..4,974.61 rows=1 width=20) (actual time=203.203..142,756.806 rows=227 loops=1)

2. 1.724 142,749.035 ↓ 463.0 463 1

Nested Loop Semi Join (cost=1,486.90..4,974.22 rows=1 width=24) (actual time=199.200..142,749.035 rows=463 loops=1)

3. 0.744 35,076.423 ↓ 468.0 468 1

Nested Loop (cost=32.94..742.58 rows=1 width=28) (actual time=91.204..35,076.423 rows=468 loops=1)

4. 0.352 7.867 ↓ 26.0 26 1

Index Scan using service_categories_name_key on service_categories a_service_categories_1 (cost=0.14..693.88 rows=1 width=20) (actual time=3.505..7.867 rows=26 loops=1)

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

SubPlan (for Index Scan)

6. 0.167 7.515 ↓ 0.0 0 167

Limit (cost=0.00..3.58 rows=1 width=4) (actual time=0.045..0.045 rows=0 loops=167)

7. 7.348 7.348 ↓ 0.0 0 167

Seq Scan on service_categories_clients_assoc (cost=0.00..3.58 rows=1 width=4) (actual time=0.044..0.044 rows=0 loops=167)

  • Filter: ((client_id = ANY ('{1}'::integer[])) AND (category_id = a_service_categories_1.id))
  • Rows Removed by Filter: 167
8. 33.588 35,067.812 ↓ 18.0 18 26

Bitmap Heap Scan on services a_services_6 (cost=32.80..48.68 rows=1 width=16) (actual time=37.377..1,348.762 rows=18 loops=26)

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

BitmapAnd (cost=32.80..32.80 rows=3 width=0) (actual time=0.508..0.508 rows=0 loops=26)

10. 0.260 0.260 ↓ 1.7 20 26

Bitmap Index Scan on services_cat_id_idx (cost=0.00..4.37 rows=12 width=0) (actual time=0.010..0.010 rows=20 loops=26)

  • Index Cond: (cat_id = a_service_categories_1.id)
11. 12.850 12.850 ↑ 1.0 513 25

Bitmap Index Scan on services_service_client_id_key (cost=0.00..28.12 rows=513 width=0) (actual time=0.514..0.514 rows=513 loops=25)

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

SubPlan (for Bitmap Heap Scan)

13. 185.081 35,021.016 ↑ 185,212.0 1 474

Nested Loop (cost=0.56..244,379.96 rows=185,212 width=0) (actual time=73.884..73.884 rows=1 loops=474)

14. 5,569.884 21,550.884 ↑ 45.2 384 474

Nested Loop (cost=0.00..46,062.50 rows=17,338 width=4) (actual time=25.118..45.466 rows=384 loops=474)

  • Join Filter: (a.store_id = fa.store_id)
  • Rows Removed by Join Filter: 65393
15. 12,067.566 12,067.566 ↑ 18.0 2 474

Seq Scan on store_assoc a (cost=0.00..4,554.68 rows=36 width=4) (actual time=25.106..25.459 rows=2 loops=474)

  • Filter: (assoc_id = 53220)
  • Rows Removed by Filter: 122308
16. 3,893.962 3,913.434 ↑ 2.1 35,350 882

Materialize (cost=0.00..1,447.95 rows=74,530 width=8) (actual time=0.005..4.437 rows=35,350 loops=882)

17. 19.472 19.472 ↑ 1.0 74,530 1

Seq Scan on fsa_store_assoc fa (cost=0.00..1,075.30 rows=74,530 width=8) (actual time=3.787..19.472 rows=74,530 loops=1)

18. 13,285.051 13,285.051 ↓ 0.0 0 181,987

Index Only Scan using user_service_map_pkey on user_service_map m (cost=0.56..11.31 rows=13 width=4) (actual time=0.073..0.073 rows=0 loops=181,987)

  • Index Cond: ((fsa_id = fa.fsa_id) AND (service_id = a_services_6.id))
  • Heap Fetches: 468
19. 393.006 107,670.888 ↑ 270.0 1 468

Nested Loop (cost=1,453.96..4,231.62 rows=270 width=4) (actual time=230.066..230.066 rows=1 loops=468)

20. 188.352 105,768.936 ↓ 2.1 1,075 468

Hash Join (cost=1,453.67..4,071.51 rows=520 width=8) (actual time=8.035..226.002 rows=1,075 loops=468)

  • Hash Cond: (map.provider_group_id = core_groups.id)
21. 105,503.979 105,576.588 ↑ 9.0 1,075 468

Index Scan using user_service_map_service_id_provider_id_idx on user_service_map map (cost=1,451.51..4,042.62 rows=9,629 width=12) (actual time=8.021..225.591 rows=1,075 loops=468)

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

SubPlan (for Index Scan)

23. 28.603 72.609 ↓ 1.7 29,949 1

Hash Join (cost=130.82..1,407.73 rows=17,338 width=4) (actual time=8.663..72.609 rows=29,949 loops=1)

  • Hash Cond: (fa_1.store_id = a_1.store_id)
24. 35.378 35.378 ↑ 1.0 74,530 1

Seq Scan on fsa_store_assoc fa_1 (cost=0.00..1,075.30 rows=74,530 width=8) (actual time=0.013..35.378 rows=74,530 loops=1)

25. 0.029 8.628 ↓ 1.9 69 1

Hash (cost=130.37..130.37 rows=36 width=4) (actual time=8.628..8.628 rows=69 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
26. 0.041 8.599 ↓ 1.9 69 1

Bitmap Heap Scan on store_assoc a_1 (cost=4.70..130.37 rows=36 width=4) (actual time=8.566..8.599 rows=69 loops=1)

  • Recheck Cond: (assoc_id = 53220)
  • Heap Blocks: exact=2
27. 8.558 8.558 ↓ 1.9 69 1

Bitmap Index Scan on store_assoc_assoc_id_idx (cost=0.00..4.69 rows=36 width=0) (actual time=8.557..8.558 rows=69 loops=1)

  • Index Cond: (assoc_id = 53220)
28. 0.008 3.996 ↑ 1.0 4 1

Hash (cost=2.11..2.11 rows=4 width=4) (actual time=3.996..3.996 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 3.988 3.988 ↑ 1.0 4 1

Seq Scan on core_groups (cost=0.00..2.11 rows=4 width=4) (actual time=3.973..3.988 rows=4 loops=1)

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

Index Scan using users_pkey on users u (cost=0.29..0.31 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=502,982)

  • Index Cond: (id = map.provider_id)
  • Filter: ((active)::smallint = '1'::smallint)
  • Rows Removed by Filter: 1
31. 6.482 6.482 ↓ 0.0 0 463

Index Scan using service_types_pkey on service_types a_service_types_7 (cost=0.15..0.39 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=463)

  • 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 : 468.073 ms
Execution time : 142,758.259 ms