explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Vgr8

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 364.266 ↑ 1.0 100 1

Limit (cost=97,292.67..97,338.59 rows=100 width=4) (actual time=363.415..364.266 rows=100 loops=1)

2.          

CTE visible_price_types

3. 0.577 0.577 ↑ 1.0 2 1

Seq Scan on retailer_price_types (cost=0.00..170.00 rows=2 width=4) (actual time=0.121..0.577 rows=2 loops=1)

  • Filter: (retailer_id = 2008)
  • Rows Removed by Filter: 9998
4. 0.079 364.248 ↑ 1,844.7 100 1

Nested Loop Semi Join (cost=97,122.67..181,824.37 rows=184,468 width=4) (actual time=363.414..364.248 rows=100 loops=1)

  • Join Filter: (price.price_type_id = visible_price_types.price_type_id)
  • Rows Removed by Join Filter: 248
5. 0.038 363.572 ↑ 1,853.9 199 1

Nested Loop (cost=97,122.67..165,222.23 rows=368,936 width=8) (actual time=363.289..363.572 rows=199 loops=1)

6. 0.041 363.384 ↑ 1,844.7 50 1

Hash Join (cost=97,122.25..103,058.36 rows=92,234 width=8) (actual time=363.276..363.384 rows=50 loops=1)

  • Hash Cond: (sku.product_id = product.id)
7. 0.422 359.974 ↑ 1,844.7 50 1

Hash Semi Join (cost=96,808.25..102,502.14 rows=92,234 width=12) (actual time=359.886..359.974 rows=50 loops=1)

  • Hash Cond: (sku.id = cs.sku_id)
8. 0.047 0.047 ↑ 319.5 313 1

Seq Scan on skus sku (cost=0.00..1,636.00 rows=100,000 width=8) (actual time=0.008..0.047 rows=313 loops=1)

9. 87.522 359.505 ↑ 1.1 454,207 1

Hash (cost=88,947.07..88,947.07 rows=479,134 width=4) (actual time=359.505..359.505 rows=454,207 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3014kB
10. 177.620 271.983 ↑ 1.1 454,207 1

Hash Join (cost=50,514.12..88,947.07 rows=479,134 width=4) (actual time=16.729..271.983 rows=454,207 loops=1)

  • Hash Cond: (cs.collection_id = collection.id)
11. 92.397 92.397 ↑ 1.0 999,520 1

Seq Scan on collection_skus cs (cost=0.00..35,808.20 rows=999,520 width=8) (actual time=14.751..92.397 rows=999,520 loops=1)

12. 0.695 1.966 ↑ 1.1 5,002 1

Hash (cost=50,448.21..50,448.21 rows=5,273 width=4) (actual time=1.966..1.966 rows=5,002 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 240kB
13. 0.890 1.271 ↑ 1.1 5,002 1

Bitmap Heap Scan on collections collection (cost=432.21..50,448.21 rows=5,273 width=4) (actual time=0.375..1.271 rows=5,002 loops=1)

  • Recheck Cond: ((visibility = 3) OR (visibility = 2))
  • Filter: ((visibility = 3) OR ((visibility = 2) AND (alternatives: SubPlan 2 or hashed SubPlan 3)))
  • Rows Removed by Filter: 998
  • Heap Blocks: exact=45
14. 0.001 0.365 ↓ 0.0 0 1

BitmapOr (cost=432.21..432.21 rows=6,000 width=0) (actual time=0.365..0.365 rows=0 loops=1)

15. 0.227 0.227 ↑ 1.0 5,000 1

Bitmap Index Scan on collections_brand_visibility_idx (cost=0.00..214.78 rows=5,000 width=0) (actual time=0.227..0.227 rows=5,000 loops=1)

  • Index Cond: (visibility = 3)
16. 0.137 0.137 ↑ 1.0 1,000 1

Bitmap Index Scan on collections_brand_visibility_idx (cost=0.00..214.78 rows=1,000 width=0) (actual time=0.137..0.137 rows=1,000 loops=1)

  • Index Cond: (visibility = 2)
17.          

SubPlan (for Bitmap Heap Scan)

18. 0.000 0.000 ↓ 0.0 0

Index Only Scan using collection_retailers_pkey on collection_retailers cr (cost=0.29..8.31 rows=1 width=0) (never executed)

  • Index Cond: ((collection_id = collection.id) AND (retailer_id = 2008))
  • Heap Fetches: 0
19. 0.004 0.016 ↑ 2.0 2 1

Bitmap Heap Scan on collection_retailers cr_1 (cost=4.32..17.82 rows=4 width=4) (actual time=0.015..0.016 rows=2 loops=1)

  • Recheck Cond: (retailer_id = 2008)
  • Heap Blocks: exact=2
20. 0.012 0.012 ↑ 2.0 2 1

Bitmap Index Scan on collections_retailers_retailer_idx (cost=0.00..4.32 rows=4 width=0) (actual time=0.012..0.012 rows=2 loops=1)

  • Index Cond: (retailer_id = 2008)
21. 1.676 3.369 ↑ 1.0 10,000 1

Hash (cost=189.00..189.00 rows=10,000 width=4) (actual time=3.369..3.369 rows=10,000 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 480kB
22. 1.693 1.693 ↑ 1.0 10,000 1

Seq Scan on products product (cost=0.00..189.00 rows=10,000 width=4) (actual time=0.007..1.693 rows=10,000 loops=1)

  • Filter: (brand_id = 1)
23. 0.150 0.150 ↑ 1.0 4 50

Index Only Scan using prices_pkey on prices price (cost=0.42..0.63 rows=4 width=8) (actual time=0.002..0.003 rows=4 loops=50)

  • Index Cond: (sku_id = sku.id)
  • Heap Fetches: 199
24. 0.597 0.597 ↑ 1.0 2 199

CTE Scan on visible_price_types (cost=0.00..0.04 rows=2 width=4) (actual time=0.001..0.003 rows=2 loops=199)

Planning time : 1.311 ms
Execution time : 375.656 ms