explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dtNA

Settings
# exclusive inclusive rows x rows loops node
1. 120.748 748.478 ↓ 1.1 197,870 1

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

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

CTE visible_price_types

3. 0.727 0.727 ↑ 1.0 2 1

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

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

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

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

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

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

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

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

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

8. 0.008 0.738 ↑ 1.0 2 1

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

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

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

10. 20.530 33.070 ↑ 1.0 100,000 1

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

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

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

12. 1.618 3.326 ↑ 1.0 10,000 1

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

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

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

  • Filter: (brand_id = 1)
14. 84.960 364.834 ↑ 1.1 454,207 1

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

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

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

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

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

17. 0.726 2.086 ↑ 1.1 5,002 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 240kB
18. 0.951 1.360 ↑ 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.412..1.360 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.400 ↓ 0.0 0 1

BitmapOr (cost=432.21..432.21 rows=6,000 width=0) (actual time=0.400..0.400 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.152 0.152 ↑ 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.152..0.152 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.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.008..0.009 rows=2 loops=1)

  • Recheck Cond: (retailer_id = 2008)
  • Heap Blocks: exact=2
25. 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)
Planning time : 0.905 ms
Execution time : 760.736 ms