explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MfxO

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 50.729 ↓ 1.4 52 1

Sort (cost=71,725.77..71,725.86 rows=38 width=245) (actual time=50.726..50.729 rows=52 loops=1)

  • Sort Key: sc.style_id, sc.order_nbr
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=36,177
2. 0.019 50.711 ↓ 1.4 52 1

Group (cost=71,724.2..71,724.77 rows=38 width=245) (actual time=50.692..50.711 rows=52 loops=1)

  • Group Key: sc.id, us.filepath, us.url_prefix, ui.filepath, ui.url_prefix
  • Buffers: shared hit=36,177
3. 0.028 50.692 ↓ 1.4 52 1

Sort (cost=71,724.2..71,724.29 rows=38 width=245) (actual time=50.688..50.692 rows=52 loops=1)

  • Sort Key: sc.id, us.filepath, us.url_prefix, ui.filepath, ui.url_prefix
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=36,177
4. 0.023 50.664 ↓ 1.4 52 1

Nested Loop (cost=70,409.06..71,723.2 rows=38 width=245) (actual time=48.193..50.664 rows=52 loops=1)

  • Buffers: shared hit=36,177
5. 0.022 50.641 ↓ 1.4 52 1

Nested Loop (cost=70,408.49..71,454.42 rows=38 width=138) (actual time=48.19..50.641 rows=52 loops=1)

  • Buffers: shared hit=36,177
6. 0.023 50.619 ↓ 1.4 52 1

Nested Loop (cost=70,407.92..71,185.64 rows=38 width=31) (actual time=48.186..50.619 rows=52 loops=1)

  • Buffers: shared hit=36,177
7. 0.007 50.492 ↓ 5.2 26 1

Nested Loop (cost=70,386.31..70,987.07 rows=5 width=12) (actual time=48.152..50.492 rows=26 loops=1)

  • Buffers: shared hit=36,070
8. 0.728 50.407 ↓ 4.3 26 1

Hash Join (cost=70,385.88..70,961.1 rows=6 width=8) (actual time=48.135..50.407 rows=26 loops=1)

  • Buffers: shared hit=35,965
9. 4.382 49.495 ↑ 3.0 8,402 1

HashAggregate (cost=70,363.26..70,617.5 rows=25,424 width=8) (actual time=47.886..49.495 rows=8,402 loops=1)

  • Group Key: s_1.id, ud.user_id
  • Buffers: shared hit=35,953
10. 1.161 45.113 ↑ 3.0 8,411 1

Append (cost=0.86..70,236.14 rows=25,424 width=8) (actual time=0.035..45.113 rows=8,411 loops=1)

  • Buffers: shared hit=35,953
11. 7.705 13.897 ↓ 4.6 2,378 1

Nested Loop (cost=0.86..634.48 rows=513 width=8) (actual time=0.035..13.897 rows=2,378 loops=1)

  • Buffers: shared hit=10,307
12. 0.262 1.436 ↓ 4.6 2,378 1

Nested Loop (cost=0.43..353.22 rows=513 width=8) (actual time=0.024..1.436 rows=2,378 loops=1)

  • Buffers: shared hit=780
13. 0.036 0.036 ↑ 1.0 1 1

Seq Scan on users_divisions ud (cost=0..6.75 rows=1 width=8) (actual time=0.01..0.036 rows=1 loops=1)

  • Filter: (ud.user_id = 709,127)
  • Buffers: shared hit=3
14. 1.138 1.138 ↓ 2.9 2,378 1

Index Only Scan using divisions_styles_unique_1_idx on divisions_styles ds (cost=0.43..338.13 rows=834 width=8) (actual time=0.011..1.138 rows=2,378 loops=1)

  • Index Cond: (ds.division_id = ud.division_id)
  • Heap Fetches: 2,378
  • Buffers: shared hit=777
15. 4.756 4.756 ↑ 1.0 1 2,378

Index Only Scan using styles_id_incl_designer_id_idx on styles s_1 (cost=0.43..0.55 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2,378)

  • Index Cond: (s_1.id = ds.style_id)
  • Heap Fetches: 2,378
  • Buffers: shared hit=9,527
