explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HvJ1

Settings
# exclusive inclusive rows x rows loops node
1. 0.213 842.852 ↑ 3.2 232 1

Hash Semi Join (cost=26,574.48..183,916.47 rows=733 width=4) (actual time=836.865..842.852 rows=232 loops=1)

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

CTE matching_price_types

3. 0.021 3.849 ↓ 3.0 3 1

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

  • Buffers: shared hit=299
4. 0.020 0.020 ↓ 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.008..0.020 rows=8 loops=1)

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

Bitmap Heap Scan on accounts_price_types apt (cost=77.11..81.12 rows=1 width=4) (actual time=0.476..0.476 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.308 3.776 ↓ 0.0 0 8

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

  • Buffers: shared hit=287
7. 1.592 1.592 ↓ 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.199..0.199 rows=3,500 loops=8)

  • Index Cond: (price_type_id = pt.id)
  • Buffers: shared hit=140
8. 1.876 1.876 ↓ 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.268..0.268 rows=4,259 loops=7)

  • Index Cond: (account_id = 6941)
  • Buffers: shared hit=147
9. 6.607 9.940 ↑ 21.4 232 1

Bitmap Heap Scan on collections collection (cost=12,869.58..169,995.23 rows=4,966 width=4) (actual time=4.095..9.940 rows=232 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: 13616
  • Heap Blocks: exact=3163
  • Buffers: shared hit=6445
10. 2.289 2.289 ↓ 1.0 13,848 1

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

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

SubPlan (forBitmap 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 = 6941))
  • Heap Fetches: 0
13. 0.956 1.044 ↓ 1.2 874 1

Bitmap Heap Scan on accounts_collections ac_1 (cost=21.98..2,135.35 rows=717 width=4) (actual time=0.159..1.044 rows=874 loops=1)

  • Recheck Cond: (account_id = 6941)
  • Heap Blocks: exact=732
  • Buffers: shared hit=739
14. 0.088 0.088 ↓ 1.2 881 1

Bitmap Index Scan on accounts_collections_account_id_idx (cost=0.00..21.80 rows=717 width=0) (actual time=0.088..0.088 rows=881 loops=1)

  • Index Cond: (account_id = 6941)
  • Buffers: shared hit=7
15. 174.796 832.699 ↓ 8.7 508,305 1

Hash (cost=12,801.10..12,801.10 rows=58,417 width=4) (actual time=832.698..832.699 rows=508,305 loops=1)

  • Buckets: 524288 (originally 65536) Batches: 1 (originally 1) Memory Usage: 21967kB
  • Buffers: shared hit=513640
16. 239.743 657.903 ↓ 8.7 508,305 1

Nested Loop (cost=1.15..12,801.10 rows=58,417 width=4) (actual time=3.884..657.903 rows=508,305 loops=1)

  • Buffers: shared hit=513640
17. 5.982 21.072 ↓ 3.3 12,409 1

Nested Loop (cost=0.58..8,919.90 rows=3,806 width=4) (actual time=3.875..21.072 rows=12,409 loops=1)

  • Buffers: shared hit=9219
18. 0.008 3.863 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=3.861..3.863 rows=1 loops=1)

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

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

  • Buffers: shared hit=299
20. 11.227 11.227 ↓ 3.3 12,409 1

Index Scan using style_prices_price_type_id_idx on style_prices sp (cost=0.56..8,881.81 rows=3,806 width=8) (actual time=0.012..11.227 rows=12,409 loops=1)

  • Index Cond: (price_type_id = matching_price_types.price_type_id)
  • Buffers: shared hit=8920
21. 397.088 397.088 ↓ 2.7 41 12,409

Index Scan using collections_styles_style_id_idx on collections_styles cs (cost=0.56..0.87 rows=15 width=8) (actual time=0.004..0.032 rows=41 loops=12,409)

  • Index Cond: (style_id = sp.style_id)
  • Buffers: shared hit=504421
Planning time : 1.075 ms
Execution time : 843.960 ms