explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 13PS

Settings
# exclusive inclusive rows x rows loops node
1. 0.133 87.050 ↓ 0.0 0 1

Hash Semi Join (cost=26,762.92..184,130.29 rows=738 width=4) (actual time=87.050..87.050 rows=0 loops=1)

  • Hash Cond: (collection.id = cs.collection_id)
  • Buffers: shared hit=2650 read=131
2.          

CTE matching_price_types

3. 0.027 83.933 ↓ 0.0 0 1

Nested Loop (cost=22.82..64.42 rows=1 width=4) (actual time=83.933..83.933 rows=0 loops=1)

  • Buffers: shared hit=41 read=131
4. 0.562 0.562 ↓ 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.036..0.562 rows=8 loops=1)

  • Index Cond: (designer_id = 135647)
  • Buffers: shared hit=6 read=2
5. 0.016 83.344 ↓ 0.0 0 8

Bitmap Heap Scan on accounts_price_types apt (cost=22.53..26.54 rows=1 width=4) (actual time=10.418..10.418 rows=0 loops=8)

  • Recheck Cond: ((account_id = 282844) AND (price_type_id = pt.id))
  • Buffers: shared hit=35 read=129
6. 0.032 83.328 ↓ 0.0 0 8

BitmapAnd (cost=22.53..22.53 rows=1 width=0) (actual time=10.416..10.416 rows=0 loops=8)

  • Buffers: shared hit=35 read=129
7. 2.376 2.376 ↑ 8.7 7 8

Bitmap Index Scan on accounts_price_types_account_id_idx (cost=0.00..4.89 rows=61 width=0) (actual time=0.297..0.297 rows=7 loops=8)

  • Index Cond: (account_id = 282844)
  • Buffers: shared hit=21 read=3
8. 80.920 80.920 ↓ 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=10.115..10.115 rows=3,500 loops=8)

  • Index Cond: (price_type_id = pt.id)
  • Buffers: shared hit=14 read=126
9. 0.427 2.976 ↑ 4,966.0 1 1

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

  • Recheck Cond: (designer_id = 135647)
  • Filter: ((NOT is_custom) AND ((visible_to_all_matches = 1) OR (alternatives: SubPlan 2 or hashed SubPlan 3)))
  • Rows Removed by Filter: 69
  • Heap Blocks: exact=63
  • Buffers: shared hit=2609
10. 2.541 2.541 ↓ 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.541..2.541 rows=13,849 loops=1)

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

SubPlan (for Bitmap Heap Scan)

12. 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
13. 0.008 0.008 ↓ 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.008 rows=0 loops=1)

  • Index Cond: (account_id = 282844)
  • Buffers: shared hit=3
14. 0.001 83.941 ↓ 0.0 0 1

Hash (cost=13,007.10..13,007.10 rows=65,746 width=4) (actual time=83.941..83.941 rows=0 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 1024kB
  • Buffers: shared hit=41 read=131
15. 0.001 83.940 ↓ 0.0 0 1

Nested Loop (cost=1.15..13,007.10 rows=65,746 width=4) (actual time=83.940..83.940 rows=0 loops=1)

  • Buffers: shared hit=41 read=131
16. 0.001 83.939 ↓ 0.0 0 1

Nested Loop (cost=0.58..8,917.59 rows=3,805 width=4) (actual time=83.939..83.939 rows=0 loops=1)

  • Buffers: shared hit=41 read=131
17. 0.002 83.938 ↓ 0.0 0 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=83.938..83.938 rows=0 loops=1)

  • Group Key: matching_price_types.price_type_id
  • Buffers: shared hit=41 read=131
18. 83.936 83.936 ↓ 0.0 0 1

CTE Scan on matching_price_types (cost=0.00..0.02 rows=1 width=4) (actual time=83.935..83.936 rows=0 loops=1)

  • Buffers: shared hit=41 read=131
19. 0.000 0.000 ↓ 0.0 0

Index Scan using style_prices_price_type_id_idx on style_prices sp (cost=0.56..8,879.51 rows=3,805 width=8) (never executed)

  • Index Cond: (price_type_id = matching_price_types.price_type_id)
20. 0.000 0.000 ↓ 0.0 0

Index Scan using collections_styles_style_id_idx on collections_styles cs (cost=0.56..0.90 rows=17 width=8) (never executed)

  • Index Cond: (style_id = sp.style_id)
Planning time : 1.810 ms
Execution time : 87.155 ms