explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vDdg : warehouses with users styles view

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 54.105 ↓ 5.0 5 1

Nested Loop (cost=73,315.5..73,737.74 rows=1 width=44) (actual time=52.445..54.105 rows=5 loops=1)

  • Buffers: shared hit=36,299
2. 0.005 54.054 ↓ 4.0 4 1

Nested Loop (cost=73,314.94..73,729.18 rows=1 width=12) (actual time=51.949..54.054 rows=4 loops=1)

  • Buffers: shared hit=36,278
3. 0.015 54.009 ↓ 5.0 5 1

Nested Loop (cost=73,314.51..73,726.64 rows=1 width=12) (actual time=47.419..54.009 rows=5 loops=1)

  • Buffers: shared hit=36,258
4. 2.258 53.954 ↓ 5.0 5 1

Merge Join (cost=73,314.23..73,726.28 rows=1 width=20) (actual time=47.395..53.954 rows=5 loops=1)

  • Buffers: shared hit=36,245
5. 0.009 32.453 ↓ 5.0 5 1

Sort (cost=70,964.8..70,964.8 rows=1 width=12) (actual time=32.451..32.453 rows=5 loops=1)

  • Sort Key: s.designer_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=35,975
6. 0.573 32.444 ↓ 5.0 5 1

Hash Join (cost=70,389.57..70,964.79 rows=1 width=12) (actual time=30.351..32.444 rows=5 loops=1)

  • Buffers: shared hit=35,975
7. 5.039 31.830 ↑ 3.0 8,402 1

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

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

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

  • Buffers: shared hit=35,953
9. 0.000 6.126 ↓ 4.6 2,378 1

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

  • Buffers: shared hit=10,307
10. 0.273 1.376 ↓ 4.6 2,378 1

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

  • Buffers: shared hit=780
11. 0.039 0.039 ↑ 1.0 1 1

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

  • Filter: (ud.user_id = 709,127)
  • Buffers: shared hit=3
12. 1.064 1.064 ↓ 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.01..1.064 rows=2,378 loops=1)

  • Index Cond: (ds.division_id = ud.division_id)
  • Heap Fetches: 2,378
  • Buffers: shared hit=777
13. 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
14. 0.000 19.701 ↑ 4.1 6,033 1

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

  • Buffers: shared hit=25,646
15. 0.647 7.686 ↑ 4.1 6,033 1

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

  • Buffers: shared hit=4,243
16. 0.011 0.011 ↑ 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.007..0.011 rows=2 loops=1)

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

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

  • Heap Blocks: exact=4,206
  • Buffers: shared hit=4,239
18. 0.754 0.754 ↑ 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.377..0.377 rows=3,086 loops=2)

  • Index Cond: (cs.category_id = uc.category_id)
  • Buffers: shared hit=33
19. 12.066 12.066 ↑ 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.002..0.002 rows=1 loops=6,033)

  • Index Cond: (s_2.id = cs.style_id)
  • Heap Fetches: 1,269
  • Buffers: shared hit=21,403
20. 0.004 0.041 ↑ 1.0 5 1

Hash (cost=26.25..26.25 rows=5 width=8) (actual time=0.04..0.041 rows=5 loops=1)

  • Buffers: shared hit=22
21. 0.037 0.037 ↑ 1.0 5 1

Index Only Scan using styles_id_incl_designer_id_idx on styles s (cost=0.43..26.25 rows=5 width=8) (actual time=0.015..0.037 rows=5 loops=1)

  • Index Cond: (s.id = ANY ('{5583619,8799200,7614893,2519420,8774652}'::integer[]))
  • Heap Fetches: 3
  • Buffers: shared hit=22
22. 3.809 19.243 ↓ 1.0 24,138 1

Unique (cost=2,349.43..2,473.96 rows=23,001 width=105) (actual time=13.536..19.243 rows=24,138 loops=1)

  • Buffers: shared hit=270
23. 10.924 15.434 ↓ 1.0 25,255 1

Sort (cost=2,349.43..2,411.69 rows=24,905 width=105) (actual time=13.533..15.434 rows=25,255 loops=1)

  • Sort Key: dw.designer_id, dw.order_nbr, dw.id
  • Sort Method: quicksort Memory: 1,952kB
  • Buffers: shared hit=270
24. 4.510 4.510 ↓ 1.0 25,255 1

Seq Scan on designer_warehouses dw (cost=0..530.85 rows=24,905 width=105) (actual time=0.014..4.51 rows=25,255 loops=1)

  • Filter: (NOT dw.deleted)
  • Buffers: shared hit=270
25. 0.040 0.040 ↑ 1.0 1 5

Index Only Scan using designers_pkey on designers d (cost=0.29..0.35 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=5)

  • Index Cond: (d.id = dw.designer_id)
  • Heap Fetches: 2
  • Buffers: shared hit=13
26. 0.040 0.040 ↑ 1.0 1 5

Index Scan using designer_warehouse_id_idx on wip_inventory wi (cost=0.42..2.53 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=5)

  • Index Cond: (wi.designer_warehouse_id = dw.id)
  • Filter: ((NOT wi.deleted) AND (wi.inventory_date IS NULL))
  • Buffers: shared hit=20
27. 0.032 0.032 ↑ 1.0 1 4

Index Scan using wip_inventory_items_inventory_style_color_size on wip_inventory_items wii (cost=0.56..8.55 rows=1 width=19) (actual time=0.007..0.008 rows=1 loops=4)

  • Index Cond: ((wii.wip_inventory_id = wi.id) AND (wii.style_id = s_1.id))
  • Buffers: shared hit=21
Planning time : 2.733 ms
Execution time : 54.35 ms