16. 4.584 30.055 ↑ 4.1 6,033 1

Nested Loop (cost=379.61..69,220.29 rows=24,911 width=8) (actual time=0.682..30.055 rows=6,033 loops=1)

  • Buffers: shared hit=25,646
17. 0.691 7.372 ↑ 4.1 6,033 1

Nested Loop (cost=379.17..57,072.82 rows=24,911 width=8) (actual time=0.667..7.372 rows=6,033 loops=1)

  • Buffers: shared hit=4,243
18. 0.013 0.013 ↑ 1.0 2 1

Index Only Scan using unique_user_category on users_categories uc (cost=0.28..11.74 rows=2 width=8) (actual time=0.008..0.013 rows=2 loops=1)

  • Index Cond: (uc.user_id = 709,127)
  • Heap Fetches: 2
  • Buffers: shared hit=4
19. 5.884 6.668 ↑ 5.1 3,016 2

Bitmap Heap Scan on categories_styles cs_1 (cost=378.9..28,377.68 rows=15,286 width=8) (actual time=0.624..3.334 rows=3,016 loops=2)

  • Heap Blocks: exact=4,206
  • Buffers: shared hit=4,239
20. 0.784 0.784 ↑ 5.0 3,086 2

Bitmap Index Scan on categories_styles_category_id_idx (cost=0..375.08 rows=15,286 width=0) (actual time=0.392..0.392 rows=3,086 loops=2)

  • Index Cond: (cs_1.category_id = uc.category_id)
  • Buffers: shared hit=33
21. 18.099 18.099 ↑ 1.0 1 6,033

Index Only Scan using styles_id_incl_designer_id_idx on styles s_2 (cost=0.43..0.49 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=6,033)

  • Index Cond: (s_2.id = cs_1.style_id)
  • Heap Fetches: 1,269
  • Buffers: shared hit=21,403
22. 0.060 0.184 ↓ 2.7 555 1

Hash (cost=20.06..20.06 rows=205 width=4) (actual time=0.184..0.184 rows=555 loops=1)

  • Buffers: shared hit=12
23. 0.124 0.124 ↓ 2.7 555 1

Index Only Scan using unique_cs on collections_styles cs (cost=0.43..20.06 rows=205 width=4) (actual time=0.02..0.124 rows=555 loops=1)

  • Index Cond: (cs.collection_id = 505,016)
  • Heap Fetches: 555
  • Buffers: shared hit=12
24. 0.078 0.078 ↑ 1.0 1 26

Index Only Scan using style_active_idx on styles s (cost=0.43..4.32 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=26)

  • Index Cond: (s.id = s_1.id)
  • Heap Fetches: 26
  • Buffers: shared hit=105
25. 0.098 0.104 ↑ 4.5 2 26

Index Scan using style_colors_style_id_index on style_colors sc (cost=21.61..39.6 rows=9 width=31) (actual time=0.004..0.004 rows=2 loops=26)

  • Index Cond: (sc.style_id = s.id)
  • Filter: ((NOT (hashed SubPlan 1)) AND (sc.deleted = 0))
  • Buffers: shared hit=107
26.          

SubPlan (for Index Scan)

27. 0.006 0.006 ↓ 0.0 0 1

Index Only Scan using coll_style_color_excl_coll_style_color_not_del on collection_style_color_exclusions csce (cost=0.43..20.08 rows=437 width=4) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (csce.collection_id = 505,016)
  • Buffers: shared hit=3
28. 0.000 0.000 ↓ 0.0 0 52

Index Scan using uploads_pkey on uploads us (cost=0.57..7.07 rows=1 width=115) (actual time=0..0 rows=0 loops=52)

  • Index Cond: (sc.swatch_id = us.id)
29. 0.000 0.000 ↓ 0.0 0 52

Index Scan using uploads_pkey on uploads ui (cost=0.57..7.07 rows=1 width=115) (actual time=0..0 rows=0 loops=52)

  • Index Cond: (sc.image_id = ui.id)
Planning time : 2.468 ms
Execution time : 52.269 ms