explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sZUR

Settings
# exclusive inclusive rows x rows loops node
1. 115.426 729.147 ↓ 1.1 197,870 1

Hash Semi Join (cost=100,569.31..119,223.06 rows=184,468 width=4) (actual time=390.849..729.147 rows=197,870 loops=1)

  • Hash Cond: (price.sku_id = cs.sku_id)
2.          

CTE visible_price_types

3. 0.574 0.574 ↑ 1.0 2 1

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

  • Filter: (retailer_id = 2008)
  • Rows Removed by Filter: 9998
4. 45.931 259.329 ↑ 1.0 200,000 1

Hash Join (cost=3,591.07..16,034.31 rows=200,000 width=8) (actual time=35.844..259.329 rows=200,000 loops=1)

  • Hash Cond: (sku.product_id = product.id)
5. 79.266 210.822 ↑ 1.0 200,000 1

Hash Join (cost=3,277.07..15,195.09 rows=200,000 width=12) (actual time=33.259..210.822 rows=200,000 loops=1)

  • Hash Cond: (price.sku_id = sku.id)
6. 60.403 99.058 ↑ 1.0 200,000 1

Hash Semi Join (cost=0.07..9,438.07 rows=200,000 width=4) (actual time=0.592..99.058 rows=200,000 loops=1)

  • Hash Cond: (price.price_type_id = visible_price_types.price_type_id)
7. 38.074 38.074 ↑ 1.0 400,000 1

Seq Scan on prices price (cost=0.00..6,163.00 rows=400,000 width=8) (actual time=0.007..38.074 rows=400,000 loops=1)

8. 0.005 0.581 ↑ 1.0 2 1

Hash (cost=0.04..0.04 rows=2 width=4) (actual time=0.581..0.581 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.576 0.576 ↑ 1.0 2 1

CTE Scan on visible_price_types (cost=0.00..0.04 rows=2 width=4) (actual time=0.119..0.576 rows=2 loops=1)

10. 19.681 32.498 ↑ 1.0 100,000 1

Hash (cost=1,636.00..1,636.00 rows=100,000 width=8) (actual time=32.498..32.498 rows=100,000 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2976kB
11. 12.817 12.817 ↑ 1.0 100,000 1

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

12. 1.254 2.576 ↑ 1.0 10,000 1

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

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

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

  • Filter: (brand_id = 1)
14. 83.132 354.392 ↑ 1.1 454,207 1

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

  • Buckets: 131072 Batches: 8 Memory Usage: 3014kB
15. 172.623 271.260 ↑ 1.1 454,207 1

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

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

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

17. 1.087 2.996 ↑ 1.1 5,002 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 240kB
18. 1.471 1.909 ↑ 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.415..1.909 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
19. 0.000 0.401 ↓ 0.0 0 1

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

20. 0.248 0.248 ↑ 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.248..0.248 rows=5,000 loops=1)

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

  • Index Cond: (visibility = 2)
22.          

SubPlan (forBitmap Heap Scan)

23. 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
24. 0.010 0.037 ↑ 2.0 2 1

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

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

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

  • Index Cond: (retailer_id = 2008)
Planning time : 0.746 ms
Execution time : 740.960 ms