explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OzOQ

Settings
# exclusive inclusive rows x rows loops node
1. 2.362 857.052 ↓ 1.0 10,000 1

Hash Join (cost=115,218.24..115,592.07 rows=9,952 width=16) (actual time=849.918..857.052 rows=10,000 loops=1)

  • Hash Cond: (sku.product_id = sku_1.product_id)
  • Buffers: shared hit=29470, temp read=1304 written=1304
2.          

CTE visible_price_types

3. 0.615 0.615 ↑ 1.0 2 1

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

  • Filter: (retailer_id = 2008)
  • Rows Removed by Filter: 9998
  • Buffers: shared hit=45
4. 3.138 263.097 ↓ 1.0 10,000 1

Hash Join (cost=16,009.09..16,245.75 rows=9,976 width=20) (actual time=258.246..263.097 rows=10,000 loops=1)

  • Hash Cond: (sku.product_id = product.id)
  • Buffers: shared hit=2908, temp read=466 written=466
5. 39.693 255.927 ↓ 1.0 10,000 1

HashAggregate (cost=15,695.09..15,794.85 rows=9,976 width=4) (actual time=254.201..255.927 rows=10,000 loops=1)

  • Group Key: sku.product_id
  • Buffers: shared hit=2844, temp read=466 written=466
6. 76.665 216.234 ↑ 1.0 200,000 1

Hash Join (cost=3,277.07..15,195.09 rows=200,000 width=4) (actual time=36.910..216.234 rows=200,000 loops=1)

  • Hash Cond: (price.sku_id = sku.id)
  • Buffers: shared hit=2844, temp read=466 written=466
7. 64.840 103.527 ↑ 1.0 200,000 1

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

  • Hash Cond: (price.price_type_id = visible_price_types.price_type_id)
  • Buffers: shared hit=2208
8. 38.064 38.064 ↑ 1.0 400,000 1

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

  • Buffers: shared hit=2163
9. 0.006 0.623 ↑ 1.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=45
10. 0.617 0.617 ↑ 1.0 2 1

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

  • Buffers: shared hit=45
11. 22.840 36.042 ↑ 1.0 100,000 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 2976kB
  • Buffers: shared hit=636, temp written=171
12. 13.202 13.202 ↑ 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..13.202 rows=100,000 loops=1)

  • Buffers: shared hit=636
13. 1.937 4.032 ↑ 1.0 10,000 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 606kB
  • Buffers: shared hit=64
14. 2.095 2.095 ↑ 1.0 10,000 1

Seq Scan on products product (cost=0.00..189.00 rows=10,000 width=16) (actual time=0.008..2.095 rows=10,000 loops=1)

  • Filter: (brand_id = 1)
  • Buffers: shared hit=64
15. 1.648 591.593 ↓ 1.0 10,000 1

Hash (cost=98,914.45..98,914.45 rows=9,976 width=4) (actual time=591.593..591.593 rows=10,000 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 480kB
  • Buffers: shared hit=26562, temp read=838 written=838
16. 91.766 589.945 ↓ 1.0 10,000 1

HashAggregate (cost=98,814.69..98,914.45 rows=9,976 width=4) (actual time=588.456..589.945 rows=10,000 loops=1)

  • Group Key: sku_1.product_id
  • Buffers: shared hit=26562, temp read=838 written=838
17. 182.515 498.179 ↑ 1.1 454,207 1

Hash Join (cost=53,791.12..97,616.85 rows=479,134 width=4) (actual time=48.463..498.179 rows=454,207 loops=1)

  • Hash Cond: (cs.sku_id = sku_1.id)
  • Buffers: shared hit=26562, temp read=838 written=838
18. 189.840 283.930 ↑ 1.1 454,207 1

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

  • Hash Cond: (cs.collection_id = collection.id)
  • Buffers: shared hit=25926
19. 91.989 91.989 ↑ 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.172..91.989 rows=999,520 loops=1)

  • Buffers: shared hit=25813
20. 0.755 2.101 ↑ 1.1 5,002 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 240kB
  • Buffers: shared hit=113
21. 0.934 1.346 ↑ 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.403..1.346 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
  • Buffers: shared hit=113
22. 0.001 0.392 ↓ 0.0 0 1

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

  • Buffers: shared hit=64
23. 0.244 0.244 ↑ 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.244..0.244 rows=5,000 loops=1)

  • Index Cond: (visibility = 3)
  • Buffers: shared hit=32
24. 0.147 0.147 ↑ 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.147..0.147 rows=1,000 loops=1)

  • Index Cond: (visibility = 2)
  • Buffers: shared hit=32
25.          

SubPlan (forBitmap Heap Scan)

26. 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
27. 0.003 0.020 ↑ 2.0 2 1

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

  • Recheck Cond: (retailer_id = 2008)
  • Heap Blocks: exact=2
  • Buffers: shared hit=4
28. 0.017 0.017 ↑ 2.0 2 1

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

  • Index Cond: (retailer_id = 2008)
  • Buffers: shared hit=2
29. 19.217 31.734 ↑ 1.0 100,000 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 2976kB
  • Buffers: shared hit=636, temp written=171
30. 12.517 12.517 ↑ 1.0 100,000 1

Seq Scan on skus sku_1 (cost=0.00..1,636.00 rows=100,000 width=8) (actual time=0.013..12.517 rows=100,000 loops=1)

  • Buffers: shared hit=636
Planning time : 0.850 ms
Execution time : 858.773 ms