explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9cbv

Settings
# exclusive inclusive rows x rows loops node
1. 0.055 1,426.430 ↑ 1.0 100 1

Limit (cost=11,918.68..11,921.18 rows=100 width=68) (actual time=1,422.628..1,426.430 rows=100 loops=1)

  • Buffers: shared hit=1628004
2.          

CTE matching_price_types

3. 0.018 4.192 ↓ 3.0 3 1

Nested Loop (cost=77.40..173.58 rows=1 width=4) (actual time=3.417..4.192 rows=3 loops=1)

  • Buffers: shared hit=299
4. 0.022 0.022 ↓ 4.0 8 1

Index Scan using price_types_designer_id_idx on price_types pt (cost=0.29..11.31 rows=2 width=4) (actual time=0.011..0.022 rows=8 loops=1)

  • Index Cond: (designer_id = 135647)
  • Buffers: shared hit=8
5. 0.032 4.152 ↓ 0.0 0 8

Bitmap Heap Scan on accounts_price_types apt (cost=77.11..81.12 rows=1 width=4) (actual time=0.518..0.519 rows=0 loops=8)

  • Recheck Cond: ((price_type_id = pt.id) AND (account_id = 6941))
  • Heap Blocks: exact=4
  • Buffers: shared hit=291
6. 0.395 4.120 ↓ 0.0 0 8

BitmapAnd (cost=77.11..77.11 rows=1 width=0) (actual time=0.515..0.515 rows=0 loops=8)

  • Buffers: shared hit=287
7. 1.688 1.688 ↓ 5.3 3,500 8

Bitmap Index Scan on accounts_price_types_price_type_id_idx (cost=0.00..17.37 rows=659 width=0) (actual time=0.211..0.211 rows=3,500 loops=8)

  • Index Cond: (price_type_id = pt.id)
  • Buffers: shared hit=140
8. 2.037 2.037 ↓ 1.7 4,259 7

Bitmap Index Scan on accounts_price_types_account_id_idx (cost=0.00..58.87 rows=2,459 width=0) (actual time=0.291..0.291 rows=4,259 loops=7)

  • Index Cond: (account_id = 6941)
  • Buffers: shared hit=147
9. 2.592 1,426.375 ↑ 6.4 100 1

GroupAggregate (cost=11,745.09..11,761.19 rows=644 width=68) (actual time=1,422.626..1,426.375 rows=100 loops=1)

  • Group Key: style.id
  • Buffers: shared hit=1628004
10. 45.565 1,423.783 ↓ 6.7 4,305 1

Sort (cost=11,745.09..11,746.70 rows=644 width=17) (actual time=1,422.594..1,423.783 rows=4,305 loops=1)

  • Sort Key: style.id
  • Sort Method: quicksort Memory: 8127kB
  • Buffers: shared hit=1628004
11. 62.438 1,378.218 ↓ 145.3 93,551 1

Nested Loop (cost=3.00..11,715.05 rows=644 width=17) (actual time=4.557..1,378.218 rows=93,551 loops=1)

  • Join Filter: (style.id = color.style_id)
  • Buffers: shared hit=1628004
12. 20.267 1,211.052 ↓ 5,236.4 26,182 1

Nested Loop (cost=2.57..11,710.50 rows=5 width=19) (actual time=4.544..1,211.052 rows=26,182 loops=1)

  • Join Filter: (style.id = size.style_id)
  • Buffers: shared hit=1472761
13. 8.118 1,149.009 ↓ 5,222.0 5,222 1

Nested Loop Semi Join (cost=2.00..11,707.21 rows=1 width=12) (actual time=4.535..1,149.009 rows=5,222 loops=1)

  • Join Filter: (style.id = cs.style_id)
  • Buffers: shared hit=1431782
14. 14.024 73.717 ↓ 1,772.7 12,409 1

Nested Loop (cost=1.02..10,798.66 rows=7 width=8) (actual time=4.247..73.717 rows=12,409 loops=1)

  • Buffers: shared hit=58864
15. 6.245 22.466 ↓ 3.3 12,409 1

Nested Loop (cost=0.58..8,922.21 rows=3,807 width=4) (actual time=4.230..22.466 rows=12,409 loops=1)

  • Buffers: shared hit=9219
16. 0.020 4.218 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=4.217..4.218 rows=1 loops=1)

  • Group Key: matching_price_types.price_type_id
  • Buffers: shared hit=299
17. 4.198 4.198 ↓ 3.0 3 1

CTE Scan on matching_price_types (cost=0.00..0.02 rows=1 width=4) (actual time=3.420..4.198 rows=3 loops=1)

  • Buffers: shared hit=299
18. 12.003 12.003 ↓ 3.3 12,409 1

Index Scan using style_prices_price_type_id_idx on style_prices sp (cost=0.56..8,884.11 rows=3,807 width=8) (actual time=0.011..12.003 rows=12,409 loops=1)

  • Index Cond: (price_type_id = matching_price_types.price_type_id)
  • Buffers: shared hit=8920
19. 37.227 37.227 ↑ 1.0 1 12,409

Index Scan using styles_pkey on styles style (cost=0.43..0.49 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=12,409)

  • Index Cond: (id = sp.style_id)
  • Filter: (designer_id = 135647)
  • Buffers: shared hit=49645
20. 46.241 1,067.174 ↓ 0.0 0 12,409

Nested Loop (cost=0.99..144.06 rows=6 width=4) (actual time=0.086..0.086 rows=0 loops=12,409)

  • Buffers: shared hit=1372918
21. 260.589 260.589 ↓ 1.2 20 12,409

Index Scan using collections_styles_style_id_idx on collections_styles cs (cost=0.56..0.89 rows=16 width=8) (actual time=0.004..0.021 rows=20 loops=12,409)

  • Index Cond: (style_id = sp.style_id)
  • Buffers: shared hit=290510
22. 720.716 760.344 ↓ 0.0 0 253,448

Index Scan using collections_pkey on collections c (cost=0.42..8.95 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=253,448)

  • Index Cond: (id = cs.collection_id)
  • Filter: ((NOT is_custom) AND ((visible_to_all_matches = 1) OR (alternatives: SubPlan 2 or hashed SubPlan 3)))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1082408
23.          

SubPlan (forIndex Scan)

24. 39.628 39.628 ↓ 0.0 0 19,814

Index Only Scan using accounts_collections_pkey on accounts_collections ac (cost=0.43..8.45 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=19,814)

  • Index Cond: ((collection_id = c.id) AND (account_id = 6941))
  • Heap Fetches: 71
  • Buffers: shared hit=59733
25. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on accounts_collections ac_1 (cost=21.98..2,135.35 rows=717 width=4) (never executed)

  • Recheck Cond: (account_id = 6941)
26. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on accounts_collections_account_id_idx (cost=0.00..21.80 rows=717 width=0) (never executed)

  • Index Cond: (account_id = 6941)
27. 41.776 41.776 ↑ 17.4 5 5,222

Index Scan using style_sizes_style_id_idx on style_sizes size (cost=0.56..2.20 rows=87 width=7) (actual time=0.004..0.008 rows=5 loops=5,222)

  • Index Cond: (style_id = sp.style_id)
  • Buffers: shared hit=40979
28. 104.728 104.728 ↑ 3.8 4 26,182

Index Scan using style_colors_style_id_index on style_colors color (cost=0.43..0.72 rows=15 width=14) (actual time=0.002..0.004 rows=4 loops=26,182)

  • Index Cond: (style_id = size.style_id)
  • Buffers: shared hit=155243
Planning time : 7.044 ms
Execution time : 1,427.248 ms