explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PhEf

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 357.272 ↑ 1.0 100 1

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

2.          

CTE visible_price_types

3. 0.601 0.601 ↑ 1.0 2 1

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

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

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

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

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

6. 0.101 356.362 ↑ 1,844.7 50 1

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

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

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

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

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

9. 84.801 352.474 ↑ 1.1 454,207 1

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

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

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

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

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

12. 0.725 2.036 ↑ 1.1 5,002 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 240kB
13. 0.920 1.311 ↑ 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.393..1.311 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.382 ↓ 0.0 0 1

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

15. 0.236 0.236 ↑ 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.236..0.236 rows=5,000 loops=1)

  • Index Cond: (visibility = 3)
16. 0.145 0.145 ↑ 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.145..0.145 rows=1,000 loops=1)

  • Index Cond: (visibility = 2)
17.          

SubPlan (forBitmap 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.003 0.009 ↑ 2.0 2 1

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

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

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

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

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

  • Buckets: 16384 Batches: 1 Memory Usage: 480kB
22. 1.698 1.698 ↑ 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.698 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.117 ms
Execution time : 368.361 ms