explain.depesz.com

PostgreSQL's explain analyze made readable

Result: msPu

Settings
# exclusive inclusive rows x rows loops node
1. 0.329 164.446 ↓ 0.0 0 1

Hash Join (cost=538,225.46..695,372.35 rows=738 width=4) (actual time=164.446..164.446 rows=0 loops=1)

  • Hash Cond: (collection.id = cs.collection_id)
  • Buffers: shared hit=67414
2. 6.306 8.882 ↑ 21.6 230 1

Bitmap Heap Scan on collections collection (cost=12,869.58..169,995.23 rows=4,966 width=4) (actual time=3.003..8.882 rows=230 loops=1)

  • Recheck Cond: (designer_id = 135647)
  • Filter: ((NOT is_custom) AND ((visible_to_all_matches = 1) OR (alternatives: SubPlan 1 or hashed SubPlan 2)))
  • Rows Removed by Filter: 13618
  • Heap Blocks: exact=3163
  • Buffers: shared hit=5709
3. 2.567 2.567 ↓ 1.0 13,849 1

Bitmap Index Scan on collections_designer_id_idx (cost=0.00..12,868.34 rows=13,839 width=0) (actual time=2.566..2.567 rows=13,849 loops=1)

  • Index Cond: (designer_id = 135647)
  • Buffers: shared hit=2543
4.          

SubPlan (forBitmap Heap Scan)

5. 0.000 0.000 ↓ 0.0 0

Index Only Scan using accounts_collections_pkey on accounts_collections ac (cost=0.43..8.45 rows=1 width=0) (never executed)

  • Index Cond: ((collection_id = collection.id) AND (account_id = 282844))
  • Heap Fetches: 0
6. 0.009 0.009 ↓ 0.0 0 1

Index Scan using accounts_collections_account_id_idx on accounts_collections ac_1 (cost=0.43..215.87 rows=58 width=4) (actual time=0.008..0.009 rows=0 loops=1)

  • Index Cond: (account_id = 282844)
  • Buffers: shared hit=3
7. 0.044 155.235 ↑ 848.4 68 1

Hash (cost=524,634.72..524,634.72 rows=57,693 width=4) (actual time=155.235..155.235 rows=68 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 515kB
  • Buffers: shared hit=61705
8. 3.223 155.191 ↑ 848.4 68 1

HashAggregate (cost=524,057.79..524,634.72 rows=57,693 width=4) (actual time=154.962..155.191 rows=68 loops=1)

  • Group Key: cs.collection_id
  • Buffers: shared hit=61705
9. 11.058 151.968 ↑ 766.5 5,882 1

Nested Loop (cost=1,001.12..512,787.04 rows=4,508,297 width=4) (actual time=62.514..151.968 rows=5,882 loops=1)

  • Buffers: shared hit=61705
10. 0.000 122.901 ↑ 43.5 6,003 1

Gather (cost=1,000.56..232,370.93 rows=260,907 width=4) (actual time=62.498..122.901 rows=6,003 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=31332
11. 2.879 126.375 ↑ 54.3 2,001 3

Nested Loop (cost=0.56..205,280.23 rows=108,711 width=4) (actual time=67.800..126.375 rows=2,001 loops=3)

  • Buffers: shared hit=31332
12. 122.577 122.577 ↑ 12.5 2 3

Parallel Seq Scan on accounts_price_types apt (cost=0.00..41,709.84 rows=25 width=4) (actual time=67.766..122.577 rows=2 loops=3)

  • Filter: (account_id = 282844)
  • Rows Removed by Filter: 1023980
  • Buffers: shared hit=25698
13. 0.919 0.919 ↑ 4.4 858 7

Index Scan using style_prices_price_type_id_idx on style_prices sp (cost=0.56..6,504.77 rows=3,805 width=8) (actual time=0.021..0.919 rows=858 loops=7)

  • Index Cond: (price_type_id = apt.price_type_id)
  • Buffers: shared hit=5634
14. 18.009 18.009 ↑ 17.0 1 6,003

Index Scan using collections_styles_style_id_idx on collections_styles cs (cost=0.56..0.90 rows=17 width=8) (actual time=0.003..0.003 rows=1 loops=6,003)

  • Index Cond: (style_id = sp.style_id)
  • Buffers: shared hit=30373
Planning time : 0.967 ms
Execution time : 164.786 ms